判断表中的数据是否被其他表中使用过

时间： 2021-08-13 作者：daque

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]=@ptablename 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]=@tablename 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,@isused=@haspkvaluebeused output /*******************************************************************************/

判断表中的数据是否被其他表中使用过

相关推荐

推荐下载

热门阅览

最新排行