Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Changing Text in Multiple Stored Procedures From a Stored Procedure

By Brian Davey,

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

Total article views: 9347 | Views in the last 30 days: 5
 
Related Articles
FORUM

Using Cursors Inside Stored procedure problem

creating cursor inside stored procedure gives error

FORUM

Inserting rows Using Cursors in Stored Procedure

Inserting rows Using Cursors in Stored Procedure

FORUM

Running Oracle stored procedures from SQL server 2005

Running Oracle stored procedures with REF CURSOR

FORUM

stored procedure

stored procedure

FORUM

Creating view from stored procedure results

Creating view from stored procedure results

Tags
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones