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.

The Solution

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.

Identity_SProcs Table

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
GO
ALTER TABLE [dbo].[Identity_SProcs] ADD CONSTRAINT [DF_Identity_SProcs_processed] DEFAULT ((0)) FOR [processed]
GO

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 
-- SCOPE_IDENTITY()
-- =============================================
CREATE PROCEDURE [dbo].[dba_GetIdentitySProcs]
AS
BEGIN
 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;

 OPEN db_cursor;
 FETCH NEXT FROM db_cursor INTO @DB;
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @SQL = 'WITH id_cte (id)
 AS
 (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)
 EXEC(@SQL);

 FETCH NEXT FROM db_cursor INTO @DB;
 END
 DEALLOCATE db_cursor;
END

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) = ''
AS
BEGIN
 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
 OPEN ct_cursor
 FETCH NEXT FROM ct_cursor INTO @ctext
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @text = @text + @ctext;
 FETCH NEXT FROM ct_cursor INTO @ctext
 END
 DEALLOCATE ct_cursor


 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
 BEGIN
 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
 BEGIN
 SET @idxCmnt = CHARINDEX(CAST('--' AS nvarchar(10)),@subText,0);
 SET @idx = CHARINDEX(@create,@subText,0);
 IF @idx > 0 AND (@idxCmnt = 0 OR @idxCmnt > @idx)
 BEGIN
 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;
 END
 END
 ----*----*----*----*----*----*----*----*----*
 PRINT CAST(@subText AS nvarchar(max));

 SET @idxCurrent = @idxBreak + 4;
 SET @idxBreak = CHARINDEX(CHAR(0x0D),@text,@idxCurrent);
 END
 SET @subText = SUBSTRING(@text,@idxCurrent,LEN(@text));
 PRINT CAST(@subText AS nvarchar(max));
 PRINT 'GO'
END

fnc_REPLACE Function

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] 
(
 @text varbinary(max),
 @find nvarchar(max),
 @replace nvarchar(max)
)
RETURNS varbinary(max)
AS
BEGIN
 DECLARE @Result varbinary(max), @idx int

 SET @Result = @text;

 IF @find <> @replace
 BEGIN
 SET @idx = CHARINDEX(@find,@Result,0);
 SET @idx = (@idx*2) -1;
 WHILE @idx > 0
 BEGIN
 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;
 END
 END

 RETURN @Result
END

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
BEGIN
 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)) + ';'
 PRINT 'GO'
 FETCH NEXT FROM db_cursor INTO @db,@id
END
DEALLOCATE db_cursor

Finally

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.

Thanks

Brian Davey