02 December 2013

TSQL code to explore keys in a database.

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

Keep up to date with Simple-Talk

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

This post has been viewed 5478 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Phil Factor

Google + To translate this article...

View all articles by Phil Factor

Related articles

Also in Blogs

Ten Years Later

It’s hard to believe, but Simple Talk has now been going for over ten years. Thanks to brilliant pieces from our writers, hard work from the team here, and countless valuable contributions from you, our readers, we’re currently receiving one million page views a month, and sitting on a hefty 2,500 articles from over 370 … Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up