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.
Tim Ford is a Senior Database Administrator with MindBody in San Luis Obispo, California and is in the process of relocating west to the Pacific Northwest from Michigan. Since 2010 he’s produced Microsoft Data Platform training events branded as SQL Cruise from Alaska to the Caribbean and the Mediterranean at Tech Outbound, an events company specializing in technical training in unconventional locations. His SQL Cruise events take place on cruise ships in the Caribbean, Alaska, and the Mediterranean. Tim also is the Executive VP of Marketing for PASS, the global association for Microsoft data professionals. He also is a contributing author for itprotoday. Tim loves helping people find their true potential through education and building networks between Thought Leaders in various fields and those who are just starting on their careers or struggling to find their footing in established careers. If you’re looking for this sort of experience then check out the next SQL Cruise event taking place this August in Seattle.
- MSSQLTips Awards: Acheiver (75+ tips) – 2010
