SQL Server 2008- Get table constraints

Question

Could you help me frame a query that retrieves the constraints in all the tables  the count of constraints in each table  and also display NULL for tables that do NOT have any constraints  This is what I have so far  Select  SysObjects  Name  As  Constraint Name            Tab  Name  as  Table Name           Col  Name  As  Column Name  From SysObjects Inner Join   Select  Name   ID  From SysObjects  As Tab On Tab  ID    Sysobjects  Parent Obj   Inner Join sysconstraints On sysconstraints Constid   Sysobjects  ID   Inner Join SysColumns Col On Col  ColID    sysconstraints  ColID  And Col  ID    Tab  ID  order by  Tab   Name

User · Answer

Here s a script to get foreign keys       SELECT TOP 150         t  name  AS  Table          cols  name  AS  Column          t2  name  AS  Referenced Table          c2  name  AS  Referenced Column          constr  name  AS  Constraint    FROM sys tables t  INNER JOIN sys foreign keys constr ON constr parent object id   t object id  INNER JOIN sys tables t2 ON t2 object id   constr referenced object id  INNER JOIN sys foreign key columns fkc ON fkc constraint object id   constr object id  INNER JOIN sys columns cols ON cols object id   fkc parent object id AND cols column id   fkc parent column id  INNER JOIN sys columns c2 ON c2 object id   fkc referenced object id AND c2 column id   fkc referenced column id  --WHERE t  name  IN                  ORDER BY t  Name   cols  name

User · Answer

I used the following query to retrieve the information of constraints in the SQL Server 2012  and works perfectly  I hope it would be useful for you   SELECT      tab name AS  Table       tab id AS  Table Id       constr name AS  Constraint Name       constr xtype AS  Constraint Type       CASE constr xtype WHEN  PK  THEN  Primary Key  WHEN  UQ  THEN  Unique  ELSE    END AS  Constraint Name       i index id AS  Index ID       ic column id AS  Column ID       clmns name AS  Column Name       clmns max length AS  Column Max Length       clmns precision AS  Column Precision       CASE WHEN clmns is nullable   0 THEN  NO  ELSE  YES  END AS  Column Nullable       CASE WHEN clmns is identity   0 THEN  NO  ELSE  YES  END AS  Column IS IDENTITY  FROM SysObjects AS tab INNER JOIN SysObjects AS constr ON constr parent obj   tab id AND constr type    K   INNER JOIN sys indexes AS i ON   i index id  gt  0 and i is hypothetical   0  AND  i object id tab id  AND i name   constr name   INNER JOIN sys index columns AS ic ON  ic column id  gt  0 and  ic key ordinal  gt  0 or ic partition ordinal   0 or ic is included column    0                                        AND  ic index id CAST i index id AS int                                       AND ic object id i object id  INNER JOIN sys columns AS clmns ON clmns object id   ic object id and clmns column id   ic column id WHERE tab xtype    U  ORDER BY tab name

User · Answer

You should use the current sys catalog views  if you re on SQL Server 2005 or newer - the sysobjects views are deprecated and should be avoided  - check out the extensive MSDN SQL Server Books Online documentation on catalog views here   There are quite a few views you might be interested in    sys default constraints for default constraints on columns sys check constraints for check constraints on columns sys key constraints for key constraints  e g  primary keys  sys foreign keys for foreign key relations   and a lot more - check it out   You can query and join those views to get the info needed - e g  this will list the tables  columns and all default constraints defined on them   SELECT      TableName   t Name      ColumnName   c Name      dc Name      dc definition FROM sys tables t INNER JOIN sys default constraints dc ON t object id   dc parent object id INNER JOIN sys columns c ON dc parent object id   c object id AND c column id   dc parent column id ORDER BY t Name

User · Answer

I tried to edit the answer provided by marc s however it wasn t accepted for some reason  It formats the sql for easier reading  includes the schema and also names the Default name so that this can easily be pasted into other code     SELECT SchemaName   s Name           TableName   t Name           ColumnName   c Name           DefaultName   dc Name           DefaultDefinition   dc Definition     FROM sys schemas                s     JOIN sys tables                 t   on  t schema id            s schema id     JOIN sys default constraints    dc  on  dc parent object id    t object id      JOIN sys columns                c   on  c object id            dc parent object id                                         and c column id            dc parent column id ORDER BY s Name  t Name  c name

User · Answer

You Can Get With This Query   Unique Constraint   Default Constraint With Value   Foreign Key With referenced Table And Column   And Primary Key Constraint   Select C     Select definition From sys default constraints Where object id   C object id  As dk definition   Select definition From sys check constraints Where object id   C object id  As ck definition   Select name From sys objects Where object id   D referenced object id  As fk table   Select name From sys columns Where column id   D parent column id And object id   D parent object id  As fk col From sys objects As C Left Join  Select   From sys foreign key columns  As D On D constraint object id   C object id  Where C parent object id    Select object id From sys objects Where type    U  And name    Table Name Here

User · Answer

SELECT      oj   name   TableName        ac   name   ColumnName        dc   name   DefaultConstraintName        dc   definition  FROM     sys default constraints  dc       sys all objects  oj       sys all columns  ac  WHERE                 oj   type  IN   u    AND           oj   object id     dc   parent object id   AND           oj   object id     ac   object id   AND           dc   parent column id     ac   column id

[sql] SQL Server 2008- Get table constraints

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to constraints