使用临时表的示例

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

 

代码

 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列。
• 采用逗号分隔符表示复合键。

 

 

转载于:https://www.cnblogs.com/cuishengli/archive/2010/04/25/1719766.html