Simple Talk is now part of the Redgate Community hub - find out why

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;

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue