SQL Tips – Generate Script files for deployment quickly using SQLCMD command utility


Often we need DROP and CREATE scripts for Stored procedure modified during development of any CR to deployment them in ST/UAT/PROD environment.

I know I can simply right click the database and ‘Tasks > Generate Scripts’, but that doesn’t script the stored procedure in the template I want.

I need the stored procedure to be scripted in the same template you get when you right click the stored procedure from the object explorer and ‘Script Stored Procedure As > DROP and CREATE’.

To achieve this I found one solution which can help me to reduce my effort during deployment.

Here, we can use SQLCMD utility and achieve what we need. I am sharing the same with you so that you can also get benefit out of this.

Let us discuss the steps to achieve this.

Step 1 – Create the scripting stored procedure, this stored procedure will help us to generate the scripts in the template we want.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GenerateScriptTip]') 
AND type in (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_GenerateScriptTip]
GO

CREATE PROCEDURE [dbo].[usp_GenerateScriptTip] 
(
  @ObjectID INT,
  @Name NVARCHAR(128),
  @SchemaID INT
) 
AS 

DECLARE @code VARCHAR(MAX), @newLine CHAR(2)
SET @newLine = CHAR(13) + CHAR(10)

SET @code = 
    'USE [' + DB_NAME() + ']' + @newLine + 'GO' + @newLine + @newLine
    + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = '
    + 'OBJECT_ID(N''[' + SCHEMA_NAME(@schemaID) + '].[' + @Name + ']'') ' 
    + 'AND type IN (N''P'', N''PC''))' + @newLine 
    + 'DROP PROCEDURE [' + SCHEMA_NAME(@schemaID) + '].[' + @name + ']' 
    + @newLine + @newLine + 'SET ANSI_NULLS ON' + @newLine + 'GO' 
    + @newLine + @newLine + 'SET QUOTED_IDENTIFIER ON' + @newLine + 'GO'
    + @newLine + @newLine
    + OBJECT_DEFINITION(@ObjectID) + @newLine + 'GO' 
    + @newLine + @newLine + 'SET ANSI_NULLS OFF' + @newLine + 'GO' 
    + @newLine + @newLine + 'SET QUOTED_IDENTIFIER OFF' + @newLine + 'GO'

WHILE @code <> ''
BEGIN
  PRINT LEFT(@code,8000)
  SET @code = SUBSTRING(@code, 8001, LEN(@code))
END
GO

Step 2 – Next, we need to enable Command shell which we are going to run to generate the SQL files for each stored procedures.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Step 3 – Run the below script to generate the templates we want. These SQL files we can use during deployment on ST/UAT/PROD environment.

DECLARE 
  @name sysname,
  @objID int,
  @schemaID int,
  @cmd varchar(1000),
  @folder varchar(128)

--This folder should exist on SQL DB server, files generated will be saved at SQL DB server C Drive. 
SET @folder = 'C:\Scripts'

--You can update the below query to select Stored procedures which are needed in deployment.
DECLARE procs CURSOR FOR 
	SELECT name, object_id, schema_id 
	FROM sys.procedures
	WHERE name in 
	(
	'Stored Procedure 1',
	'Stored Procedure 2',
	'Stored Procedure 3'
	)
	ORDER BY [name]

OPEN procs

FETCH NEXT FROM procs
	INTO @name, @objID, @schemaID

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @cmd = 'sqlcmd -S .\ -U sa -P sa -d ' + DB_NAME() 
           + ' -Q "EXEC usp_GenerateScriptTip ' 
           + CONVERT(VARCHAR(20), @objID) + ', N'''
           + @name + ''', ' + CONVERT(VARCHAR(20), @schemaID) 
           + '" &gt; ' + @folder + '\' + @name + '.sql'

  EXEC xp_cmdshell @cmd

  FETCH NEXT FROM procs
  INTO @name, @objID, @schemaID

END
CLOSE procs
DEALLOCATE procs

You can also update select query of above Cursor with below query which fetches the stored procedure names which are updated recently for CR-1234. Here CR-1234 must be present in the stored procedures which you have modified as a comment, which we usually do during development.

SELECT name, object_id, schema_id 
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%CR-1234%'

Similarly, You can generate DROP and CREATE scripts for New Tables. Do not use this if table is already exists and you want to alter the definition of table.

DECLARE tabls CURSOR FOR  
SELECT name, object_id, schema_id  
FROM sys.Tables
WHERE is_ms_shipped = 0  
ORDER BY [name] 
OPEN tabls 

FETCH NEXT FROM tabls 
INTO @name, @objID, @schemaID 

WHILE @@FETCH_STATUS = 0 
BEGIN 

  SET @cmd = 'sqlcmd -S .\ -U sa -P sa -d ' + DB_NAME()  
           + ' -Q "EXEC usp_GenerateScriptTip '  
           + CONVERT(VARCHAR(20), @objID) + ', N''' 
           + @name + ''', ' + CONVERT(VARCHAR(20), @schemaID)  
           + '" &gt; ' + @folder_Tbl + '\' + @name + '.sql' 

  EXEC xp_cmdshell @cmd 

  FETCH NEXT FROM tabls 
  INTO @name, @objID, @schemaID 

END 

CLOSE tabls 
DEALLOCATE tabls 

That is all.

Hope this will help and speed up your deployment process.

For more details on SQLCMD command – click here.

Jay Ganesh !!!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s