Sql Server script to rename the table name in all the stored procedures
Sql server code sample to rename the table name used in all the sql server stored procedures.
A small procedure is used here to rename all the tables in the stored procedure text
Introduction
We may come across a requirement where we need to change the name of a Table, What would happen is we need to modify all the the Stored Procedures where we use the specific table, Instead of modifying all the Procedures manually, I have written a procedure to help you in doing it.Stored Procedure Used
This stored procedure is written in TSQL which helps to rename the spcific table in all the Stored procedures.
CREATE PROC dbo.ReplaceTableNamesInRelatedProcedure
(
@OldTableName VARCHAR(255)
, @NewTableName VARCHAR(255)
)
/* -- Comments --
Written by Alwyn on 10th January 2012
To rename a Table_Name in all the Stored Procedures
*/
AS
SET NOCOUNT ON;
BEGIN
IF ISNULL(@OldTableName, '') = ''
BEGIN
SELECT 'Please provide value for @OldTableName' AS [Message]
RETURN -1
END
IF ISNULL(@NewTableName, '') = ''
BEGIN
SELECT 'Please provide value for @NewTableName' AS [Message]
RETURN -1
END
--Play with Cursor
DECLARE @ProcedureText VARCHAR(MAX)
, @ProcedureName VARCHAR(255)
, @Table_Name VARCHAR(255)
, @SQL NVARCHAR(MAX)
SET @Table_Name = '%' + @OldTableName + '%'
DECLARE MyCur CURSOR
FAST_FORWARD FOR
SELECT SO.name
, SC.text
FROM sys.syscomments SC
INNER JOIN sys.objects SO
ON SO.object_id = SC.id
WHERE SO.type = 'P'
AND SC.text LIKE @Table_Name
OPEN MyCur
FETCH NEXT FROM MyCur INTO @ProcedureName, @ProcedureText
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
BEGIN TRY
SET @SQL = 'DROP PROC '+ @ProcedureName
EXECUTE SP_EXECUTESQL @SQL
SET @SQL = REPLACE(@ProcedureText, @OldTableName, @NewTableName)
EXECUTE SP_EXECUTESQL @SQL
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [Error Message]
, ERROR_PROCEDURE() AS [Error Procedure]
, ERROR_SEVERITY() AS [Error Severity]
ROLLBACK TRAN
CLOSE MyCur
DEALLOCATE MyCur
RETURN -1
END CATCH
COMMIT TRAN
FETCH NEXT FROM MyCur INTO @ProcedureName, @ProcedureText
END
CLOSE MyCur
DEALLOCATE MyCur
SELECT 'All the TableName '+ @OldTableName + ' In the Stored Procedures are renamed to '+@NewTableName+' Successfully.' AS [Message]
END
This procedure uses a Sql Query to get all the Stored procedures which has the given table to be replaced
SELECT SO.name
, SC.text
FROM sys.syscomments SC
INNER JOIN sys.objects SO
ON SO.object_id = SC.id
WHERE SO.type = 'P'
AND SC.text LIKE '%Table1%'
A fast forward cursor has been used to move through the result set and replace the Table with the newly changed name
After Executing the Procedure you can call the Procedure using the below code
For sample, i have replaced the Table1 with table2
EXEC dbo.ReplaceTableNamesInRelatedProcedure 'Table1', 'Table2'Conclusion
Using this Stored procedure you can easily rename the tablename in all the Stored Procedures. Make sure the table name to be renamed has all the columns same as the Renamed Table Name, i.e Table2 should have the same columns as like of Table1.
thanku so much u are great..u have make my work very easy