Tuesday, April 24, 2012

Find tables in my database without a SQL Server clustered index

-- Find tables in my database without a SQL Server clustered index
SELECT TOP 1000 o.name, i.type_desc, o.type_desc, o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
 ON  i.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND i.type_desc = 'HEAP'
ORDER BY o.name
GO

-- To find out how many tables across all of my databases that do have a SQL Server clustered index
EXEC sp_MSforeachdb 'USE [?]
SELECT   ''?'' AS DatabaseName,
COUNT(o.name) AS Count
FROM sys.indexes i
INNER JOIN sys.objects o
 ON  i.object_id = o.object_id
WHERE o.type_desc = ''USER_TABLE''
AND i.type_desc = ''HEAP'''

No comments:

Post a Comment