TSQL Script- To find tables without primary key & heap tables


SELECT  S.name+'.'+T.name AS Tablename 
FROM sys.tables T 
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id 
WHERE T.type = 'U' 
AND NOT EXISTS 
( 
SELECT C.name 
FROM  sys.key_constraints C 
WHERE  C.parent_object_id = T.object_id 
AND  C.schema_id = S.schema_id 
AND  C.type = 'PK')




SELECT T.name Heap_Tables 
FROM SYS.TABLES T 
INNER JOIN SYS.INDEXES I 
ON T.object_id = I.object_id
AND I.type_desc='HEAP'



See Also:


No comments: