Technical Article

Get the all the tables on the server which do not have Clustered index

,

Get the list of the tables on the server which dont have the clustered index.

--Verify if any table dnt have the cluster key 

DECLARE @tbl TABLE
(
	ID INT IDENTITY(1,1),
	DBName NVARCHAR(100),
	TableName  NVARCHAR(100)
)	
	
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
	SELECT ''?'',ts.Name 
	  FROM ?.sys.tables ts
 LEFT JOIN ?.sys.indexes si
	    ON  ts.object_id = si.object_id
	   AND  si.type = 1
INNER JOIN ?. sys.databases d
		ON d.Name=''?'' AND d.name NOT IN ( ''tempdb'',''master'',''msdb'',''ReportServer'')
     WHERE  si.index_id IS NULL
	'
INSERT INTO @tbl
EXECUTE sp_MSforeachdb @SQL

select * from @tbl

Rate

4 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (5)

You rated this post out of 5. Change rating