Need Script to List Foreign Keys for a Table - Error TRUNCATING Table

Question

Post reply

Need Script to List Foreign Keys for a Table - Error TRUNCATING Table

Welsh Corgi

SSC Guru

Points: 116520
More actions
July 20, 2012 at 12:03 pm

#264121

I'm looking for a script that List all Foreign Keys for a Table.
I had a few but I can't locate them or they are not giving me what I need.
I need to truncate several tables but I can't find the Foreign Keys in this case one table in particular that is giving me an error when TRUNCATING the Table.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

~~select *, OBJECT_NAME(fkeyid)~~

~~from sys.sysforeignkeys~~

~~where OBJECT_NAME(rkeyid) = 'YourMainTableHere'~~

DOH! Still using the deprecated version above....disregard and instead use this:

select *, OBJECT_NAME(parent_object_id)

from sys.foreign_key_columns

where OBJECT_NAME(referenced_object_id) = 'YourMainTableHere'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Sean,

That is a very straight forward script.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

You're welcome. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

Another tip:

Select your table in MSMS and then press Ctrl+F1

Check the result set with FK'S 😉

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

lokeshvij (7/22/2012)
Another tip:
Select your table in MSMS and then press Ctrl+F1
Check the result set with FK'S 😉

That is a super easy way to find foreign keys defined on the current table but doesn't help find foreign keys defined on other tables that reference this one. That is what he OP was trying to find. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deepzzzz SSCrazy Points: 2890 More actions · Answer 6

SELECT f.name AS ForeignKey,

SCHEMA_NAME(f.SCHEMA_ID) SchemaName,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

WHERE OBJECT_NAME(f.parent_object_id)= 'Table Name'

GO

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

deepzzzz (7/23/2012)
SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME(f.parent_object_id)= 'Table Name'
GO

This is another good script for finding all the foreign keys on TableB, it does not help the OP because they are looking for all foreign keys that are setup on any table that references TableA.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 8

Sean Lange (7/22/2012)
lokeshvij (7/22/2012)
Another tip:
Select your table in MSMS and then press Ctrl+F1
Check the result set with FK'S 😉
That is a super easy way to find foreign keys defined on the current table but doesn't help find foreign keys defined on other tables that reference this one. That is what he OP was trying to find. 🙂

I think it does both

Correct me if I am wrong 🙂

Edit: it should Alt+F1 (not Ctrl+F1)

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Daxesh Patel (7/23/2012)
Sean Lange (7/22/2012)
lokeshvij (7/22/2012)
Another tip:
Select your table in MSMS and then press Ctrl+F1
Check the result set with FK'S 😉
That is a super easy way to find foreign keys defined on the current table but doesn't help find foreign keys defined on other tables that reference this one. That is what he OP was trying to find. 🙂
I think it does both
Correct me if I am wrong 🙂
Edit: it should Alt+F1 (not Ctrl+F1)

Yes you are correct. 😛 The last table in the resultset is table(s) that are referenced.

In case anybody wants to know what that key is doing, it is nothing more than a keyboard shortcut to executing sp_help

http://msdn.microsoft.com/en-us/library/ms187335.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

deepzzzz SSCrazy Points: 2890 More actions · Answer 10

If you want for all tables, just remove the last 'where' clause in the above query. It will give for all the tables in the database.

Thanks