How to get Tablename ,ForeignKey and Constraints

Question

I have written the Query in which i am getting TableName,Columns,Precision but how can i get Table related Foreign key and Constraints

    SELECT DISTINCT 
    QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'Schema',
    QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'Table',
    C.NAME AS 'Column',
    T.NAME AS 'DataType',
    C.max_length,
    C.is_nullable,
    c.precision,
    c.scale

FROM SYS.COLUMNS C
INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id]
INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id
WHERE tb.[is_ms_shipped] = 0 
ORDER BY tb.[Name]

Suugest me

Answer 1

Just try this

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
And OBJECT_NAME(parent_object_id) = 'TableName'

Or you might try this also (ref: http://sqlmag.com/t-sql/summarize-all-constraints-table)

SELECT OBJECT_NAME(constid) 'Constraint Name',
  constid 'Constraint ID',
  CASE (status & 0xF)
    WHEN 1 THEN 'Primary Key'
    WHEN 2 THEN 'Unique'
    WHEN 3 THEN 'Foreign Key'
    WHEN 4 THEN 'Check'
    WHEN 5 THEN 'Default'
    ELSE 'Undefined'
  END 'Constraint Type',
  CASE (status & 0x30)
    WHEN 0x10 THEN 'Column'
    WHEN 0x20 THEN 'Table'
    ELSE 'N/A'
  END 'Level'
  FROM sysconstraints
  WHERE id=OBJECT_ID('TableName')

more details as follow

By referencing this question SQL Server 2008- Get table constraints

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