TSQL code to explore keys in a database.

/* list out all the constraints for a particular table, using the standard help system stored procedure — this includes check constraints, default constraints  and foreign keys.*/

 

EXEC sp_helpconstraint ‘Person.BusinessEntityAddress’;

 

/* use the information schema to find your current constraints on a particular table */

 

SELECT DISTINCT C.constraint_name

  FROM information_schema.table_constraints C

  WHERE constraint_type IN ( ‘PRIMARY KEY’, ‘UNIQUE’ )

    AND table_name LIKE ‘BusinessEntityAddress’;

 

/* using the information schema to determine what columns are used for each key constraint in the database */

 

SELECT c.TABLE_CATALOG + ‘.’ + c.TABLE_SCHEMA + ‘.’ + c.TABLE_NAME,

  Constraint_TYPE, c.CONSTRAINT_NAME,

  COALESCE(STUFF(

             (

             SELECT ‘, ‘ + cc.COLUMN_NAME

               FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cc

               WHERE cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME

                 AND cc.TABLE_CATALOG = c.TABLE_CATALOG

                 AND cc.TABLE_SCHEMA = c.TABLE_SCHEMA

               ORDER BY ORDINAL_POSITION

             FOR XML PATH(), TYPE

             ).value(‘.’, ‘varchar(max)’), 1, 2, ), ‘?’) AS Columns

  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u

    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c

      ON u.CONSTRAINT_NAME = c.CONSTRAINT_NAME

  WHERE CONSTRAINT_TYPE IN ( ‘PRIMARY KEY’, ‘UNIQUE’ )

  –CHECK, UNIQUE, PRIMARY KEY or FOREIGN KEY

  GROUP BY c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, Constraint_TYPE,

  c.CONSTRAINT_NAME

  ORDER BY c.TABLE_CATALOG + ‘.’ + c.TABLE_SCHEMA + ‘.’ + c.TABLE_NAME;

 

/* use the information schema to find tables with no primary key on them */

 

SELECT t.TABLE_CATALOG + ‘.’ + t.TABLE_SCHEMA + ‘.’ + t.TABLE_NAME

  FROM INFORMATION_SCHEMA.TABLES t

    LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c

      ON t.TABLE_CATALOG = c.TABLE_CATALOG

     AND t.TABLE_SCHEMA = c.TABLE_SCHEMA

     AND t.TABLE_NAME = c.TABLE_NAME

     AND CONSTRAINT_TYPE = ‘PRIMARY KEY’

  WHERE t.TABLE_TYPE = ‘BASE TABLE’ AND c.TABLE_NAME IS NULL;

 

–List out all your heaps, using the object catalog views.

 

SELECT OBJECT_NAME(object_ID) + ‘ is a heap.’

  FROM sys.indexes

  WHERE type_desc = ‘HEAP’;

 

–check to see if you have any heaps with indexes on them, using the object catalog views

 

SELECT OBJECT_SCHEMA_NAME(b.object_ID) + ‘.’ + OBJECT_NAME(b.object_ID)

       + ‘ has a ‘ + LOWER(b.type_desc) + ‘ index but no primary key.’

  FROM sys.indexes a INNER JOIN sys.indexes b ON a.object_id = b.object_id

  WHERE a.type = 0 AND b.type <> 0;

 

–display the tables and primary keys where the primary key is non-clustered, using the object catalog views

 

SELECT OBJECT_SCHEMA_NAME(object_ID) + ‘.’ + OBJECT_NAME(object_ID), name

  FROM sys.indexes

  WHERE is_primary_key <> 0 AND type_desc = ‘NONCLUSTERED’;

 

–Tables that have both primary keys and unique indexes using the object catalog views

 

SELECT OBJECT_SCHEMA_NAME(b.object_ID) + ‘.’ + OBJECT_NAME(b.object_ID)

       + ‘ has both a primary key and unique index(es)’

  FROM sys.indexes a

    INNER JOIN sys.indexes b

      ON a.object_id = b.object_id

     AND a.is_primary_Key <> 0

     AND b.is_unique <> 0

     AND a.name <> b.name

  WHERE OBJECT_SCHEMA_NAME(b.object_ID) <> ‘sys’

  GROUP BY b.object_ID;

 

/* tables, the number of indexes, unique indexes, unique keys, using the object catalog views*/

 

SELECT OBJECT_SCHEMA_NAME(a.object_ID) + ‘.’ + OBJECT_NAME(a.object_ID) AS [Table],

  SUM(CASE WHEN a.name IS NULL THEN 0 ELSE 1 END) AS [indexes],

  SUM(CASE WHEN a.is_unique <> 0 THEN 1 ELSE 0 END) AS Unique_indexes,

  SUM(CASE WHEN a.is_unique_constraint <> 0 THEN 1 ELSE 0 END) AS [Unique Key],

  SUM(CASE WHEN a.is_primary_key <> 0 THEN 1 ELSE 0 END ) AS [Primary Key],

  SUM(CASE WHEN a.type = 1 THEN 1 ELSE 0 END ) AS [Clustered],

  SUM(CASE WHEN a.type = 2 THEN 1 ELSE 0 END ) AS [Non-clustered],

  SUM(CASE WHEN a.type = 3 THEN 1 ELSE 0 END ) AS [XML],

  SUM(CASE WHEN a.type = 4 THEN 1 ELSE 0 END ) AS [Spatial],

  SUM(CASE WHEN a.type = 5 THEN 1 ELSE 0 END ) AS [Clustered Columnstore],

  SUM(CASE WHEN a.type = 6 THEN 1 ELSE 0 END ) AS [Nonclustered columnstore]

  FROM sys.indexes a

    INNER JOIN sys.tables ON a.object_ID = sys.tables.object_id

  WHERE OBJECT_SCHEMA_NAME(a.object_ID) <> ‘sys’

  — and a.name is not null

  GROUP BY a.object_ID;

 

/* list all the key constraints and their tables, using the object catalog views*/

 

SELECT OBJECT_SCHEMA_NAME(keys.Parent_Object_ID) + ‘.’

       + OBJECT_NAME(keys.Parent_Object_ID) AS TheTable, –table & Schema

  keys.name AS TheKey, –the name of the key

  REPLACE(LOWER(MAX(type_desc)), ‘_’, ‘ ‘) AS [Type],

  CASE WHEN COUNT(*) = 1

      THEN COL_NAME(TheColumns.Object_Id, MIN(TheColumns.Column_Id))

         ELSE –otherwise the list of columns

           COALESCE(STUFF( (

             SELECT ‘, ‘ + COL_NAME(Ic.Object_Id, Ic.Column_Id)

                    + CASE WHEN Is_Descending_Key <> 0 THEN ‘ DESC’ ELSE

                      END

               FROM Sys.Index_Columns AS Ic

               WHERE Ic.Index_Id = TheColumns.Index_Id

                 AND Ic.Object_Id = TheColumns.Object_Id

                 AND is_included_column = 0

               ORDER BY Key_Ordinal

             FOR XML PATH(), TYPE).value(‘.’, ‘varchar(max)’), 1, 2, ), ‘?’)

  END AS Columns

  FROM sys.Key_Constraints keys

    INNER JOIN sys.Index_columns TheColumns

      ON keys.Parent_Object_ID = TheColumns.Object_ID

     AND unique_index_ID = index_ID

  GROUP BY TheColumns.object_ID, TheColumns.Index_Id, keys.name,

  keys.schema_ID, keys.Parent_Object_ID

  ORDER BY keys.name;

 

/* identify tables with a unique constraint that allows nulls, using the object catalog views*/

 

SELECT DISTINCT OBJECT_SCHEMA_NAME(keys.Parent_Object_ID) + ‘.’

                + OBJECT_NAME(keys.Parent_Object_ID) AS TheTable

  FROM sys.Key_Constraints keys

    INNER JOIN sys.Index_columns TheColumns

      ON keys.Parent_Object_ID = TheColumns.Object_ID

     AND unique_index_ID = index_ID

    INNER JOIN sys.columns c

      ON TheColumns.object_ID = c.object_ID

     AND TheColumns.column_ID = c.column_ID

  WHERE type = ‘UQ’ AND is_nullable = 1;

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

  • 5638 views

  • Rate
    [Total: 1    Average: 5/5]