Tuesday, July 26, 2016

SQL Server query All HEAPS

This query pulls all the tables in SQL Server that are HEAPS. That is all tables without a cluster.

SELECT        SCHEMA_NAME(o.schema_id) AS [Schema], OBJECT_NAME(i.object_id) AS Tables, p.rows AS ROWS, ius.user_seeks, ius.user_scans, ius.user_lookups,
                         ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, i.type_desc
FROM            sys.indexes AS i INNER JOIN
                         sys.objects AS o ON i.object_id = o.object_id INNER JOIN
                         sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT OUTER JOIN
                         sys.dm_db_index_usage_stats AS ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE        (i.type_desc = 'HEAP')
ORDER BY ius.user_updates DESC

0 Comments:

Post a Comment

<< Home