判斷表中的數(shù)據(jù)是否被其他表中使用過(guò)!!
2024-07-21 02:11:38
供稿：網(wǎng)友
create procedure dbo.systemfuntion_hasbeused
@ptablename varchar(100), --要檢查數(shù)據(jù)是否被使用的主鍵表名稱
@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
使用方法：
/***********獲取當(dāng)前主鍵值是否被其他表使用過(guò)(@haspkvaluebeused as haspkvaluebeused)*************/
declare @haspkvaluebeused int
execute systemfuntion_hasbeused 'tlsubjectplan',@subjectplanid,@[email protected] output
/*******************************************************************************/
