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
Find Commmon SQL Objects (to drop)
Bob Mihada, Wednesday, May 28, 2008
Labels:
SQL maintenance
Subscribe to:
Post Comments (Atom)
Comments :
Post a Comment