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

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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook