两个关于外键的存储过程详解数据库

/*
获取指定表的所有关联外键表名和关联字段名
*/
create procedure [dbo].[GetTableNameOfForeignkey]
(
@PKTableName varchar(50) /*输入主键表名称*/
)
 as
BEGIN
   DECLARE @tmptableid int—取主键表的object_id
   select @tmptableid=object_id from sys.tables where [email protected]
   –根据主键表的object_id从系统视图sys.sysforeignkeys中取相关外键表名列表极其外键字段名称
   select b.name as FKtablename ,c.name as FKColumnName
   from sys.sysforeignkeys a,sys.tables b,sys.columns c
   where a.fkeyid=b.object_id and a.fkeyid=c.object_id
   and a.fkey=c.column_id and [email protected]
END

/*

判断指定表的某个值是否在外键表中有记录,返回多个表，具体个数不确定

*/

create procedure [dbo].[GetTableNameOfForeignkey]

(

@PKTableName varchar(50),–输入主键表名称

    @FKVulae int –某个值

)

as

BEGIN

DECLARE @tmptableid int,@tmpFKtablename varchar(50),@tmpFKCoLName varchar(50),@tmpSql varchar(1000)–定义变量

      declare @temp table(tablename varchar(50),ColumnName  varchar(50))–声明表

–取主键表的object_id

select  @tmptableid=object_id from sys.tables where [email protected]

[email protected]取相关外键表名列表极其外键字段名称


      insert into @temp     select b.name,c.name
from sys.sysforeignkeys a,sys.tables b,sys.columns c

where a.fkeyid=b.object_id and a.fkeyid=c.object_id and a.fkey=c.column_id and [email protected]  

   
DECLARE tnames_cursor CURSOR  LOCAL FORWARD_ONLY READ_ONLY –声明游标

   FOR select * from @temp

            open tnames_cursor

           FETCH NEXT FROM tnames_cursor INTO @tmpFKtablename,@PKTableName

   WHILE (@@FETCH_STATUS = 0)

         BEGIN

           set @tmpSql=’select count(*) as conutNO from [email protected]+’ where [email protected]+’=’+cast(@FKVulae as varchar(10))

          exec(@tmpSql)–输出

     —  print @tmpSql

      FETCH NEXT FROM tnames_cursor INTO @tmpFKtablename,@PKTableName

     END

    CLOSE tnames_cursor–关闭游标

    DEALLOCATE tnames_cursor

END

GO