Binaryworld - Useful Stored Proc to get Foreign Key Information of any DB for any ta ... [ SQL Server -> Stored Procedure ]

Create proc spFKInfo
@TableName varchar(100)='%',
@DBName varchar(100)='Northwind'
as

declare @sql varchar(max)

set @sql='
SELECT
RELATION= FK.TABLE_SCHEMA + ''.'' + FK.TABLE_NAME + ''.'' + CU.COLUMN_NAME + '' -> '' + PK.TABLE_SCHEMA + ''.'' + PK.TABLE_NAME + ''.'' + PT.COLUMN_NAME,
FK_Database= FK.TABLE_CATALOG,
FK_Schema= FK.TABLE_SCHEMA,
  FK_Table = FK.TABLE_NAME,
  FK_Column = CU.COLUMN_NAME,
PK_Database = PK.TABLE_CATALOG,
PK_Schema = PK.TABLE_SCHEMA,
  PK_Table = PK.TABLE_NAME,
  PK_Column = PT.COLUMN_NAME,
  Constraint_Name = C.CONSTRAINT_NAME,
COL.IS_NULLABLE,
COL.DATA_TYPE
FROM
  ' + @DBName + '.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
  INNER JOIN
  ' + @DBName + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
  INNER JOIN
  ' + @DBName + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
  INNER JOIN
  ' + @DBName + '.INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
  INNER JOIN
  (
    SELECT
      i1.TABLE_NAME, i2.COLUMN_NAME
    FROM
      ' + @DBName + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
      INNER JOIN
      ' + @DBName + '.INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
      ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
      WHERE i1.CONSTRAINT_TYPE = ''PRIMARY KEY''
  ) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
INNER JOIN ' + @DBName + '.INFORMATION_SCHEMA.COLUMNS COL
ON CU.COLUMN_NAME=COL.COLUMN_NAME and COL.TABLE_NAME=FK.TABLE_NAME
Where PK.TABLE_NAME like ''%' + @TableName + '%'' or FK.TABLE_NAME like ''%' + @TableName + '%''
ORDER BY
  1,2,3,4
'

print @sql
execute(@sql)

go