Find Commmon SQL Objects (to drop)

We often inload a lot of data and use views to point to the most recent table. Periodically I've been deleting the old table with this script and thought I'd post it, hope it helps.

--// Find Commmon Objects

SELECT
DISTINCT
S.NAME
,'sp_helpText ''' + S.NAME +'''' [ExecToView]
,CASE
WHEN S.Type = 'P' THEN (CASE WHEN d.depid IS NOT NULL then '--//' else '' END) + 'DROP PROC ' + S.NAME
WHEN S.Type = 'U' THEN (CASE WHEN d.depid IS NOT NULL then '--//' else '' END) + 'DROP TABLE ' + S.NAME
WHEN S.Type = 'V' THEN (CASE WHEN d.depid IS NOT NULL then '--//' else '' END) + 'DROP VIEW '
ELSE 'NOT IN NORM'
END [Action]
,CASE
WHEN S.Type = 'P' THEN 'Stored Procedure'
WHEN S.Type = 'U' THEN 'User Table'
WHEN S.Type = 'V' THEN 'View'
ELSE 'NOT IN NORM'
END [ObjectType]
FROM
SYSOBJECTS S (NOLOCK)
LEFT JOIN sys.sysdepends d ON d.depid = s.id
WHERE
--S.TYPE = 'P'
S.TYPE = 'U'
--S.TYPE = 'V'
AND S.NAME LIKE '%TableNamePrefix%'
ORDER BY
S.Name

Comments :

0 comments to “Find Commmon SQL Objects (to drop)”