How to Generate Scripts In SQL Server

There are multiple methods for exporting data from SQL Server. We will explore a few and focus on the “Generate Scripts” feature in SSMS.

Feature Image / License

Generate Scripts in SSMS

There are plenty of ways to export table data and definition from SQL Server. To name a few:

Copy / paste to text file or Excel
“Save results as” from a query output grid
SSIS
Database level “Export Data” under tasks of database in object browser

However, there can be hang-ups from the above methods. What if you have a lot of tables to go through? What if your target table(s) has a column with XML data type? It’s not often clean to use these methods. Maybe we want to copy a table from one server to another and don’t want to setup linked servers? What if you need the dependent objects of the table to accompany it?

If you are looking to get one or more tables definition AND data there is a way already baked into SQL Server that we can use. SSMS gives us a great canned way to do this.

Single Table Example

Let’s pick an example table from AdventureWorks2016. Let’s pick a big table.

USE AdventureWorks2016;
GO

select distinct t.name, t.[object_id], SCHEMA_NAME([schema_id]) as 'SchemaName', t.create_date, t.modify_date, p.[rows]
from sys.tables t inner join sys.partitions p on t.[object_id] = p.[object_id]
order by p.[rows] desc;

USE AdventureWorks2016;

select distinct t.name, t.[object_id], SCHEMA_NAME([schema_id]) as 'SchemaName', t.create_date, t.modify_date, p.[rows]

from sys.tables t inner join sys.partitions p on t.[object_id] = p.[object_id]

order by p.[rows] desc;

Sales.SalesOrderDetail looks like a good choice. Let’s generate a script for that table, all associated objects, and its data.

The safest way to create structure including all indexes, keys, defaults, constraints, dependencies, triggers, etc. is to use SSMS Generate Scripts.

Microsoft KB on the Generate Scripts Feature

Simply right click on the database that contains the objects you want to generate a script for –> Tasks –> Generate Scripts

A wizard will pop up – you know the drill…

Now we can choose the objects we want to script out.

PROTIP: if the object you want to generate a script for is a temp table or some other data set, then create it as a physical table and then proceed.

The default is to script the entire database and all database objects. That’s probably overkill but if that’s what you want to do go for it.

In this example, we want to only generate scripts for a specific object or more. Here we have to select that object. Let’s choose Sales.SalesOrderDetails.

Click next and we come to an options panel:

Options to Consider

There’s a few things we want to change here. We can generate scripts for the clipboard, a new query window, or a file. Most use cases will be for a file. Obviously, you’ll want to save the file to your choice location.

You can choose to generate the file to a single file OR one file per object.

Now click on Advanced and the fun begins:

There are some options worth changing from their defaults. I’ll point out a few:

Script DROP and CREATE – if you want drop statements to accompany the creates
Script for Server Version – for any TSQL incongruencies if loading into an older version of SQL Server
Script for the database engine edition – example: you are going from Enterprise Edition (EE) to Standard Edition (SE) and have objects to script which use EE features.
Types of data to script – this is the most important one you are probably after. It allows you to script out only the data, the schema and data, or the schema only.

THIS IS THE KEY OPTION YOU WANT TO CHANGE

Down near the bottom are the options for things like check constraints, foreign keys, primary keys, indexes, and triggers.

Once all of this has been sorted, click next and proceed to the summary screen.

Click next and see the result:

All green and success! That’s what we like to see.

Click finish and let’s load the file we just created and see what’s in it. We can navigate to the file in the file system:

So What Do We Do With A File Like This?

Open the file with a text editor like Notepad++ and check it out. In my example it is over 122,000 lines!

It contains everything you asked it to have. Now you can load the file into a database and reproduce it.

Be careful with big files – SSMS has limits. You might need to use a more lightweight client such as sqlcmd to work with it.

An XML Example

Some data types don’t play nice: XML and NVARCHAR(MAX) can add complications. That’s why this method is a useful one here.

Let’s find a table who has a column with xml data type.

select * from INFORMATION_SCHEMA.columns where DATA_TYPE = 'xml';

1	select * from INFORMATION_SCHEMA.columns where DATA_TYPE = 'xml';

NVARCHAR(MAX) can also present problems when there is a ton of data in the table.

select * from INFORMATION_SCHEMA.columns where DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH = -1;

1	select * from INFORMATION_SCHEMA.columns where DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH = -1;

As an XML example, let’s pick HumanResources.JobCandidate. Select from that table yields:

That XML won’t copy nice to Excel and will be hard to read in a text editor – especially when you want to see tabular results. You can follow the same steps as above.

Thanks for reading!

If you liked this post then you might also like my recent post about Wait Statistics in SQL Server – Performance Troubleshooting.

Do you care about your InfoSec and Privacy? Then YOU need to use a VPN.