Here’s a simple query to find all empty tables in your SQL Server database. We are using the dm_db_partition_stats DMV, which returns page and row-count information for every partition in the current database.
/* Use the dm_db_partition_stats DMV to list all empty tables in your database. */
;WITH Empty AS
(
SELECT
OBJECT_NAME(OBJECT_ID) [Table],
SUM(row_count) [Records]
FROM
sys.dm_db_partition_stats
WHERE
index_id = 0 OR index_id = 1
GROUP BY
OBJECT_ID
)
-- pull it back out
SELECT [Table],Records
FROM [Empty]
WHERE [Records] = 0
-- your results
Table Records
sysclones 0
sysseobjvalues 0
syspriorities 0
sysdbfrag 0
sysfgfrag 0
.........
......
..
This will provide you a little more information on the sys.dm_db_partition_stats DMV:
Please remember, this is not guaranteed to be an accurate record count. These counts are based on what is cached in memory, or stored on disk in varied system tables. Something I often refer people to is this post from Kalen Delaney. She shows different methods to get the row counts, and makes us ask ourselves what 'accurate' really means:
Wait. What about the tables that aren't used anymore, but are not empty? We often have unused tables in the database that still contain data. In my book, if they're not used, get rid of them. Thus, preserving server resources for what is required, rather than old/unused objects that are no longer needed.
No comments:
Post a Comment