• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

SQL Server: need a list of referencing tables

Hi, I need a list of tables which foreign keys reference primary key of the current table.
Thanks.
0
quasar_ee
Asked:
quasar_ee
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Copy-paste the below T-SQL into your SSMS, execute it to verify it works, then delete the WHERE clause
CREATE TABLE boo (foo varchar(100) DEFAULT 'banana')

SELECT t.name, c.name, dc.definition
FROM sys.tables t
	JOIN sys.columns c ON t.object_id = c.object_id
	JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
WHERE c.name = 'foo'

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Disregard the above, posted in the wrong question.
0
 
Scott PletcherSenior DBACommented:
You can get that by querying sys.objects and sys.foreign_key_columns.
0
 
ZberteocCommented:
One quick way is to execute the following in the database where your table is:

USE YourDatabase
GO
EXEC sp_help table_name

Open in new window


And you will get info in multiple result sets about the table. The last 2 results sets will be about FKs from your table to others and FKs from other tables referencing your table.

If you only want the constraints you can run:

USE YourDatabase
GO
EXEC sys.sp_helpconstraint table_name

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now