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



Did you like this post?
kick it on DotNetKicks.com
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:

comments

1 Response to "A Simple Stored Procedure to Find Foreign Key Dependencies on Your Table in SQL Server 2005/2008"
  1. Anonymous said...
    April 6, 2009 at 6:00 AM

    sp_fkeys 'Employees'

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions