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

Did you like this post?

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

Feedback: