Home > sql > Find Tables That Are Missing PK or Clustered Index

Find Tables That Are Missing PK or Clustered Index

I had a need to look for tables that violate company standards that every table must have a Primary Key as well as a Clustered Index. I put together a script that gives you each table name, whether it has a PK or not and whether it has a clustered index or not. You could add a where clause to only show those that violate one of these rules but the script as is shows all tables

SELECT 
    c.name + '.' + b.name as ObjectFullName, 
    'Y' as HasPrimaryKey,
    CASE i.index_id 
        WHEN '0' THEN 'N' 
        WHEN '1' THEN 'Y' 
    END as HasClusteredIndex  
FROM sys.tables b  
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
INNER JOIN sys.indexes i ON b.object_id = i.object_id and i.index_id IN (0,1)  
WHERE OBJECTPROPERTY(b.object_id, 'IsUserTable') = 1
AND EXISTS --Has PK
(
    SELECT a.name  
    FROM sys.key_constraints a  
    WHERE a.parent_object_id = b.OBJECT_ID  
    AND a.schema_id = c.schema_id  
    AND a.type = 'PK'
)
UNION 
SELECT 
    c.name + '.' + b.name as ObjectFullName, 
    'N' as HasPrimaryKey,
    CASE i.index_id 
        WHEN '0' THEN 'N' 
        WHEN '1' THEN 'Y' 
    END as HasClusteredIndex 
FROM sys.tables b  
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
INNER JOIN sys.indexes i ON b.object_id = i.object_id and i.index_id IN (0,1)  
WHERE OBJECTPROPERTY(b.object_id, 'IsUserTable') = 1
AND NOT EXISTS --No PK
(
    SELECT a.name  
    FROM sys.key_constraints a  
    WHERE a.parent_object_id = b.OBJECT_ID  
    AND a.schema_id = c.schema_id  
    AND a.type = 'PK'
)
ORDER BY HasPrimaryKey, HasClusteredIndex, ObjectFullName
Advertisements
Categories: sql
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: