April 7, 2010 at 5:37 am
Hello Everyone
I am trying to write a query that returns the table name(s), column name(s) that are related to the Primary Key column of a table.
Not sure that I worded that so that it makes good sense.
How can I determine what other tables and columns in other tables are related by a constraint to the primary key column of a table?
I have done this with the foreign keys, but not sure where to get the data for the primary keys.
Does anyone have a link to the Microsoft page where you can order the system table diagram?
Thanks in advance
Andrew SQLDBA
April 7, 2010 at 5:42 am
If you are looking for sys.primary_keys ... it's not there - primary keys are listed together with unique keys under sys.key_constraints
Relationship column details are found in sys.foreign_key_columns
See also: Object Catalog Views
April 7, 2010 at 5:47 am
SELECT p.TABLE_NAME,c.CONSTRAINT_NAME,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = p.TABLE_NAME
AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
ORDER by c.TABLE_NAME
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 7, 2010 at 6:14 am
...and for fun value:
SELECT [schema_name] = QUOTENAME(SCHEMA_NAME(KC.[schema_id])),
table_name = QUOTENAME(OBJECT_NAME(KC.parent_object_id)),
constraint_name = QUOTENAME(KC.name),
key_columns =
STUFF(
(
SELECT N', ' + QUOTENAME(C.name)
FROM sys.index_columns IC
JOIN sys.columns C
ON C.[object_id] = IC.[object_id]
AND C.column_id = IC.column_id
WHERE IC.[object_id] = KC.[parent_object_id]
AND IC.index_id = KC.unique_index_id
ORDER BY
IC.key_ordinal
FOR XML PATH (''), TYPE
).value('./text()[1]', 'VARCHAR(8000)')
, 1, 2, SPACE(0))
FROM sys.key_constraints KC
WHERE KC.type_desc = N'PRIMARY_KEY_CONSTRAINT'
AND KC.is_ms_shipped = 0
ORDER BY
[schema_name],
table_name;
April 7, 2010 at 6:15 am
That one is close, but I would like to know, what the other table name and column name for each primary key listed in the query.
Also, how do you make this site show the other posts that are related to this post? I can only see what I am writing currently. I cannot see what you two guys have written.
I am coming close, but still cannot get it, the system tables diagram map would be a huge help
Thanks
Andrew SQLDBA
April 7, 2010 at 6:21 am
AndrewSQLDBA (4/7/2010)
That one is close, but I would like to know, what the other table name and column name for each primary key listed in the query.
You just want to list the foreign key relationships then, not just the primary keys? Do you want to include foreign keys that reference a unique key - or just primary keys? Just so we can be sure.
[/quote]Also, how do you make this site show the other posts that are related to this post? I can only see what I am writing currently. I cannot see what you two guys have written.[/quote]
In the reply window, there's a little '+' in a square bottom right of the page, click it and then ten previous posts appear. There is another link after you expand that, right at the bottom, that will open the whole thread in a new tab/window.
I am coming close, but still cannot get it, the system tables diagram map would be a huge help
Sure would - but I have never seen one. I did post a link to object catalog views - but I know that's not great 🙁
Thanks
Andrew SQLDBA[/quote]
April 7, 2010 at 6:26 am
Paul
I was not referring to your query. I have not tried that one yet. You posted that while I was typing my last post.
Let me work with that one for a few mins, and I will let you know.
Microsoft used to allow one to purchase a big poster of the system database diagram. I have one from SQL 2000, but I cannot find oen for 2005 or 2008. I hope they did not stop that. They used to give them away at some of the SQL shows also.
Thanks
Andrew SQLDBA
April 7, 2010 at 6:28 am
AndrewSQLDBA (4/7/2010)
PaulI was not referring to your query. I have not tried that one yet. You posted that while I was typing my last post.
I know - mine is just the SQL version of Bhuvnesh's...with some tweaks for my own amusement.
Microsoft used to allow one to purchase a big poster of the system database diagram. I have one from SQL 2000, but I cannot find oen for 2005 or 2008. I hope they did not stop that. They used to give them away at some of the SQL shows also.
I had one for the DMVs for 2005/8, but not seen a system table one yet...:(
April 7, 2010 at 6:34 am
Paul
That query is great, but I would like to see all the other tables and columns that those are related too.
I have so many tables in this database, that I cannot chase the lines in the diagram.
Thanks
Andrew SQLDBA
April 7, 2010 at 6:44 am
Paul White NZ (4/7/2010)
You just want to list the foreign key relationships then, not just the primary keys? Do you want to include foreign keys that reference a unique key - or just primary keys? Just so we can be sure.
Hey Andrew,
Can you just answer the above question too please? Will save rework 😉
It's nearly 1am here, so I may not get to this until tomorrow now...but there are lots of other people around.
Thanks,
Paul
April 7, 2010 at 6:52 am
Hey Paul
I would like only the Primary Keys table.columns and their related table.column
So one primary key table.column may have 1 or many related table.columns
Thanks
Andrew SQLDBA
April 7, 2010 at 7:14 am
I found this link that you can download the map for SQL 2005, there is also a link for SQL 2008.
That will help me I am sure, now that I know what the table names are.
Andrew SQLDBA
April 7, 2010 at 10:26 pm
SELECT parent_schema = QUOTENAME(PS.name),
parent_table_name = QUOTENAME(PT.name),
child_schema = QUOTENAME(RS.name),
child_table_name = QUOTENAME(RT.name),
foreign_key_schema = QUOTENAME(FS.name),
foreign_key_name = QUOTENAME(FK.name),
FK.is_disabled,
FK.is_not_trusted,
parent_columns =
STUFF(
(
SELECT [text()] = ',' + QUOTENAME(PC.name)
FROM sys.foreign_key_columns FKC
JOIN sys.columns PC
ON PC.[object_id] = FKC.parent_object_id
AND PC.column_id = FKC.parent_column_id
WHERE FKC.constraint_object_id = FK.[object_id]
ORDER BY
FKC.constraint_column_id
FOR XML PATH('')
), 1, 1, SPACE(0)),
child_columns =
STUFF(
(
SELECT [text()] = ',' + QUOTENAME(RC.name)
FROM sys.foreign_key_columns FKC
JOIN sys.columns RC
ON RC.[object_id] = FKC.referenced_object_id
AND RC.column_id = FKC.referenced_column_id
WHERE FKC.constraint_object_id = FK.[object_id]
ORDER BY
FKC.constraint_column_id
FOR XML PATH('')
), 1, 1, SPACE(0))
FROM sys.foreign_keys FK
JOIN sys.schemas FS
ON FS.[schema_id] = FK.[schema_id]
JOIN sys.tables PT
ON PT.[object_id] = FK.parent_object_id
JOIN sys.schemas PS
ON PS.[schema_id] = PT.[schema_id]
JOIN sys.tables RT
ON RT.[object_id] = FK.referenced_object_id
JOIN sys.schemas RS
ON RS.[schema_id] = RT.[schema_id]
WHERE FK.is_ms_shipped = 0
ORDER BY
parent_schema,
parent_table_name,
child_schema,
child_table_name;
April 7, 2010 at 10:27 pm
AndrewSQLDBA (4/7/2010)
I found this link that you can download the map for SQL 2005, there is also a link for SQL 2008.
Thanks Andrew! All I need to do now is find an A0 printer :w00t:
April 8, 2010 at 5:45 am
Me too, I like that poster. You used to be able to order it from MS, and they would send you the poster. Not any longer it seems, I did not see or read anything about that. Maybe you can take it to a Kinko's and they can give you a huge wall sized poster.
Thanks for the query. Let me work with it and see what it returns. I made some headway yesterday afternoon. It is almost 8am here in the US.
Andrew SQLDBA
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply