June 24, 2010

Generally we backed up tables and SPs for safer side with date suffix.
Below is cleaup script for those objects which might not be used after successfull deployment.

-- To drop all tables suffix with dates ( backup unused tables)

SELECT 'DROP TABLE '+s.name+'.'+o.name
FROM sys.schemas s
JOIN sys.objects o on s.schema_id=o.schema_id
AND o.type='U'
WHERE SUBSTRING(o.name,PATINDEX('%[^' + 'a-z._' + ']%',o.name),1) !=''

-- To drop all procedures suffix with dates

SELECT 'drop procedure '+s.name+'.'+o.name
FROM sys.schemas s
JOIN sys.objects o on s.schema_id=o.schema_id
AND o.type='P'
WHERE SUBSTRING(o.name,PATINDEX('%[^' + 'a-z._' + ']%',o.name),1) !=''

No comments: