SP that outputs queried tables Foreign Keys? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

SP that outputs queried tables Foreign Keys?

Author

Topic

droog
Starting Member

12 Posts

Posted - 2007-03-27 : 20:43:01

I am pulling information about the contents of a table and want to know if and what Foreign Keys reside in it.

Is there a System Stored Procedure that outputs this? I've tried sp_fkeys but it outputs the FK's of the PK of the queried table. I need to know the FK's which reside in the queried table.

I've tried, sp_columns, sp_foreignkeys and neither give me the output i am looking for.

If there isn't a procedure already available, how could i find this out?

Thanks!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-27 : 22:16:58

What do you mean by Fkeys residing in the table? Do you want to know the tables that the table in question is Foreign Keyed to or do you want to know what all tables have Fkeys to this table in question? If you do sp_help @table you can see the tables that have FK relation back to @table.

************************
Life is short. Enjoy it.
************************

droog
Starting Member

12 Posts

Posted - 2007-03-28 : 00:28:13

I'm trying to find out which columns of the table i'm querying are foreign keys.

For Example

TransactionLog
transID(PK), transDate, transAmount, transResult, custID(FK), orderID(FK)

I want to know that custID and orderID are FK's so i can exclude them from a sql script i am dynamically generating.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-28 : 00:29:42

have a look at the site in my sig, it will give you this information.

www.elsasoft.org

droog
Starting Member

12 Posts

Posted - 2007-03-28 : 00:40:02

Um will i find the answer in scriptDB or sqlSpec for $149? I am really not interested in spending money for something as simple as this. I'll just write the extra code to store the vars of sp_fkeys for later use if there isn't a simple solution.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-28 : 01:51:38

sqlspec is the one I was thinking of. It has a free trial version that's fully featured, so you don't have to pay anything.

else you can have a look at this:


select 
	quotename(su2.name) + N'.' + quotename(so2.name) as parent
	,quotename(su1.name) + N'.' + quotename(so1.name) as name
	,sc2.name as colName
	,'foreign key from ' + quotename(su2.name) + N'.' + quotename(so2.name) + ' to ' + quotename(su3.name) + N'.' + quotename(so3.name) as description
	,quotename(su3.name) + N'.' + quotename(so3.name) as refTable
	,sc3.name as refColumn
	,ObjectProperty(sf.constid, 'CnstIsDisabled') as isDisabled
	,ObjectProperty(sf.constid, 'CnstIsNotRepl') as isNotForReplication
	,ObjectProperty(sf.constid, 'CnstIsDeleteCascade') as isDeleteCascade
	,ObjectProperty(sf.constid, 'CnstIsUpdateCascade') as isUpdateCascade
from sysforeignkeys sf
join sys.objects so1 on sf.constid=so1.object_id
join sys.objects so2 on sf.fkeyid=so2.object_id
join sys.objects so3 on sf.rkeyid=so3.object_id
join sys.schemas su1 on su1.schema_id=so1.schema_id
join sys.schemas su2 on su2.schema_id=so2.schema_id
join sys.schemas su3 on su3.schema_id=so3.schema_id
join syscolumns sc2 on sc2.colid=sf.fkey and sc2.id=so2.object_id
join syscolumns sc3 on sc3.colid=sf.rkey and sc3.id=so3.object_id

EDIT: Just tack on a where clause on the end to limit the results to just the table you care about. as is this query will give you all the fks in the db.

www.elsasoft.org

droog
Starting Member

12 Posts

Posted - 2007-03-28 : 19:27:12

Thanks for the post guys. I came across a solution prior to the one being posted above. This seems a little more straight forward for me i guess. Here it is:

SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
ORDER BY
1,2,3,4

If you want to limit it to specific tables, you can add any of the following immediately prior to the optional ORDER BY clause:

WHERE PK.TABLE_NAME='something'

WHERE FK.TABLE_NAME='something'

WHERE PK.TABLE_NAME IN ('one_thing', 'another')

WHERE FK.TABLE_NAME IN ('one_thing', 'another')

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-28 : 19:31:59

cool.

did you know your name means "friend" in russian?

www.elsasoft.org

droog
Starting Member

12 Posts

Posted - 2007-03-28 : 19:41:19

I had no idea. Something cool to know =D Your smart!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources