取得所有外键内容的脚本

近来有个任务要找出SQL Server 2005某个数据库里所有的外键，包括外键表/列名、引用表/列名。随手写了个脚本，放在这里备忘。

-- This script scans your database and stores all FK information,
-- including source table/column and reference table/column. 
-- Then it will store this information into table fk, 
-- which is created at the beginning.


--- USE [YourDatabaseNameHere];

--- GO
IF NOT EXISTS (SELECT name
               FROM   sys.tables
               WHERE  name = N'fk')
  BEGIN
    CREATE TABLE [dbo].[fk] (
      [id]      [INT]   IDENTITY ( 1 , 1 )   NOT NULL,
      [stable]  [NVARCHAR](255)   NULL,
      [scolumn] [NVARCHAR](255)   NULL,
      [rtable]  [NVARCHAR](255)   NULL,
      [rcolumn] [NVARCHAR](255)   NULL,
      CONSTRAINT [fk_pk] PRIMARY KEY CLUSTERED ( [id] ASC ) WITH ( pad_index = OFF,ignore_dup_key = OFF ) ON [primary])
    ON [primary]
  END
ELSE
  BEGIN
    TRUNCATE TABLE fk
  END

GO

--- start processing FKs and store in table fk
DECLARE  @tableName NVARCHAR(255);

DECLARE tables CURSOR  FOR
SELECT   [name]
FROM     sys.tables
ORDER BY [name]

OPEN tables;

FETCH NEXT FROM tables
INTO @tableName

WHILE @@FETCH_STATUS = 0
  BEGIN
    INSERT INTO fk
               (stable,
                scolumn,
                rtable,
                rcolumn)
    SELECT @tableName    AS stable,
           t1.name       AS scolumn,
           t2.rtablename AS rtable,
           t2.name       AS rcolumn
    FROM   (SELECT col.name,
                   f.constid AS t
            FROM   syscolumns col,
                   sysforeignkeys f
            WHERE  f.fkeyid = col.id
                   AND f.fkey = col.colid
                   AND f.constid IN (SELECT DISTINCT (id)
                                     FROM   sysobjects
                                     WHERE  Object_name(parent_obj) = @tableName
                                            AND xtype = 'F')) AS t1,
           (SELECT Object_name(f.rkeyid) AS rtablename,
                   col.name,
                   f.constid             AS t
            FROM   syscolumns col,
                   sysforeignkeys f
            WHERE  f.rkeyid = col.id
                   AND f.rkey = col.colid
                   AND f.constid IN (SELECT DISTINCT (id)
                                     FROM   sysobjects
                                     WHERE  Object_name(parent_obj) = @tableName
                                            AND xtype = 'F')) AS t2
    WHERE  t1.t = t2.t

    FETCH NEXT FROM tables
    INTO @tableName
  END

CLOSE tables

DEALLOCATE tables

GO

SELECT *
FROM   fk