判断表中的数据是否被其他表中使用过!!
2024-07-21 02:11:38
供稿：网友
create procedure dbo.systemfuntion_hasbeused
@ptablename varchar(100), --要检查数据是否被使用的主键表名称
@pkvalue int, --主键字段的值
@isused int output
as
declare @tablename varchar(100) --外键表的名称
declare @fkname varchar(100) --外键字段名称
declare @fkey int
declare @sql varchar(8000)
set @sql = 'select 0 as colcount into #temptablecol union '
declare cursorfktable cursor for
select b.[name] as tablename , a.fkey
from sysforeignkeys a inner join sysobjects b on a.fkeyid=b.[id] inner join sysobjects c on a.rkeyid=c.[id]
where c.[name][email protected]
open cursorfktable
fetch next from cursorfktable into @tablename,@fkey
while @@fetch_status = 0
begin
select @fkname = a.[name] from syscolumns a inner join sysobjects b on a.[id]=b.[id] where b.[name][email protected] and @fkey=a.colid
set @sql = @sql + 'select case when count(*)=0 then 0 else 1 end from ' + @tablename +' where ' + @fkname + '=' + cast(@pkvalue as varchar(100))
set @sql = @sql + ' union '
fetch next from cursorfktable into @tablename,@fkey
end
close cursorfktable
deallocate cursorfktable
select @sql = left(@sql,len(@sql)-6)
exec(@sql)
select @isused = @@rowcount-1
go
使用方法：
/***********获取当前主键值是否被其他表使用过(@haspkvaluebeused as haspkvaluebeused)*************/
declare @haspkvaluebeused int
execute systemfuntion_hasbeused 'tlsubjectplan',@subjectplanid,@[email protected] output
/*******************************************************************************/
