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
sp_fkeys 'Employees'
ReplyDelete