I have created a new database with copies of existing tables but changed the names of these tables, is there a SQL script that I can run (maybe using SysObjects) to change all references to these tables in all stored procedures?
DO NOT RELY ON INFORMATION_SCHEMA.ROUTINES because ROUTINE_DEFINITION
is only nvarchar(4000)
. You need to sys.sql_modules
where definition
is nvarchar(max)
try any of these to find the procedure that you need to modify:
SELECT DISTINCT
LEFT(s.name+'.'+o.name, 100) AS Object_Name,o.type_desc --, m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE m.definition Like '%'+@SearchValue+'%'
ORDER BY 1
SELECT
OBJECT_SCHEMA_NAME(m.object_id)+'.'+OBJECT_NAME(m.object_id) --, m.definition
FROM sys.sql_modules m
WHERE m.definition like '%whatever%'
SELECT
OBJECT_SCHEMA_NAME(m.object_id)+'.'+OBJECT_NAME(m.object_id), o.type_desc
--,m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition like '%whatever%'
you can uncomment m.definition
to list out the content, but I find it better to just ID all the procedures and then review them manually, because you don't want to run UPDATE commands on the system tables. Script out the necessary procedures, make the changes (search/replace or manually), and then run the scripts!!!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With