March 24, 2009

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

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


CREATE PROCEDURE [dbo].[usersp_FindTableDependency]

@tblName varchar(50)


SELECT, 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 = @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

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

1 comment:

Anonymous said...

sp_fkeys 'Employees'