January 7, 2009 at 7:40 pm
For now we'll just consider one table, not the whole db. Say I have a table with the following information.
intCompanyID IDENTITY -- PRIMARY KEY
vcAddress
vcCity
intStateID -- FOREIGN KEY TO STATES TABLE
What I want is a listing the resembles the following:
COLUMN NAME | DATATYPE | PRIMARY KEY | FOREIGN KEY
--------------------------------------------------------------------------
intCompanyID int YES NO
vcAddress varchar NO NO
vcCity varchar NO NO
intStateID int NO YES
I've been trying to use sys.columns, sys.tables and all the other sys's I can think of. I've also been trying to use INFORMATION_SCHEMA to gather all of the information. I can get the key information but I can't seem to link it to a column listing of the tables. Any help would be appreciated. If I'm missing something obvious you can call me an idiot. I don't particularly care if the PK and FK are in different columns, just a column that says FK or PK would be fine.
January 7, 2009 at 11:29 pm
sys.tables, sys.columns, sys.foreign_keys, sys.foreign_key_columns, sys.indexes where is_primary_key=1, sys.index_columns
It's a nasty, very large query, but those are the tables needed. If you get stuck, post the query that you have.
One other thing. Be careful of multi-column keys
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2009 at 12:46 am
This should help:
SELECT c.TABLE_SCHEMA
, c.TABLE_NAME
, c.COLUMN_NAME
, c.DATA_TYPE
, CASE When pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
Then 'YES' Else 'NO' End as [Primary Key]
, Case When Exists(Select *
From INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
Where fk.CONSTRAINT_TYPE = 'FOREIGN KEY'
And k.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA
And k.CONSTRAINT_NAME = fk.CONSTRAINT_NAME)
Then 'YES' Else 'NO' End [Foreign Key]
From INFORMATION_SCHEMA.COLUMNS c
Left Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON k.TABLE_SCHEMA = c.TABLE_SCHEMA
And k.TABLE_NAME = c.TABLE_NAME
And k.COLUMN_NAME = c.COLUMN_NAME
Left Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
ON k.CONSTRAINT_SCHEMA = pk.CONSTRAINT_SCHEMA
And k.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
Order By c.TABLE_SCHEMA
, c.TABLE_NAME
, c.COLUMN_NAME, c.ORDINAL_POSITION
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 1:05 am
WITH CTE_Keys -- ID just the PKs and FKs from info_schema.key col usage
AS
(
SELECT
k.CONSTRAINT_NAME
,k.TABLE_SCHEMA
,k.TABLE_NAME
,k.COLUMN_NAME
,o.[type]
,o.type_desc
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
INNER JOIN sys.objects o
ON OBJECT_ID(k.CONSTRAINT_NAME) = o.[object_id]
AND o.[type] IN ( 'f','pk' )
)
SELECT
c.TABLE_SCHEMA
,c.TABLE_NAME
,c.COLUMN_NAME
,c.DATA_TYPE
,c.ORDINAL_POSITION
,CASE WHEN pk.CONSTRAINT_NAME IS NULL THEN 'NO' ELSE 'YES' END AS PRIMARY_KEY
,CASE WHEN fk.CONSTRAINT_NAME IS NULL THEN 'NO' ELSE 'YES' END AS FOREIGN_KEY
,pk.CONSTRAINT_NAME AS PK_CONSTRAINT_NAME
,fk.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN CTE_Keys pk
ON pk.[type] = 'pk'
AND pk.TABLE_SCHEMA = c.TABLE_SCHEMA
AND pk.TABLE_NAME = c.TABLE_NAME
AND pk.COLUMN_NAME = c.COLUMN_NAME
LEFT OUTER JOIN CTE_Keys fk
ON fk.[type] = 'f'
AND fk.TABLE_SCHEMA = c.TABLE_SCHEMA
AND fk.TABLE_NAME = c.TABLE_NAME
AND fk.COLUMN_NAME = c.COLUMN_NAME
ORDER BY
c.TABLE_NAME
,c.ORDINAL_POSITION
January 8, 2009 at 1:13 am
Heh, good point. Likewise, Since there can be more than one foreign key on a table, you could get multiplication from your last join (that's why I put it in the EXISTS subquery).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 6:15 am
Thank you all very much. I've never used a forum for this type of thing and you guys just made my day!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply