以一种能够生成有序表列的格式获取一个关系列表
代码
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
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列。
- 采用逗号分隔符表示复合键。