以一种能够生成有序表列的格式获取一个关系列表

 

代码
 1 IF EXISTS(
 2     SELECT * FROM dbo.sysobjects
 3     WHERE id=OBJECT_ID('dbo.uspGetSqlRelationships') AND OBJECTPROPERTY(id,'IsProcedure')=1)
 4     DROP PROCEDURE dbo.uspGetSqlRelationships
 5 GO
 6 
 7 CREATE PROCEDURE dbo.uspGetSqlRelationships
 8 AS
 9     IF OBJECT_ID('tempdb..#temp')IS NOT NULL DROP TABLE #temp
10     SELECT 
11         r_obj.name as relationshipName,
12         parent_obj.name as Parent,
13         child_obj.name as child,
14         CONVERT(nvarchar(1000),'') as pk,
15         CONVERT(nvarchar(1000),'') as fk,
16         r.fkeyid,
17         r.rkeyid,
18         r.constid
19     INTO #temp 
20     FROM dbo.sysobjects r_obj
21         INNER JOIN dbo.sysreferences r ON r_obj.id=r.constid
22         INNER JOIN dbo.sysobjects parent_obj ON r.rkeyid=parent_obj.Id
23         INNER JOIN dbo.sysobjects child_obj ON r_obj.parent_obj=child_obj.Id
24         INNER JOIN dbo.sysforeignkeys fkeys ON fkeys.constid=r.constid
25         INNER JOIN dbo.syscolumns cols ON cols.id=fkeys.fkeyid and cols.colid=fkeys.fkey
26     WHERE r_obj.xtype='F' AND (r_obj.status & 0x100)=0
27 
28     DECLARE @PK VARCHAR(1000), @FK VARCHAR(1000)
29     DECLARE @fkeyid int,@rkeyid int, @cnstid INT
30     DECLARE @keys NVARCHAR(2126)
31     DECLARE @cnstname SYSNAME
32     DECLARE @cnstdes nvarchar(4000)
33     DECLARE @cnsttype CHARACTER(2)
34     DECLARE @relName VARCHAR(1000)
35     
36     DECLARE tempTable CURSOR LOCAL FOR
37         SELECT relationshipName,pk,fk,fkeyid,rkeyid,constid FROM #temp
38     open tempTable
39     FETCH tempTable into @relName,@pk,@fk,@fkeyid,@rkeyid,@cnstid
40     
41     WHILE @@FETCH_STATUS>=0
42     BEGIN
43         DECLARE ms_crs_fkey cursor local FOR
44             SELECT fkey,rkey from sysforeignkeys WHERE constid=@cnstid
45         OPEN ms_crs_fkey
46         DECLARE @FKEYCOL SMALLINT,@RKEYCOL SMALLINT
47         FETCH ms_crs_fkey into @fkeycol,@rkeycol
48         
49         SELECT @keys=col_name(@fkeyid,@fkeycol)
50         SELECT @cnstdes=col_name(@rkeyid,@rkeycol)
51         
52         FETCH ms_crs_fkey into @fkeycol,@rkeycol
53         WHILE @@FETCH_STATUS>=0
54         BEGIN
55             SELECT @keys=@keys+','+col_name(@fkeyid,@fkeycol)
56             SELECT @cnstdes=@cnstdes+','+col_name(@rkeyid,@rkeycol)
57             FETCH ms_crs_fkey into @fkeycol,@rkeycol
58         END
59         DEALLOCATE ms_crs_fkey
60         UPDATE #temp
61         SET pk=@cnstdes,fk=@keys
62         WHERE relationshipName=@relName
63         FETCH tempTable into @relName,@pk,@fk,@fkeyid,@rkeyid,@cnstid 
64     END
65     DEALLOCATE tempTable
66     SELECT DISTINCT relationshipName, Parent, child,pk,fk from #temp

 

 

  • 该存储过程查询sysreferences表，以获得对象间引用的完整列表。sysreferences表包含多个id列，可以使用与sysobjects表的联接将这些列转换为对象名。第26行表示所获取的行集为强制性的外键引用。
  • 第14、15行为pk和fk列占位。
  • 接下来使用指向#temp的游标，查找主键列和外键列，然后将查询信息存入pk和fk列。
  • 采用逗号分隔符表示复合键。