Thursday, 6 November 2014

Query to list the tables which doesn't have Primary key

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE  TABLE_SCHEMA = 'dbo'
AND TABLE_NAME NOT IN
(
    SELECT 
    Tab.TABLE_NAME
    from
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY'
    AND Tab.table_schema = 'dbo'
)

No comments:

Post a Comment