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).
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