By: Tim Ford | Updated: 2008-05-21 | 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.
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.
About the author
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
Article Last Updated: 2008-05-21
