Click here to monitor SSC

Simple-Talk columnist

TSQL code to explore keys in a database.

Published 2 December 2013 2:26 pm

 

 

/*These queries are all to explore and investigate the keys of a table or database, and are designed to accompany my article on primary keys that is here. You'll find, I hope, that is is quite easy to modify them for other purposes */

 

USE AdventureWorks; —or whatever

GO

/* 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

 

Leave a Reply