Finding the Foreign Keys for a table
For a while it's been driving me barmy, trying to obtain a list of Foreign Keys which reference a particular table. Below is the end result of my hunting. No doubt the code could be optimised, which is something I should do. As it uses the INFORMATION_SCHEMA views, it is at least SQL Server version independent and thus should work on both 2000 and 2005. (Not tested on SQL Server 7)
I'm sure there's probably a way to do this in SQLDMO, which will be my next investigation. Watch this space!
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'GetFKsForTable' AND type = 'P') DROP PROCEDURE GetFKsForTable
GO
CREATE PROCEDURE GetFKsForTable
@dbname sysname,
@sTable varchar(255)
AS
DECLARE @sCurrentDB varchar(255);
SELECT @sCurrentDB = SD.name
FROM master..sysprocesses SP
INNER JOIN master..sysdatabases SD
ON SP.dbid = SD.dbid
WHERE SP.spid = @@SPID
IF LOWER(@sCurrentDB) <> LOWER(@dbName)
BEGIN
RAISERROR ('TARGET database is not CURRENT database. Aborted', 18, 1) WITH NOWAIT
RETURN
END
SELECT
CCU.TABLE_NAME AS 'FK_ORIGIN_TABLE',
CCU.COLUMN_NAME AS 'FK_ORIGIN_TABLE_COL',
CCU2.TABLE_NAME AS 'FK_OWNING_TABLE',
RC.CONSTRAINT_NAME AS 'FK_NAME',
CCU2.COLUMN_NAME AS 'FK_COLUMN'
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON CCU.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2
ON CCU2.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE CCU.TABLE_NAME = @sTable
GO