Identifying Unused Tables

I have inherited support for a database with many used tables. There
isn't any documentation on what is used or dead. I was hoping to run
traces and capture the objectid but the property doesn't work that
way.

Any good ideas would be appreciated.david_0 (dosberg@.yahoo.com) writes:
> I have inherited support for a database with many used tables. There
> isn't any documentation on what is used or dead. I was hoping to run
> traces and capture the objectid but the property doesn't work that
> way.

If the application uses only stored procedures, then it is fairly easy:
you search the code for all tables, and the keep track of which tables
does not give a hit. Alright, so that opens the question if there are
stored procedures which are not used.

If the application sends SELECT statement from the code, your best bet
is probably to search the application code, again once for each table.
If you find no references for a table you may want to make extra
precautions as it could be a lookup table that is being reference in
a foreign-ley constraint or trigger only. Or it may be part of a view
that is not in use.

Using the Profiler and the search the output is a possibility, but there
may be tables which are used in end-of-the-year functions only.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is what I came up with that will work for my situation. With a
little rework it could be used in other cases.

It doesn't do anything to detect inline sql on the client side. That
issue doesn't really apply in my situation.

DECLARE @.tbl TABLE(id INT IDENTITY(1,1), tblname VARCHAR(128),
found_flag CHAR(1))
DECLARE @.cnt int
DECLARE @.loop int
DECLARE @.parm varchar(255)

/*
*load variable with tables that aren't in sysdepends
*/
INSERT INTO @.tbl (tblname, found_flag)
SELECT
OBJECT_NAME(a.id),'N'
FROM
sysobjects a LEFT JOIN sysdepends b ON a.id=depid
WHERE
a.type='u' AND b.depid IS NULL
ORDER BY object_name(a.id)

/*
*setup variables for the loop
*/
SELECT @.cnt=MAX(id) FROM @.tbl
SET @.loop=1

/*
*take list of tables with no dependencies and look for job steps
that might reference them.
*/
WHILE @.loop <=@.cnt BEGIN

SELECT @.parm=tblname FROM @.tbl WHERE id=@.loop

IF EXISTS (SELECT job_id FROM msdb..sysjobsteps WHERE
CHARINDEX(@.parm,command)>0)
BEGIN
UPDATE @.tbl SET found_flag='Y' where id=@.loop
END
SET @.loop=@.loop+1
END

/*
*return table names not used in job steps or having object
dependencies
*/
SELECT tblname FROM @.tbl WHERE found_flag='N'

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9536EBF5675DYazorman@.127.0.0.1>...
> david_0 (dosberg@.yahoo.com) writes:
> > I have inherited support for a database with many used tables. There
> > isn't any documentation on what is used or dead. I was hoping to run
> > traces and capture the objectid but the property doesn't work that
> > way.
> If the application uses only stored procedures, then it is fairly easy:
> you search the code for all tables, and the keep track of which tables
> does not give a hit. Alright, so that opens the question if there are
> stored procedures which are not used.
> If the application sends SELECT statement from the code, your best bet
> is probably to search the application code, again once for each table.
> If you find no references for a table you may want to make extra
> precautions as it could be a lookup table that is being reference in
> a foreign-ley constraint or trigger only. Or it may be part of a view
> that is not in use.
> Using the Profiler and the search the output is a possibility, but there
> may be tables which are used in end-of-the-year functions only.|||david_0 (dosberg@.yahoo.com) writes:
> Here is what I came up with that will work for my situation. With a
> little rework it could be used in other cases.
>...
> INSERT INTO @.tbl (tblname, found_flag)
> SELECT
> OBJECT_NAME(a.id),'N'
> FROM
> sysobjects a LEFT JOIN sysdepends b ON a.id=depid
> WHERE
> a.type='u' AND b.depid IS NULL
> ORDER BY object_name(a.id)

Note however that sysdepends is at best approxamite. For instance if you
drop and recreate a table, you lose all entries in sysdepends for
the reference, so the table may appear unreferenced.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp