getting the Foreign key relationships of a column

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

General SQL Server Forums

New to SQL Server Programming

getting the Foreign key relationships of a column

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2009-12-23 : 07:30:26

I want to get all the foreign key relationships of a column. Is there any method for that?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-23 : 07:50:10

If you are on sql server 2005 then there are the various system views.

Try this to start with:


SELECT
	ky.[name] AS [Key Name]
	, tbl.[name] AS [Table holding Key]
	, icP.[column_name] AS [Column Holding Key]
	, target.[name] AS [key Targets Table]
	, icT.[column_name] AS [key Targets Column]
FROM
	sys.foreign_key_columns fkc
	JOIN sys.objects ky ON ky.[object_Id] = fkc.[constraint_object_id]
	JOIN sys.objects tbl ON tbl.[object_id] = fkc.[parent_object_Id]
	JOIN sys.objects target ON target.[object_ID] = fkc.[referenced_object_Id]

	JOIN information_schema.columns icP ON
		icP.[table_name] = tbl.[name]
		AND icP.[ordinal_position] = fkc.[parent_column_Id]

	JOIN information_schema.columns icT ON
		icT.[table_name] = target.[name]
		AND icT.[ordinal_position] = fkc.[referenced_column_Id]
ORDER BY
	ky.[name]
	, icP.[column_name]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-26 : 00:27:29

[code]
try this too
SELECT
TC.Table_Name
, TC.Constraint_Name
, 'ALTER TABLE [' + TC.Table_Name
+ '] DROP CONSTRAINT [' + TC.Constraint_Name + ']' AS DropStmt
, 'ALTER TABLE [' + TC.Table_Name
+ '] ADD CONSTRAINT [' + TC.Constraint_Name + '] PRIMARY KEY'
+ CASE INDEXPROPERTY(OBJECT_ID(TC.Table_Name),TC.Constraint_Name,'IsClustered')
WHEN 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED'END + ' ('
+ STUFF((SELECT ', [' + Column_Name + ']'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE Table_Name = TC.Table_Name AND Constraint_Name = TC.Constraint_Name
ORDER BY Ordinal_Position
FOR XML PATH('')), 1, 1, '') + ')' AS CreateStmt
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
WHERE TC.Constraint_TYPE = 'Foreign KEY'
AND TC.Table_Name <> 'dtproperties' -- this isn't a true user table, it's for database diagramming
ORDER BY TC.Table_Name
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources