SQL Server, Unused tables for a long time (How to get rid of unnecesarry tables)?

Question

Is there a way to find the unused tables which are nothing else but rubbish in database ?

Answer 1

If you use a source control, see the latest database script. Its the easiest way.

Answer 2

I think you might find the database statistics would the most profitable place to look. it should be able to tell you which tables are read from most, and which ones are updated most. If you find tables which are neither read from nor written to, they're probably not much used.

I'm not sure what database statistics are available in SQL Svr 2000 though.

However, rather than simply looking at which tables are not much used, wouldn't a better approach be to examine what each table holds and what it if for, so you gain a proper understanding of the design? In this case you would then be able to properly judge what is necessary and what is not.

It is a concern that you don't know what source control is though (It's a way of managing changing to files - usually sorce code - so you can keep track of who changed what, when and why.) Anything larger than a one-man project (and even some one-man projects) should use it.

Answer 3

The only way I can think of working out if a table is being used is to use sys.dm_db_index_usage_stats. The caveat with this is that it only records the usage of the tables since the SQL Service was last started. So bearing that in mind, you can use the following query:

  SELECT DISTINCT
         OBJECT_SCHEMA_NAME(t.[object_id])   AS 'Schema'
       , OBJECT_NAME(t.[object_id])          AS 'Table/View Name'
       , CASE  WHEN rw.last_read > 0 THEN rw.last_read END  AS 'Last Read'
       , rw.last_write                        AS 'Last Write'
       , t.[object_id]
    FROM sys.tables                           AS t
    LEFT JOIN sys.dm_db_index_usage_stats     AS us
      ON us.[object_id]    = t.[object_id]
     AND us.database_id    = DB_ID()
    LEFT JOIN
         ( SELECT MAX(up.last_user_read)      AS 'last_read'
                , MAX(up.last_user_update)    AS 'last_write'
                , up.[object_id]
             FROM (SELECT last_user_seek
                        , last_user_scan
                        , last_user_lookup
                        , [object_id]
                        , database_id
                        , last_user_update, COALESCE(last_user_seek, last_user_scan, last_user_lookup,0) AS null_indicator
                     FROM sys.dm_db_index_usage_stats) AS sus 
                  UNPIVOT(last_user_read FOR read_date IN(last_user_seek, last_user_scan, last_user_lookup, null_indicator)) AS up
            WHERE database_id = DB_ID()
         GROUP BY up.[object_id]
         ) AS rw
      ON rw.[object_id] = us.[object_id]
ORDER BY [Last Read]
       , [Last Write]
       , [Table/View Name];

;

Answer 4

You can use sp_depends to confirm any depedencies for the suspect tables.

Here is an example:

CREATE TABLE Test (ColA INT)
GO

CREATE PROCEDURE usp_Test AS 
BEGIN
  SELECT * FROM Test
END
GO

CREATE FUNCTION udf_Test() 
RETURNS INT 
AS 
BEGIN 
  DECLARE @t INT 
  SELECT TOP 1 @t = ColA FROM Test 
  RETURN @t 
END
GO

EXEC sp_depends 'Test'

/** Results **/

In the current database, the specified object is referenced by the following:
name          type
-----         ----------------
dbo.udf_Test  scalar function
dbo.usp_Test  stored procedure

This approach has some caveats. Also this won't help with tables that are accessed directly from an application or other software (i.e. Excel, Access, etc.).

To be completely thorough, I would recommend using SQL Profiler in order to monitor your database and see if and when these tables are referenced.