27,580
社区成员
发帖
与我相关
我的任务
分享
/*
测试:
Exec sp_search 'F9E42E1E-01E2-4831-B34E-D55E0A405855'
思路:
先查询出所有表所有GUID类型的字段,然后统计字段值等于@Val的个数,如果大于0,保存结果
如果数据量大的话,有点慢
如果你的值存的可能还有其他类型,那你要system_Type_ID = 36 改一下这句,这样更慢更慢更慢
*/
CREATE Procedure sp_Search(@Val UNIQUEIDENTIFIER)
As
Begin
Declare @TBName Varchar(200)
Declare @ColName Varchar(200)
Declare @Rst int
Declare @Sql NVarchar(4000)
Declare @TB Table(TbName Varchar(200),ColName Varchar(200),Cnt INT)
Declare myCur Cursor For
Select A.Name,B.Name From sys.objects A Inner join sys.columns B on a.object_id=b.object_id
Where A.Type='U' And system_Type_ID = 36
Open myCur;
Fetch Next From myCur Into @TBName,@ColName
While @@FETCH_STATUS = 0
BEGIN
SET @Rst = 0
Set @Sql='Select @Rst = count(1) From ['+@TBName+'] Where ['+@ColName+'] = @Val)'
Exec sp_Executesql @sql,N'@Val UNIQUEIDENTIFIER,@Rst int output',@Val,@Rst output
If(@Rst>=1)
Insert Into @TB values(@TBName,@ColName,@Rst)
Fetch Next From myCur Into @TBName,@ColName
End
Close myCur
DeAllocate myCur
Select * From @TB
End