How to change schema name of a table in all stored procedures

Question

I know how to change the schema of a table in SQL server 2005:

ALTER SCHEMA NewSchama TRANSFER dbo.Table1

But how can i check and/or alter stored procedures that use the old schema name?

Sorry: I mean: There are stored procedures that have the old schema name of the table in the sql of the stored procedure... How can i edit all the stored procedures that have the dbo.Table1 in the body of the procedure...

Answer 1

Get a list of dependent objects by right-clicking on the table before you change the schema and then look at what is dependent on the table, make a list and then change those. There is, however, always a possibility that you'll miss something because it is possible to break the dependencies SQL server tracks.

But the best way would be to script the database out into a file and then do a search for the table name, make a list of all of the sprocs where it needs to be changed and then add those to the script to change the schema of the table.

Answer 2

• Use Tasks>Generate Scripts in SSMS to provide a series of Create Proc scripts.
• Use Find & Replace (Alt - H) to change 'Create ' to 'Alter '
• Use F & R to change 'dbo.Table1' to 'dbo.Table2'
• Then Execute (F5) to modify all the affected SPs.

Simple but effective.

Answer 3

DECLARE @SearchObject VARCHAR(100)

SET @SearchObject = 'searchable_table_name' -- change 'searchable_table_name' to the table name what you want to search

SELECT sc.name [Search Object], so.name [Container Object],
CASE so.xtype WHEN 'U' THEN 'Table' WHEN 'P' THEN 'Stored Procedure' WHEN 'F' THEN 'User Defined Function' ELSE 'Other' END as [Container Object Type]

FROM sysobjects so

INNER JOIN syscolumns sc ON so.id = sc.id

WHERE sc.name LIKE '%' + @SearchObject + '%' AND so.xtype IN ('U','P','F') -- U : Table , P : Stored Procedure, F: User defined functions(udf)

ORDER BY [Container Object] ASC

-- Display the stored procedures that contain the table name requested.

Select text From syscomments Where text like '%from ' + @SearchObject + '%'

(Select id From sysobjects Where type='P' and name = '')

-- Display the content of a specific stored procedure (found from above)

--Exec sp_helptext 'DeleteAssetByID'