How to generate scripts automatically in SQL Server

In this article I’m going to explain how you can generate CRUD (Create, Retrieve, Update, and Delete) statements and other queries such as create database, tables, and etc. automatically using SQL Server Management Studio.

There are different ways to achieves these. Imagine we have a simple table called Students as below.

 

Generate CRUD queries 

Generate Create and Drop Table queries

Generate Alter query

Generate create database script

 

 

Generate CRUD queries

Right click on the desired table, Script Table as, SELECT To, and choose whether you want so generate the script in new Query Editor Window, save to file, or copy in clipboard.

I chose New Query Editor Window so SQL Server will be generating the select statement for me.

USE [test]
GO

SELECT [StudenID]
      ,[Name]
      ,[Age]
  FROM [dbo].[Students]
GO

 

Generate INSERT, UPDATE, DELETE will follows the same steps.

-- INSERT STATEMENT --

USE [test]
GO

INSERT INTO [dbo].[Students]
           ([StudenID]
           ,[Name]
           ,[Age])
     VALUES
           (<StudenID, bigint,>
           ,<Name, nvarchar(50),>
           ,<Age, int,>)
GO

-- UPDATE STATEMENT --

USE [test]
GO

UPDATE [dbo].[Students]
   SET [StudenID] = <StudenID, bigint,>
      ,[Name] = <Name, nvarchar(50),>
      ,[Age] = <Age, int,>
 WHERE <Search Conditions,,>
GO

-- DELETE STATEMENT --

USE [test]
GO

DELETE FROM [dbo].[Students]
      WHERE <Search Conditions,,>
GO

Generate Create and Drop Table queries

In the same way you as generating CRUD queries you can generate Drop and Create table queries which for our Student table would be as below:

USE [test]
GO

/****** Object:  Table [dbo].[Students]    Script Date: 20/12/2013 5:16:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Students](
	[StudenID] [bigint] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Age] [int] NOT NULL
) ON [PRIMARY]

GO

USE [test]
GO

/****** Object:  Table [dbo].[Students]    Script Date: 20/12/2013 5:17:59 PM ******/
DROP TABLE [dbo].[Students]
GO

Generate Alter query

 

Generating Alter queries are a bit different. To do so open your table in design mode (right click on table name, select Design).

Rename one of the columns or change its Data Type.

After the changes right click on the column and select Generate Change Script.

A new Window will be opening which contains the Alter script.

You can check the Automatically generate change script on every save so SQL Server will prompt this window on every change.

In the same way you can generate Alter query for adding or removing columns.

Add a new column Phone of type nchar(20).

Right click on any column and select Generate Change Script. You will get the Alter query.

In the same you can get queries for deleting a column:

Set a column to be Primary Key.

You can generate queries on any other changes in the same way.

Generate create database script

SQL Server allows you to generate query for the whole database. That is to create all tables (or selected tables) and the data they contain.

To do so right click on the database, select Tasks, Generate Scripts.

A new window which helps what kind of query you want to generate will be appearing. Select Next on the welcome screen.

On the Choose Objects screen you can proceeds to generate script for the entire database (includes Tables, Views, Stored Procedures, Users, and etc.) by choosing Script entire database and all database objects, or select the object (Table, View, etc.) which you want to generate script by selecting the option Select specific database objects option.

Let’s select the second option and only create script for the Student table.

Click Next.

On the next screen you can choose how to generate the script. Whether to save into a file, copy in clipboard, or open in new query window. Choose the last option.

By default SQL Server will only generate script for you schema, that is in this example will only be getting the create tables and columns query, but the data we have. We need to tell SQL Server we want query for data as well. To do so click Advanced button.

Scroll down to Types of data to script.

The default value is Schema only. You can choose other options:

• Data Only: will be generating script which includes INSERT statements of all the records in the table.
• Schema and data: will be generating script which includes the table creating script as well as INSERT statements of all the records in the table
• Schema Only: will only be generating script for table definition.

Select the Schema and data for our sample and press OK. Press Next, Next, and Finish.

Your query is ready:

USE [test]
GO
/****** Object:  Table [dbo].[Students]    Script Date: 20/12/2013 5:48:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Students](
	[StudenID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Age] [int] NOT NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
	[StudenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Students] ON 

GO
INSERT [dbo].[Students] ([StudenID], [Name], [Age]) VALUES (1, N'Liza ', 17)
GO
INSERT [dbo].[Students] ([StudenID], [Name], [Age]) VALUES (2, N'Macy', 18)
GO
SET IDENTITY_INSERT [dbo].[Students] OFF
GO