Generate Scrips

Other SQL Server Topics
1

Hello, How can I generate a script of a database that has the entire schema but for some tables it would contain the data from tables that I select? I'm Using MS Sql Server 2014.

Thanks,

Steve

2

Right click on the database and under Tasks Generate Script. For the data you want, you could export it to a file.

3

This does not allow you to select certain tables that I want populated. I want the entire schema but just want certain tables (like my "States" table) to have the data.

4

You would have to do that in two goes - first with all tables, and no data, to get the DDL script to build the tables, and then a second time for just the tables you want data for, and you would have to edit-out the Create Table bits from that script (maybe you can set the Options to not generate Create Table and only generate Data Insert statements, I haven't tried that combination!)

If you are "moving" this to another MS SQL database (rather than some other SQL Flavour) you would be much better off, IMHO, exporting the data to a file, rather than generating INPUT statements - they are slow to run, cumbersome for anything large, but perhaps you just have a handful of "Config Rows" and want an all-in-one install script; in that context it would be a reasonable choice.

For larger tables I would use BCP using NATIVE mode to Export (and then Import) the data (provided that the target Server/DB was also MS SQL Server)

5

Backup the database and restore it to another name...

Truncate/Delete the data in all of the tables except the ones you are interesting in keeping - optionally shrink the database to a smaller size and rebuild indexes.

Backup this copy and deploy to your new system with a simple restore operation...of course, this option is not backward compatible and would not work for downgrading the database to a previous version.

Another option - use a database compare tool like SQL Compare and SQL Data Compare from Redgate. Use your current database as the source system and a blank database as the destination. SQL Compare can be used to generate the scripts to create all the objects and SQL Data Compare can be used to generate the scripts to load the data from your selected tables.