How to find unused tables? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

How to find unused tables?

Author

Topic

learntsql

524 Posts

Posted - 2011-10-27 : 05:38:17

Hi All,

Is there any way t find unsued tables in database.
i.e which is not used for any specific reason.
TIA.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 06:10:16

[code]
SELECT DISTINCT OBJECT_NAME(I.OBJECT_ID)

FROM SYS.INDEXES AS I

INNER JOIN SYS.OBJECTS AS O

ON I.OBJECT_ID = O.OBJECT_ID

WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

AND I.INDEX_ID

NOT IN (SELECT S.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

WHERE S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = DB_ID(db_name()))

ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learntsql

524 Posts

Posted - 2011-10-27 : 06:14:46

Thanks Visakh,

Does this script work for the tables don't have any index on them?
TIA.

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-27 : 11:27:18

Nice question ,So take this method
With SQL Server 2005, you can use the dynamic management view sys.dm_db_index_usage_stats. The name says "index" but that's a little misleading - every table has an entry in here, even if it doesn't have any indexes. Here's a useful query from SQL Magazine:

SELECT
t.name AS 'Table',
SUM(i.user_seeks + i.user_scans + i.user_lookups)
AS 'Total accesses',
SUM(i.user_seeks) AS 'Seeks',
SUM(i.user_scans) AS 'Scans',
SUM(i.user_lookups) AS 'Lookups'
FROM
sys.dm_db_index_usage_stats i RIGHT OUTER JOIN
sys.tables t ON (t.object_id = i.object_id)
GROUP BY
i.object_id,
t.name
ORDER BY [Total accesses] DESC
Here's the original article:

http://www.sqlmag.com/Article/ArticleID/53878/sql_server_53878.html

paul Tech

learntsql

524 Posts

Posted - 2011-10-28 : 00:04:45

ThankQ very much paul Tech.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-28 : 04:54:11

Just bear in mind that those queries will give you tables that have not been used since the last time SQL Server started. So if your server is restarted frequently, that could list tables that are seldom used.

Don't drop any tables without monitoring for at least a month, probably more.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources