Introduction

I was recently asked to create a complete copy of one of the databases on our test environment using a .NET application that is built in-house. The reason that we use this application instead of the built-in scripting capabilities of SQL Server is that by using our application we have the flexibility to select which objects we want to transfer and leave the rest intact. In addition by using this application we have the ability to transfer the data too if we wanted. At the heart of the application is SQLDMO which is used to script the selected objects. We can then run the generated script on the destination server and create those objects.

I had used this application successfully a few times before however this time the result was not as satisfactory. After investigating the problem I learned that when you rename a Stored Procedure or User-Defined Function the background system tables are not updated accordingly and this was the cause of the problem I faced.

Before we start

I wrote all the example code on SQL Server 2005. I am using SQL Server Management Studio (SSMS) to connect to 2005 and 2000 databases.

"Wrong Procedure Name" Issue

After running the script and creating all of the objects on the server I generated one of the business reports using the data in the source and destinations servers. To my surprise the numbers did not line up. Both reports were calling the same set of stored procedures to source the data. I checked the data in the underlying tables and found no discrepancy there.

Next I checked the code of the Stored Procedures and noticed for one of them the name is the same in the source and destination databases however the code of the stored procedure is different.

The first thing that came to my mind was that the DMO is the problem, so I started my investigation by setting breakpoints in the application and looking at the procedure name and the procedure code that was being pulled by DMO and noticed although the name of the stored procedure is correct, the code of the stored procedure is different from what I see in SSMS.

Obviously the DMO was doing its job flawlessly since it was just using what it was given by the database.

Next I checked the text of the stored procedure in the "syscomments" table and surprisingly found that the name that I see in SSMS is different from the name that I have in the text of that very same stored procedure in the "syscomments" table. In another words in SSMS I had "usp_pull_data_old" for the rouge stored procedure whereas in the in the "text' column of the "syscomments" table I had "CREATE PROCEDURE usp_pull_data ...".

A couple of hours of investigation revealed what had happened:

On the development database the developer had created a stored procedure to pull some data and named it "usp_pull_data".

After sometime the business requirements was changed, so the developer renamed the stored procedure to "usp_pull_data_old" and created a new "usp_pull_data" with the new business requirements and so the problem began. By renaming the stored procedure we ended up having two "CREATE" statements for the same stored procedure in the "text" column of the "syscomments"; one with the old logic and another with the new logic. The DMO therefore generated two

"CREATE PROCEDURE usp_pull_data ..."

scripts. On the destination server the one with the old business logic was created first and the second one had failed because there was already a procedure with the same name in the database.

Example

Create a stored procedure in your test database:

CREATE PROCEDURE usp_Delete_Me_After_Test
AS
BEGIN
 SELECT 'HELLO THIS IS JUST A TEST'
END

Now let's check the text of the newly created procedure:

SELECT
 o.name, c.text
FROM
 sysobjects o join syscomments c
 on o.ID = c.ID
WHERE
 o.name = 'usp_Delete_Me_After_Test'

The query returns:

As you can see our stored procedure is correctly registered in SQL Servers' system tables.

Now let's rename the stored procedure. You can do this by right clicking on the stored procedure name in Management Studio and click on "rename". Alternatively you can execute the following code:

EXEC sp_rename 'usp_Delete_Me_After_Test', 'usp_Delete_Me_After_Test_Renamed'

And again let's check what we have in the syscomments tables:

SELECT
 o.name, c.text
FROM
 sysobjects o join syscomments c
 on o.ID = c.ID
WHERE
 o.name = 'usp_Delete_Me_After_Test_Renamed'

And this is what we get:

As you can see the rename only affects the "sysobjects" table and the "syscommnets" table is not getting updated with the change.

One might think that the solution is to somehow refresh or update the "syscomments" table with the new procedure name, however it is neither possible nor recommended to update the system catalogue tables manually or with an ad hoc query. Even if it was allowed, it is too risky an operation to try. So basically the only solution that is left is to drop the procedure and recreate it with the new name.

Of course it is easy to identify such SPs or UDFs in a test environment where there are only a few of them but imagine you want to find all the stored procedures that have been renamed in a development or production server/database with hundreds of stored procedures and user defined functions.

What we need is a script that can crawl into the system tables and pull all those objects that qualify for the discrepancy shown above. All we need to do is to join the "sysobjects" table with the "syscomments" and check if the name that is registered in the "sysobjects" table is the same as the one in the text column of the "syscomments" table.

However if you are still using SQL Server 2000 you might face a problem. The issue lies within the fact that there is an 8000 character limit on the size of the "text" column in the "syscomments" table.

Lots of companies put standard headers on top of stored procedures and user defined functions, which contain copyright information, developer name, versioning, and other information that can span multiple lines. In such cases the

"CREATE PROCEDURE ..."

Might not be in the first 8000 characters therefore you need to assemble all the rows related to one specific stored procedure and search through them for the name.

The Script

When searching for solutions to this problem I came across this post on www.sqlservercental.com: http://www.sqlservercentral.com/Forums/Topic333815-329-1.aspx

The post proposes a solution for finding the full text of any stored procedures. Of course we don't need everything that is happening in that code so after removing the extra features and adding what is required the result is the following script.

DECLARE @SQL varchar(8000)
DECLARE @name varchar(400)
DECLARE @buf varchar(8000)
DECLARE @id int
DECLARE @colid int
DECLARE @found int
SET @found = 0
DECLARE cur CURSOR READ_ONLY FOR
SELECT
 name,
 syscomments.id,
 colid
FROM
 syscomments join sysobjects
 ON
 syscomments.id = sysobjects.id
WHERE
 category = 0
ORDER BY
 name,
 colid
OPEN cur
SET @buf = ''
FETCH NEXT FROM cur INTO @name, @id, @colid
WHILE (@@fetch_status <> -1)
BEGIN
SELECT @buf = cast([text] as varchar(8000)) 
FROM syscomments WHERE id = @id and colid = @colid
IF PATINDEX('%CREATE%PROCEDURE%',@buf) > 0
 BEGIN
 IF PATINDEX('%' + @name + '%', @buf) > 0
 BEGIN
 SET @found = 1
 END
 ELSE
 BEGIN
 SET @found = 0
 SELECT @name 'Wrong Object Name', @id 'Object ID'
 SELECT @SQL = '
 select 
 c.text AS ''SYSTEM TABLE TEXT''
 from
 sysobjects o join syscomments c on o.id = c.id
 where
 o.xtype = ''P'' and o.category = 0 and o.name = '''+@name+''''
 EXEC (@SQL)

 END
 END
 FETCH NEXT FROM cur INTO @name, @id, @colid
END
CLOSE cur
DEALLOCATE cur

syscomments table has another column, COLID, which can be used to retrieve all the rows that belong to the same stored procedure text.

In the script first we open a cursor that contains all the SPs in the database and then we go through the records and look for the text "CREATE PROCEDURE ...".

We then check to see if the "CREATE PROCEDURE..." line does contain the name that is registered in the sysobjects for the corresponding object identifier.

As it is mentioned in the comments of the that post if you are using SQL Server 2005 you won't face this problem since you can use the OBJECT_DEFINITION system function that takes care of all these for you.

Conclusion

Renaming Stored Procedures and User-Defined Functions must be avoided as a good programming practice.

If the business logic changes and you need a new version of the SP, it is much better to drop and re-create the stored procedure. Of course using a Versioning Application and applying Versioning Techniques is always highly recommended.