两个关于外键的存储过程

/*
获取指定表的所有关联外键表名和关联字段名
*/
create procedure [dbo].[GetTableNameOfForeignkey]
(
@PKTableName varchar(50) /*输入主键表名称*/
)
 as
BEGIN
   DECLARE @tmptableid int---取主键表的object_id
   select @tmptableid=object_id from sys.tables where name=@PKTableName
   --根据主键表的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 rkeyid=@tmptableid
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 name=@PKTableName
--根据主键表的object_id从系统视图sys.sysforeignkeys中取相关外键表名列表极其外键字段名称
      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 rkeyid=@tmptableid  

    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 '+@tmpFKtablename+' where '+@PKTableName+'='+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

作者：xuexiaodong2009 发表于2011-7-11 13:40:41 原文链接

阅读：50 评论：0 查看评论

转载于:https://www.cnblogs.com/guoyiqi/archive/2011/07/11/2136118.html