January 19, 2014 at 8:14 pm
I want to see foreign key relationship of a table say student_tbl
I want to see which tables are connected with this.
what is the query to see the details ?
January 19, 2014 at 8:36 pm
Have a look into the information_schema view.
This one will give you a list
select *
from information_schema.table_constraints
and you can glean extra information from other information_schema views for the constraint_name.
January 20, 2014 at 5:28 am
I'd prefer querying the information schema too, but there is always sp_help
EXEC sys.sp_help 'sales.salesorderheader'
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 20, 2014 at 8:34 am
p.s. you can also see this visually if you create a diagram, and add the table you're interested in. Then right-click the table and choose 'add related tables.'
January 20, 2014 at 8:42 am
Here's a script to help with that
http://jasonbrimhall.info/2011/11/16/table-hierarchy-goes-cs/
As the post cautions, the script slows significantly when dealing with circular references.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 20, 2014 at 9:42 am
spectra (1/19/2014)
I want to see foreign key relationship of a table say student_tblI want to see which tables are connected with this.
what is the query to see the details ?
This will list all foreign key relationships in a database
USE [somedb]
SELECT 'The table ' + QUOTENAME(SCHEMA_NAME(k.schema_id)) + '.' +
QUOTENAME(OBJECT_NAME(k.parent_object_id)) +
CHAR(10) + ' has Foreign Key constraint ' + QUOTENAME(k.name) +
' on column [' + c.name + CHAR(10) + '] and References table ' +
QUOTENAME(SCHEMA_NAME(k.schema_id)) +
'.' + QUOTENAME(OBJECT_NAME(fc.referenced_object_id)) +
CHAR(10) + 'on column [' + c2.name + ']'
FROM sys.foreign_keys k
INNER JOIN sys.foreign_key_columns fc
ON k.object_id = fc.constraint_object_id
INNER JOIN sys.columns c ON fc.parent_object_id = c.object_id
and fc.parent_column_id = c.column_id
INNER JOIN sys.columns c2 ON fc.referenced_object_id = c2.object_id
and fc.referenced_column_id = c2.column_id
WHERE k.is_ms_shipped = 0
ORDER BY OBJECT_NAME(k.parent_object_id)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 20, 2014 at 10:36 am
Nice script Perry, if you don't mind a small improvement to cover cases where the parent table is in a different schema
SELECT 'The table ' + QUOTENAME(SCHEMA_NAME(k.schema_id)) + '.' +
QUOTENAME(OBJECT_NAME(k.parent_object_id)) +
CHAR(10) + ' has Foreign Key constraint ' + QUOTENAME(k.name) +
' on column [' + c.name + CHAR(10) + '] and References table ' +
QUOTENAME(rtrim(schema_name(ObjectProperty(fc.referenced_object_id,'schemaid')))) +
'.' + QUOTENAME(OBJECT_NAME(fc.referenced_object_id)) +
CHAR(10) + 'on column [' + c2.name + ']'
FROM sys.foreign_keys k
INNER JOIN sys.foreign_key_columns fc
ON k.object_id = fc.constraint_object_id
INNER JOIN sys.columns c ON fc.parent_object_id = c.object_id
and fc.parent_column_id = c.column_id
INNER JOIN sys.columns c2 ON fc.referenced_object_id = c2.object_id
and fc.referenced_column_id = c2.column_id
WHERE k.is_ms_shipped = 0
ORDER BY OBJECT_NAME(k.parent_object_id)
---------------------------------------------------------------------
January 20, 2014 at 11:38 am
Fill your boots mate, I'd appreciate your input 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply