getting foreign keys and data types

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

getting foreign keys and data types

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-03-02 : 22:39:48

Hi friends
i have following scripts that returns all Fkeys
SELECT
CCU.TABLE_NAME AS 'FK_ORIGIN_TABLE',
CCU.COLUMN_NAME AS 'FK_ORIGIN_TABLE_COL',
CCU2.TABLE_NAME AS 'FK_OWNING_TABLE',
RC.CONSTRAINT_NAME AS 'FK_NAME',
CCU2.COLUMN_NAME AS 'FK_COLUMN'
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON CCU.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2
ON CCU2.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

but i want to get data types of columns also,how can i change abv query please

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-03-02 : 22:44:18

i tried to join INFORMATION_SCHEMA.columns view but getting duplicate records !!

am using sql server 2005
Cheers

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-03 : 01:00:42

See this..
SELECT
CCU.TABLE_NAME AS 'FK_ORIGIN_TABLE',
CCU.COLUMN_NAME AS 'FK_ORIGIN_TABLE_COL',
CCU2.TABLE_NAME AS 'FK_OWNING_TABLE',
RC.CONSTRAINT_NAME AS 'FK_NAME',
CCU2.COLUMN_NAME AS 'FK_COLUMN',
COL.DATA_TYPE AS 'FK_DATATYPE'
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON CCU.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2
ON CCU2.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.COLUMNS COL
ON
CCU2.TABLE_NAME = COL.TABLE_NAME
AND CCU2.COLUMN_NAME = COL.COLUMN_NAME

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-03-03 : 15:06:25

Thank you very much Gupta. Thats working beautifully.
i tried join only on column name thats why i got duplicate records.
Thanks again.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources