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


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, 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'