A Simple Stored Procedure to Find Foreign Key Dependencies on Your Table in SQL Server 2005/2008

Tweet

One of the criterias while dropping a table is that the ForeignKey Dependencies on the table should be dropped first. But how to you programmatically find out the Foreign Key Dependecies on your table using T-SQL. Here's a simple stored procedure that shows the dependencies of the table passed to it

USE Northwind

GO

CREATE PROCEDURE [dbo].[usersp_FindTableDependency]

@tblName varchar(50)

AS

SELECT fk.name, OBJECT_NAME(fk.parent_object_id) as DepTable

FROM sys.foreign_keys fk

inner join sys.tables st on st.[object_id] = fk.referenced_object_id

WHERE st.name = @tblName

In Order to test this procedure:

 

EXEC usersp_FindTableDependency 'Employees'

Produces the result

Name                                DepTable

FK_Orders_Employees                    Orders

FK_EmployeeTerritories_Employees    EmployeeTerritories

FK_Employees_Employees                Employees

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

Feedback: