Recently I was tasked with replacing @@IDENTITY with SCOPE_IDENTITY() in all of our stored procedures. If I only had to do this in a single database and for just a few stored procedures this would have been easy enough, but I have over 90 databases and over a thousand stored procedures with @@IDENTITY in them. I needed to find a better way.
After an extensive search of the Internet I failed to find any solution that would help automate this task, so I decided to take a closer look at one of the tables that I knew contained information about stored procedures. That table is sys.syscomments. According to BOL (http://msdn.microsoft.com/en-us/library/ms186293.aspx) this table is only around for backwards compatibility, so this solution may not work in future releases of SQL.
The syscomments table contains all the information needed to script out stored procedures. The column ctext is the key to this. The ctext column contains the Unicode text of the stored procedure, including formatting like tabs and hard returns.
I created a table called Identity_Sprocs to hold the database name, the stored procedure id, the row sequence (for stored procedures greater than 8k bytes), the binary text, and a flag indicating whether I had fixed this stored procedure yet.
I also created 2 stored procedures. One stored procedure, called dba_GetIdentitySProcs, iterates through each database and searches the text column of the syscomments table for @@IDENTITY and inserts that information into the table above. The other stored procedure called dba_PrintSProc converts the varbinary of the ctext column into nvarchar, replaces the @@IDENTITY with SCOPE_IDENTITY(), and replaces CREATE PROCEDURE with ALTER PROCEDURE. It then prints the corrected stored procedure to the message window.
Finally in this article there is the script that I ran to generate the first 10 stored procedures that need to be fixed to the message window.
CREATE TABLE [dbo].[Identity_SProcs](
[dbName] [varchar](200) NOT NULL,
[id] [int] NOT NULL,
[colid] [int] NOT NULL,
[spText] [varbinary](max) NOT NULL,
[processed] [bit] NOT NULL
) ON [PRIMARY] GO
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Identity_SProcs] ADD CONSTRAINT [DF_Identity_SProcs_processed] DEFAULT ((0)) FOR [processed]
dba_GetIdentitySProcs Stored Procedure
The dba_GetIdentitySPRocs stored procedure uses a cursor and dynamic SQL to iterate through each database and then insert the information from syscomments into the Identity_SProcs table.
Looking at the dynamic SQL you can see that I am filtering out any stored procedures that start with sp_MSsync. These are the stored procedures that MS generates for replication and I do not want to mess with them since they aren't mine.
Below that, in the dynamic SQL, you can see in the sub-query that I am returning a list of id's where the text column has @@IDENTITY. The reason for putting this in the where clause is that a single stored procedure may be comprised of more than one row in the syscomments table. The colid column tells you the order in which to put the stored procedure back together in.
-- Author: Brian Davey
-- Create date: 3/2/2011
-- Description: Prints a stored procedure to the
-- message window with the
-- @@IDENTITY changed to
-- ============================================= CREATE PROCEDURE [dbo].[dba_GetIdentitySProcs]
SET NOCOUNT ON;
DECLARE @DB varchar(200), @SQL varchar(2000);
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE NOT name IN('master','msdb','model','tempdb')
ORDER BY name;
FETCH NEXT FROM db_cursor INTO @DB; WHILE @@FETCH_STATUS = 0
SET @SQL = 'WITH id_cte (id)
(Select id From ' + QUOTENAME(@DB) + '.sys.sysobjects
Where Not name Like ''sp_MSsync%''
SELECT ' + QUOTENAME(@DB,'''') + ', id, colid, ctext
FROM ' + QUOTENAME(@DB) + '.sys.syscomments
WHERE id IN(Select Distinct id
From ' + QUOTENAME(@DB) + '.sys.syscomments
Where [text] Like ''%@@IDENTITY%'')
And id In(Select id From id_cte)' INSERT INTO Identity_SProcs(dbName,id,colid,spText)
FETCH NEXT FROM db_cursor INTO @DB;
dba_PrintSproc Stored Procedure
The dba_PrintSproc stored procedure takes as parameters the id of the stored procedure and the database name in which it is located. It then pulls the varbinary information from the Identity_SProcs table, replaces @@IDENTITY and CREATE, and then prints it out to the message window.
The first thing this stored procedure does is to loop through all the rows in the table that have the same id and database name and then concatenate the varbinary together in order by colid. This is because a stored procedure may be contained in more that one row in the syscomments table.
Next, after printing USE [db] and setting some literal strings in nvarchar variables, there is the fnc_REPLACE function. This function, which is described below, replaces all instances of @@IDENTITY with SCOPE_IDENTITY().
The next thing we do is to start looping though the varbinary form of the stored procedure one hard return at a time. The line SET @idxBreak = CHARINDEX(CHAR(0x0D),@text,@idxCurrent); finds the next hard return in the stored procedure. It is important to loop one row at a time for 2 reasons. First, the PRINT statement can only handle 4000 bytes at a time (although possible, it is unlikely that any given sentence in a stored procedure will exceed 4000 bytes). Second, we need to find the CREATE PROCEDURE line so that we can change it to ALTER PROCEDURE.
Since the only text that is allowed to appear before the CREATE statement in a stored procedure are comments I simply need to keep track of if the current line is a comment line. The first line that is not a comment and contains the word CREATE must be the CREATE PROCEDURE line.
You will notice that I am adjusting the position of some of my index pointer variables like SET @idx = (@idx*2)-1. This is because of the difference between looking at a varbinary and a nvarchar. What I am doing is doubling the current position and then subtracting 1 to position the pointer to the beginning of the character.
CREATE PROCEDURE [dbo].[dba_PrintSproc]
-- Add the parameters for the stored procedure here
@id int = 0,
@db varchar(200) = ''
DECLARE @text varbinary(max), @ctext varbinary(max)
SELECT @text=CAST('' AS varbinary(1))
DECLARE ct_cursor CURSOR FOR SELECT spText FROM Identity_Sprocs WHERE dbname=@db AND id=@id ORDER BY id,colid
FETCH NEXT FROM ct_cursor INTO @ctext WHILE @@FETCH_STATUS = 0
SET @text = @text + @ctext;
FETCH NEXT FROM ct_cursor INTO @ctext
PRINT 'USE [' + @db + ']'
PRINT 'GO' DECLARE @ident nvarchar(20), @scope nvarchar(20), @alter nvarchar(20),@create nvarchar(20)
SELECT @ident='@@IDENTITY',@scope='SCOPE_IDENTITY()',@alter='ALTER',@create='CREATE' SET @text = dbo.fnc_REPLACE(@text,@ident,@scope); DECLARE @idxCmnt int, @idxCmntOpen int, @idxCmntClosed int, @foundCreate bit, @idx int
DECLARE @subText varbinary(max), @idxBreak int, @idxCurrent int
SELECT @idxCmnt=0, @idxCmntOpen=0, @idxCmntClosed=0, @foundCreate=0, @idx=0
, @idxBreak=0, @idxCurrent=0
SET @idxBreak = CHARINDEX(CHAR(0x0D),@text,@idxCurrent); WHILE @idxBreak > 0
SET @subText = SUBSTRING(@text,@idxCurrent,@idxBreak-@idxCurrent);
--FIND CREATE PROC AFTER OPENING COMMENTS
SET @idxCmntClosed = CHARINDEX(CAST('*/' AS nvarchar(10)),@subText,0);
IF @idxCmntClosed > 0
SET @idxCmntOpen = CHARINDEX(CAST('/*' AS nvarchar(10)),@subText,@idxCmntClosed);
ELSE IF @idxCmntOpen = 0
SET @idxCmntOpen = CHARINDEX(CAST('/*' AS nvarchar(10)),@subText,0);
IF @idxCmntOpen = 0 AND @foundCreate = 0
SET @idxCmnt = CHARINDEX(CAST('--' AS nvarchar(10)),@subText,0);
SET @idx = CHARINDEX(@create,@subText,0); IF @idx > 0 AND (@idxCmnt = 0 OR @idxCmnt > @idx)
SET @idx = (@idx*2)-1;
SET @subText = SUBSTRING(@subText,0,@idx) + CAST(@alter AS varbinary(20)) + SUBSTRING(@subText,@idx + LEN(@create)*2,LEN(@subText));
SET @foundCreate = 1;
----*----*----*----*----*----*----*----*----* PRINT CAST(@subText AS nvarchar(max));
SET @idxCurrent = @idxBreak + 4;
SET @idxBreak = CHARINDEX(CHAR(0x0D),@text,@idxCurrent);
SET @subText = SUBSTRING(@text,@idxCurrent,LEN(@text));
PRINT CAST(@subText AS nvarchar(max));
This function is intended to replicate what the T-SQL REPLACE does except it deals with the varbinary data type specifically.
Once again you'll notice here that I am adjusting my pointer like SET @idx = (@idx*2) -1. This is because when CHARINDEX returns the starting position based on a nvarchar data type it returns it as if each character represented a single byte. So we need to adjust this to be in line with the varbinary Unicode by doubling the position and subtracting 1.
-- Author: Brian Davey
-- Create date: 3/1/2011
-- Description: Replaces all occurrences of a specified string value with another string value.
-- For varbinary data types
CREATE FUNCTION [dbo].[fnc_REPLACE]
DECLARE @Result varbinary(max), @idx int
SET @Result = @text;
IF @find <> @replace
SET @idx = CHARINDEX(@find,@Result,0);
SET @idx = (@idx*2) -1; WHILE @idx > 0
SET @Result = SUBSTRING(@Result,0,@idx) + CAST(@replace AS varbinary(max)) + SUBSTRING(@Result,@idx + LEN(@find)*2,LEN(@Result));
SET @idx = CHARINDEX(@find,@Result,0);
SET @idx = (@idx*2) -1;
The Script to Generate the Stored Procedures
This script simply iterates through the Identity_SProcs table and calls the dba_PrintSProc stored procedure. It also prints an update statement to the message window to mark this stored procedure as fixed in the Identity_SProcs table.
DECLARE @db sysname,@id int DECLARE db_cursor CURSOR FOR
SELECT DISTINCT TOP 10 dbname,id FROM Identity_SProcs WHERE processed=0 ORDER BY dbName,id
OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db,@id WHILE @@FETCH_STATUS = 0
EXEC dba_PrintSproc @id,@db
PRINT 'UPDATE [DatabaseAdministration].dbo.Identity_SProcs SET processed=1 WHERE dbname=' + QUOTENAME(@db,'''') + ' AND id=' + CAST(@id AS varchar(20)) + ';'
FETCH NEXT FROM db_cursor INTO @db,@id
With the information above you should be able to replace any text you can define in your stored procedures. I hope you have found this information valuable.