September 1, 2015 at 1:46 am
Below I have a query which list the relations (constraints) between tables.
I want to list all the relations which are visible in the Database Diagrams.
The list is not complete. How do I get a complete list ?
Ben
--
-- Query to show the relations (constraints) between tables.
-- This does not show the complete list
--
SELECT A.constraint_name,
B.table_name AS Child,
C.table_name AS Parent,
D.column_name AS Child_field,
D.ordinal_position,
e.column_name AS Parent_field,
e.ordinal_position AS ordinal_position_parent
FROM
information_schema.referential_constraints A
INNER JOIN
information_schema.table_constraints B
ONA.constraint_name = B.constraint_name
AND A.constraint_schema = B.constraint_schema
AND A.constraint_catalog = B.constraint_catalog
INNER JOIN information_schema.table_constraints C
ON A.unique_constraint_name = C.constraint_name
AND A.unique_constraint_schema = C.constraint_schema
AND A.Unique_constraint_catalog = C.constraint_catalog
INNER JOIN information_schema.key_column_usage D
ON A.constraint_name = D.constraint_name
AND A.constraint_schema = D.constraint_schema
AND A.constraint_catalog = D.constraint_catalog
INNER JOIN information_schema.key_column_usage e
ON A.unique_constraint_name = e.constraint_name
AND A.unique_constraint_schema = e.constraint_schema
AND A.unique_constraint_catalog = e.constraint_catalog
AND D.ordinal_position = e.ordinal_position
--
-- The list of constraints. (Here more constraints / relations are visible).
--
SELECT A.constraint_name,*
FROM
information_schema.referential_constraints A
September 1, 2015 at 9:00 am
Hello All,
Allthough not completely elegant, I think I found a solution (???) to the stated problem.
What I do not understand is who is the parent and who is the child.
With relational constraints I use the rules.
A parent exists first.
A parent can have zero, 1 or more children.
A child must have an existing parent. (NULL's are an exception to this).
In the table sys.foreign_key_columns, it looks that the 'parent' is the child.
And the 'referenced' table is the parent.
Why Oh Why is 'it' called the parent ?
This piece of code is quickly assembled.
It uses sys tables and information_schema views. :crying:
(They should not be mixed ?)
It is not fully tested.
Improvements ?
Remarks ?
Ben
---------------------------------------------------------------------------------
-- Get all relational constraints. --
---------------------------------------------------------------------------------
-- exec sp_drop ##C -- In my environment this drops the table when it exists
select
OBJECT_NAME(constraint_object_id) constraint_naam
, constraint_column_id
, CONVERT(varchar(60),'') as constraint_column_id_naam
, OBJECT_NAME(parent_object_id) Child_table
, parent_column_id
, CONVERT(varchar(60),'') as Child_column
, OBJECT_NAME(referenced_object_id) Parent_table
, referenced_column_id
, CONVERT(varchar(60),'') as Parent_column
into ##C
from sys.foreign_key_columns
-- I have choosen not to resolve the column_name in line with a join,
-- but use two distinct steps for this. This to clearify the steps.
-- (Two outer joins would have resolved the column names as well).
--
-- Resolve the numbered 'column' in a named column. (Child)
--
update ##C
set Child_column = column_name from ##C join
information_schema.COLUMNS on Child_table = TABLE_NAME and ORDINAL_POSITION = parent_column_id
--
-- Resolve the numbered 'column' in a named column. (Parent)
--
update ##C
set Parent_column = column_name from ##C join
information_schema.COLUMNS on Parent_table = TAble_NAME and ORDINAL_POSITION = referenced_column_id
select * from ##C
-- exec sp_drop ##C
---------------------------------------------------------------------------------
September 1, 2015 at 10:20 am
select
OBJECT_NAME(constraint_object_id) constraint_naam
, constraint_column_id as constraint_column_sequence
, OBJECT_NAME(parent_object_id) Child_table
, parent_column_id
, COL_NAME(parent_object_id, parent_column_id) as child_column
, OBJECT_NAME(referenced_object_id) Parent_table
, referenced_column_id
, COL_NAME(parent_object_id, parent_column_id) as Parent_column
into ##C
from sys.foreign_key_columns
--order by constraint_naam, constraint_column_sequence
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 3, 2015 at 1:50 am
Thanks Scott,
More elegant, more compact and it did uncover a mistake in my solution.
(My parent_column was not correct, I had not noticed that yet.).
Thanks,
Ben
Edit:
Second check,
, COL_NAME(parent_object_id, parent_column_id) as Parent_column
Does appear twice in your solution, is this correct ?
I think the second occurence of this line should read:
, COL_NAME(referenced_object_id, referenced_column_id) as Parent_column
Still thanks,
Ben
September 3, 2015 at 9:27 am
ben.brugman (9/3/2015)
Thanks Scott,More elegant, more compact and it did uncover a mistake in my solution.
(My parent_column was not correct, I had not noticed that yet.).
Thanks,
Ben
Edit:
Second check,
, COL_NAME(parent_object_id, parent_column_id) as Parent_column
Does appear twice in your solution, is this correct ?
I think the second occurence of this line should read:
, COL_NAME(referenced_object_id, referenced_column_id) as Parent_column
Still thanks,
Ben
Sorry, quite right, a copy/paste where I didn't finish editing afterward:blush:
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply