TSQL to rename and script new modified stored procedure

SELECT ‘EXEC SP_RENAME ”’ + name + ”’ , ”’ + ‘X_20170223_’ + name + ””
FROM sys.objects
WHERE type = ‘P’
and modify_date > ‘2016-09-09 16:37:38.913’
and name not like ‘X_%’
order by modify_date desc

SELECT M.definition
FROM sys.procedures P WITH(NOLOCK)
INNER JOIN sys.sql_modules M WITH(NOLOCK)ON M.object_id = P.object_id
WHERE EXISTS (SELECT name, create_date, modify_date
FROM sys.objects O
WHERE type = ‘P’
and modify_date > ‘2016-09-09 16:37:38.913’
and name not like ‘X_%’
AND O.name = P.name )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s