Recently a friend asked me steps/instructions to scripting schema and data of tables from a SQL Server database. I thought of writing a post and sharing the link rather than writing the process in an email, because I think this may benefit more people.

Sample Table

Let us assume that we have a table with the schema/structure and data as follows.

IF OBJECT_ID('Customers','U') IS NOT NULL BEGIN
	DROP TABLE Customers
END
CREATE TABLE Customers (
	CustomerID INT IDENTITY,
	FirstName VARCHAR(20),
	LastName VARCHAR(20),
	City VARCHAR(20) 
)

INSERT INTO Customers (FirstName, LastName, City)
SELECT 'Jacob', 'Sebatian','Ahmedabad' UNION ALL
SELECT 'Pinal', 'Dave', 'Bangalore'

Script Wizard

let us now try to generate the script for the schema and data of this table and find out what we get from SSMS.

The first step is to right click on the database and select Tasks > Generate Scrits

This will start the wizard. Click Next

If you would like to script all the objects in the database, leave the default options. Otherwise, select the specific objects you wish to script.

This is a critical step if you wish to generate scripts for the data also. Click on the Advanced button (as shown in the below screen image) and specify the additional options (as explained below)

At this step, locate the option Types of data to script and select Schema and Data (or any other option that suites your requirements)

This step is a usual ‘overhead’ that every wizard has and usually no one pays attention to that. However, it is recommended that you review the information before clicking on next

This will generate the scripts in the selected folder. Wait for the process to complete before proceeding.

Click on Finish to close the wizard.

Examine the results

Now let us go to the folder specified in the wizard and take a look at the script the wizard generated.  Here is what I got.

USE [NorthPole]
GO
/****** Object:  Table [dbo].[Customers]    Script Date: 07/13/2012 19:02:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customers](
	[CustomerID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](20) NULL,
	[LastName] [varchar](20) NULL,
	[City] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[Customers] ON
INSERT [dbo].[Customers] ([CustomerID], [FirstName], [LastName], [City]) 
	VALUES (1, N'Jacob', N'Sebatian', N'Ahmedabad')
INSERT [dbo].[Customers] ([CustomerID], [FirstName], [LastName], [City]) 
	VALUES (2, N'Pinal', N'Dave', N'Bangalore')
SET IDENTITY_INSERT [dbo].[Customers] OFF