T-SQL Programmatically Script Modified Stored Procedures

In this example I will show how to write a T-SQL routine that scripts any stored procedures in your database that have been modified in the past three days. You should, of course, modify this time filter as you see fit.

This T-SQL routine is useful to extend the scripting option built into SQL Server Management Studio, and can be even more useful if you are considering using it programmatically.

I will use the T-SQL routine as part of a regular coding scenario where you are updating an application and want to deploy your updated stored procedures to your test environment. You can naturally extend this code to include modifications by only one or more users, for example.

If you find this example interesting, then feel free to check out other articles I have written about using T-SQL to query your database.

The Process: an Overview

This code is meant to be run in your Database in SSMS. You can use SQL Server 2005 and up, but the system table calls are not supported by SQL Server 2000.

When you run the code, it will look in the system tables for recently modified Stored Procedures, which it will then script for you.

Once the code has finished running you will have a functional script that you can copy and paste into SSMS for a different version of your database. When you run it, the code will drop any old versions of your procedures before creating your new versions.

The code will also copy the permissions you have set for each of your procedures. The idea is to automate the basic process needed to migrate your Stored Procedures to a different environment.

How Does the Code Work?

Rather than trying to remember everything that you modified while writing your code, you can simply query the sys.objects system table for all recently updated Stored Procedures.

In the case of this example I have set the time filter to select the modified procedures from the past three days by using the DATEADD function on GETDATE().

The code then uses a cursor to loop through each modified procedure. The code will write the CREATE code to your SSMS query results pane. You can easily take this and integrate it with an application instead of running it directly in SSMS.

As the code executes, and before the CREATE code for each procedure is output, the routine makes sure to output the IF EXISTS. The logic behind this is that you want to make sure that the code doesn’t fail when you run it if an earlier version of your procedure already exists. So this code checks for earlier versions of your procedures and drops them before it tries to create new procedures.

As a final step, the code makes sure to copy the various permissions assigned to each of your Stored Procedures. It does so using a second cursor that runs within the loop that generates each procedure. The second cursor needs to query two system tables (sys.database_principals and sys.database_permissions).

The Code

DECLARE @ProcName VARCHAR(255)
DECLARE @ProcId INT
DECLARE @OutProcText VARCHAR(MAX)
DECLARE @GrantCmd VARCHAR(100)
DECLARE @GrantLevel VARCHAR(100)
DECLARE @GrantRole VARCHAR(200)
DECLARE @CurrDate DATETIME
SET @CurrDate = DATEADD(dd, -3, GETDATE())
DECLARE Proc_Cursor CURSOR FOR
SELECT name,object_id
FROM sys.objects
WHERE type = 'P'
AND modify_date >= @CurrDate
ORDER BY Modify_date desc
OPEN Proc_Cursor
FETCH NEXT FROM Proc_Cursor INTO @ProcName,@ProcId
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @OutProcText=sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.type='p'
AND OBJECT_NAME(sm.object_id) = @ProcName
PRINT '------------------------------START PROC ' + @ProcName + '---------------------------'
PRINT 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @ProcName + ']'') AND type in (N''P'', N''PC''))
DROP PROCEDURE [dbo].[' + @ProcName + ']'
PRINT 'GO'
PRINT @OutProcText
PRINT 'GO'
DECLARE Permissions_Cursor CURSOR FOR
SELECT permiss.state_desc,permiss.permission_name,princ.name
FROM sys.database_principals AS princ
INNER JOIN sys.database_permissions AS permiss ON permiss.grantee_principal_id = princ.principal_id
WHERE permiss.major_id=@ProcId
OPEN Permissions_Cursor
FETCH NEXT FROM Permissions_Cursor INTO @GrantCmd,@GrantLevel,@GrantRole
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @GrantCmd + ' ' + @GrantLevel + ' ON [dbo].[' + @ProcName + '] TO [' + @GrantRole + '] AS [dbo]'
PRINT 'GO'
FETCH NEXT FROM Permissions_Cursor INTO @GrantCmd,@GrantLevel,@GrantRole
END
CLOSE Permissions_Cursor
DEALLOCATE Permissions_Cursor
PRINT '------------------------------END PROC ' + @ProcName + '---------------------------'
FETCH NEXT FROM Proc_Cursor INTO @ProcName,@ProcId
END
CLOSE Proc_Cursor
DEALLOCATE Proc_Cursor