Database clean-up
-
As a developer, I have been requested to clean up our database. Remove unused functions, stored procedures and views that are not used by the current web application. Is there a simple way to accomplish this task. If there is no help I will just start searching through the code and database.
Any assistance is appreciated!
Rick
-
I use the following script to find single items. You could possibly wrap this in a select from sysobjects and syscolumns to generate some sort of cross reference. Of course there may be a more complete script somewhere in the script library.
/*
Search syscomments for table or field changes that might affect
other views, rules, defaults, triggers or stored procedures.
*/
DECLARE @Msg varchar(255)
DECLARE @SearchString varchar(253)
SELECT @SearchString = 'Account'
SET NOCOUNT ON
DECLARE @Pattern varchar(255)
SELECT @Pattern = '%' + @SearchString + '%'
SELECT @Msg = 'Seaching for ' + @Pattern
PRINT @Msg
PRINT ''
PRINT 'Found in object names...'
SELECT sObj.Name, sObj.Type
FROM sysobjects sObj
WHERE sObj.name LIKE @Pattern
GROUP BY sObj.Name, sObj.Type
PRINT ''
PRINT 'Found in field names...'
select sObj.name as ObjectName, sCol.name as Field
from syscolumns sCol
inner join sysobjects sObj
on sCol.id = sObj.id
WHERE sCol.name LIKE @Pattern
GROUP BY sObj.Name, sCol.name
PRINT ''
PRINT 'Found in procedures...'
SELECT sObj.Name
FROM sysobjects sObj
INNER JOIN syscomments sComm
ON sObj.id = sComm.id
WHERE sComm.Text LIKE @Pattern
AND sObj.Type = 'P'
GROUP BY sObj.Name
PRINT ''
PRINT 'Found in views...'
SELECT sObj.Name
FROM sysobjects sObj
INNER JOIN syscomments sComm
ON sObj.id = sComm.id
WHERE sComm.Text LIKE @Pattern
AND sObj.Type = 'V'
GROUP BY sObj.Name
PRINT ''
PRINT 'Found in other objects...'
SELECT sObj.Name, sObj.Type
FROM sysobjects sObj
INNER JOIN syscomments sComm
ON sObj.id = sComm.id
WHERE sComm.Text LIKE @Pattern
AND sObj.Type NOT IN ( 'V', 'P' )
GROUP BY sObj.Name, sObj.TypeHope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Phill,
Thank you for the SQL!
Cleaning up is difficult for kids and adults.
Rick
-
Could also look at sysdepends, though its not 100%. You could also potentially have cross database references. You could run profiler for a day or two, build a list of every object referenced. I think Lockwood Tech sells a tool that does db clean up too.
One other thing, I typically rename objects I think are dead, putting zz_ in front of them. Then if something breaks I didn't catch, I just rename back. Once they sit there for a while, then you can remove.
Andy
-
...there is also sp_depends
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply