Options for scripting SQL Server database objects

By:   |   Comments (8)   |   Related: > SQL Server Management Studio


Problem

SQL Server Management Studio is rich with features.  So rich, in fact, that after almost 3 years I'm still finding new tools in the application that afford me more control over my databases and the methods for supporting them.  Recently I found myself needing to script out a database and it's objects for reviewing a problem offline.  I was going to be traveling without my laptop (ooh, that phrase still makes me break out in a cold sweat) and I needed to be able to look over how the tables in a specific database were structured.  I scripted out the tables and was not satisfied with the format and spent quite some time reformatting the scripts that were generated by Management Studio.  After the fact I started to wonder if perhaps there was a way to alter how the scripts are formatted.

Solution

Leave it to our benefactors at Microsoft to come up with a method for setting preferences for scripting output in SQL Server Management Studio.  Located under Tools\Options is a dialog form for altering various options associated with all aspects of Management Studio.  Selecting Scripting from the left pane of this form will expose various formatting settings you can take advantage of to make your scripts more relevant to you, versus what the Microsoft Developers thought was relevant to all Database Professionals.

ssms scripting options
ssms scripting options

At this point I am going to script out the dbo.Categories table from the Northwind database.  Scripting out the database objects is accomplished by selecting the object of interest in Object Explorer.  Utilizing the right-click menu you then select Script Table As\CREATE To\File.  This will create a .sql file with the Transact SQL code required to create the table in question. 

The default script output for this table is shown below:

USE [Northwind] 
GO 
/****** Object:  Table [dbo].[Categories]    Script Date: 05/18/2008 21:38:24 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Categories]( 
   [CategoryID] [int] IDENTITY(1,1) NOT NULL, 
   [CategoryName] [nvarchar](15) NOT NULL, 
   [Description] [ntext] NULL, 
   [Picture] [image] NULL, 
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED  
( 
   [CategoryID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now let's take a look at the effect some of the more important scripting modification options would have on this script.

Include Descriptive Headers

As you would expect, this option toggles descriptive headers in your scripts ON or OFF.

USE [Northwind] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Categories]( 
   [CategoryID] [int] IDENTITY(1,1) NOT NULL, 
   [CategoryName] [nvarchar](15) NOT NULL, 
   [Description] [ntext] NULL, 
   [Picture] [image] NULL, 
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED  
( 
   [CategoryID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Include IF NOT EXISTS Clause

This setting dictates whether to check for existence of an object before creating it.

USE [Northwind] 
GO 
/****** Object:  Table [dbo].[Categories]    Script Date: 05/18/2008 21:55:28 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Categories]') AND type IN (N'U')) 
BEGIN 
CREATE TABLE [dbo].[Categories]( 
   [CategoryID] [int] IDENTITY(1,1) NOT NULL, 
   [CategoryName] [nvarchar](15) NOT NULL, 
   [Description] [ntext] NULL, 
   [Picture] [image] NULL, 
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED  
( 
   [CategoryID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
END

Script Permissions

Will script the database user and role permissions associated with the object(s) being scripted.

USE [Northwind] 
GO 
/****** Object:  Table [dbo].[Categories]    Script Date: 05/18/2008 22:04:45 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Categories]( 
   [CategoryID] [int] IDENTITY(1,1) NOT NULL, 
   [CategoryName] [nvarchar](15) NOT NULL, 
   [Description] [ntext] NULL, 
   [Picture] [image] NULL, 
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED  
( 
   [CategoryID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

GO 
GRANT DELETE ON [dbo].[Categories] TO [public] 
GO 
GRANT INSERT ON [dbo].[Categories] TO [public] 
GO 
GRANT REFERENCES ON [dbo].[Categories] TO [public] 
GO 
GRANT SELECT ON [dbo].[Categories] TO [public] 
GO 
GRANT UPDATE ON [dbo].[Categories] TO [public]

Script Indexes

Will script out any associated indexes for the table(s) being scripted.

USE [Northwind] 
GO 
/****** Object:  Table [dbo].[Categories]    Script Date: 05/18/2008 22:11:32 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Categories]( 
   [CategoryID] [int] IDENTITY(1,1) NOT NULL, 
   [CategoryName] [nvarchar](15) NOT NULL, 
   [Description] [ntext] NULL, 
   [Picture] [image] NULL, 
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED  
( 
   [CategoryID] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

GO 

/****** Object:  Index [CategoryName]    Script Date: 05/18/2008 22:11:32 ******/ 
CREATE NONCLUSTERED INDEX [CategoryName] ON [dbo].[Categories]  
( 
   [CategoryName] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 

Using SQL Server Management Studio's scripting options customization tool gives you the ability to make your scripts as verbose or streamlined as necessary for your distinct needs.  Don't settle for anything less than script generation that fits you're environment.

Next Steps
  • Review other tips on generating SQL scripts here. 
  • Check this out for yourself in your environment.  Script out one of your databases, altering various script options and reviewing the effect on your base script.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, October 20, 2020 - 5:59:22 AM - Taqveem Khalid Back To Top (86662)
The Scripting options now live under the same Tools>Options, but have been put under the 'SQL Server' Object Explorer root node.

Saturday, March 23, 2019 - 9:24:48 AM - wassuf Back To Top (79383)

Hi everyone,

how  we can select the data of 2 years and not all data in this case ?

thanks 


Friday, February 2, 2018 - 7:24:04 AM - BizAlchemist Back To Top (75087)

Hi everyone,

 

Is there any way to suppress generation of SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON?

 

thanks

Constantine


Thursday, March 17, 2016 - 9:39:29 AM - Greg Robidoux Back To Top (40971)

Hi Joe,

we also covered the DROP IF EXISTS in this tip:

https://www.mssqltips.com/sqlservertip/4108/tsql-enhancements-in-sql-server-2016/

-Greg

 


Wednesday, March 16, 2016 - 11:22:49 PM - Joe Back To Top (40967)

 

These options are removed completely from ssms 2014. :'( 

 

However DIE or Drop if exists will be part of SQL 2016!! 

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/ 


Tuesday, June 10, 2008 - 11:50:45 AM - marcos.casamayor Back To Top (1129)

Yes, there are many tools to generate DDL scripts. But, practically no tool to script data. So I built Nautilus (http://sourceforge.net/projects/nautilus/) to locate records, navigate related records and generate scripts to copy the data from one DB to another.

As we use it everyday, it's always growing. You can generate dynamic diagrams and navigate it. I hope you'd like it.

Marcos. 


Monday, June 9, 2008 - 9:03:46 AM - grobido Back To Top (1116)

I checked this in both SQL 2005 Standard edition and in SQL 2005 Express edition and the "Scripting" option exists in both under the Tools->Options menu item.


Monday, June 9, 2008 - 4:33:45 AM - trobichaux Back To Top (1112)

Why don't I have "Scripting" on my Tools->Options menu? I have all of the other choices shown.

 Is this only in 2008, or is there some other setting I can check?

 















get free sql tips
agree to terms