How to get Tablename ,ForeignKey and Constraints

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get Tablename ,ForeignKey and Constraints

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-09-19 : 06:53:06

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]

Suggest me

P.V.P.MOhan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-19 : 07:08:23

You can start with this systemview

SELECT *
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA