Prev: who to contact for content issues?
Next: How do I creat a auto increment for my key field
From: omnistead on 18 Dec 2009 14:17
OK, I've been wrestling with this for a while; time to ask for help.

I need a query (SQL 2005/2008) that, given a table name, will return the
column information for that table as shown in SSMS (schema name, table name,
column name, if it's a primary or foreign key; AND, additionally, if it is a
foreign key - the referenced table and column that contains the primary
key).

I have been able to put together a query to get primary keys and one to get
the foreign keys, but unable to pull it all together with the inclusion of
the reference information for foreign keys! Any help?

Thanks!

From: Tom Cooper on 18 Dec 2009 15:43
Select s.name As SchemaName,
t.name As TableName,
c.name As ColumnName,
Case When ic.index_id Is Null Then 'No' Else 'Yes' End As InPrimaryKey,
Object_Name(c2.object_id) As ReferencedTable,
c2.name As ReferencedColumn
From sys.tables t
Inner Join sys.schemas s On t.schema_id = s.schema_id
Inner Join sys.columns c On t.object_id = c.object_id
Left Join sys.indexes i On t.object_id = i.object_id And i.is_primary_key =
1
Left Join sys.index_columns ic On t.object_id = ic.object_id And c.column_id
= ic.column_id And i.index_id = ic.index_id
Left Join sys.foreign_key_columns fkc On t.object_id = fkc.parent_object_id
And c.column_id = fkc.parent_column_id
Left Join sys.columns c2 On fkc.referenced_object_id = c2.object_id And
fkc.referenced_column_id = c2.column_id
Where t.name = 'Product'
Order By s.name, c.column_id;

Tom

"omnistead" <mhunt5(a)hotmail.com> wrote in message
news:eWGpVbBgKHA.3552(a)TK2MSFTNGP06.phx.gbl...
> OK, I've been wrestling with this for a while; time to ask for help.
>
> I need a query (SQL 2005/2008) that, given a table name, will return the
> column information for that table as shown in SSMS (schema name, table
> name, column name, if it's a primary or foreign key; AND, additionally, if
> it is a foreign key - the referenced table and column that contains the
> primary key).
>
> I have been able to put together a query to get primary keys and one to
> get the foreign keys, but unable to pull it all together with the
> inclusion of the reference information for foreign keys! Any help?
>
> Thanks!

From: Plamen Ratchev on 18 Dec 2009 17:34
Here is a query that is probably a bit too complex but uses the information schema views which supposedly will work
across different versions (you just need to add a predicate for table name in the WHERE clause as currently it pulls all
tables in the current database):

SELECT C.TABLE_CATALOG,
C.TABLE_SCHEMA,
C.TABLE_NAME,
C.COLUMN_NAME,
X.CONSTRAINT_NAME AS FK,
X.UNIQUE_CONSTRAINT_NAME AS PK,
P.TABLE_NAME AS PK_TABLE,
P.COLUMN_NAME AS PK_COLUMN,
K.CONSTRAINT_NAME AS PK_COLUMN_CONSTRAINT
FROM INFORMATION_SCHEMA.COLUMNS AS C
JOIN INFORMATION_SCHEMA.TABLES AS T
ON C.TABLE_CATALOG = T.TABLE_CATALOG
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN (SELECT U.TABLE_CATALOG,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME,
U.ORDINAL_POSITION,
R.CONSTRAINT_CATALOG,
R.CONSTRAINT_SCHEMA,
R.CONSTRAINT_NAME,
R.UNIQUE_CONSTRAINT_CATALOG,
R.UNIQUE_CONSTRAINT_SCHEMA,
R.UNIQUE_CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS U
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS R
ON U.CONSTRAINT_CATALOG = R.CONSTRAINT_CATALOG
AND U.CONSTRAINT_SCHEMA = R.CONSTRAINT_SCHEMA
AND U.CONSTRAINT_NAME = R.CONSTRAINT_NAME) AS X
ON C.TABLE_CATALOG = X.TABLE_CATALOG
AND C.TABLE_SCHEMA = X.TABLE_SCHEMA
AND C.TABLE_NAME = X.TABLE_NAME
AND C.COLUMN_NAME = X.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS P
ON X.UNIQUE_CONSTRAINT_CATALOG = P.CONSTRAINT_CATALOG
AND X.UNIQUE_CONSTRAINT_SCHEMA = P.CONSTRAINT_SCHEMA
AND X.UNIQUE_CONSTRAINT_NAME = P.CONSTRAINT_NAME
AND X.ORDINAL_POSITION = P.ORDINAL_POSITION
LEFT JOIN (SELECT A.CONSTRAINT_CATALOG,
A.CONSTRAINT_SCHEMA,
A.CONSTRAINT_NAME,
A.TABLE_NAME,
A.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS A
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS B
ON A.CONSTRAINT_CATALOG = B.CONSTRAINT_CATALOG
AND A.CONSTRAINT_SCHEMA = B.CONSTRAINT_SCHEMA
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'PRIMARY KEY') AS K
ON C.TABLE_CATALOG = K.CONSTRAINT_CATALOG
AND C.TABLE_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.TABLE_NAME = K.TABLE_NAME
AND C.COLUMN_NAME = K.COLUMN_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE';

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: who to contact for content issues?
Next: How do I creat a auto increment for my key field