Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Options for scripting SQL Server database objects


By:   |   Read Comments (5)   |   Related Tips: More > SQL Server Management Studio

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.

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  = OFFSTATISTICS_NORECOMPUTE  = OFFIGNORE_DUP_KEY = OFFALLOW_ROW_LOCKS  = ON
ALLOW_PAGE_LOCKS  = ONON [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  = OFFSTATISTICS_NORECOMPUTE  = OFFIGNORE_DUP_KEY = OFFALLOW_ROW_LOCKS  = ON
ALLOW_PAGE_LOCKS  
= ONON [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  = OFFSTATISTICS_NORECOMPUTE  = OFFIGNORE_DUP_KEY = OFFALLOW_ROW_LOCKS  = ON
ALLOW_PAGE_LOCKS  = ONON [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  = OFFSTATISTICS_NORECOMPUTE  = OFFIGNORE_DUP_KEY = OFFALLOW_ROW_LOCKS  = ON
ALLOW_PAGE_LOCKS  
= ONON [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  = OFFSTATISTICS_NORECOMPUTE  = OFFIGNORE_DUP_KEY = OFFALLOW_ROW_LOCKS  = ON
ALLOW_PAGE_LOCKS  
= ONON [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  = OFFSTATISTICS_NORECOMPUTE  = OFFSORT_IN_TEMPDB = OFFIGNORE_DUP_KEY = OFF
DROP_EXISTING = OFFONLINE = OFFALLOW_ROW_LOCKS  = ONALLOW_PAGE_LOCKS  = ONON [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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

 

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

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 09, 2008 - 9:03:46 AM - grobido Back To Top

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 09, 2008 - 4:33:45 AM - trobichaux Back To Top

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?

 


Learn more about SQL Server tools