搜索数据库表对象中包含特定字符串

Create Proc [dbo].[Upc_SearchTextOnDB](@SearchText nvarchar(100))ASBEGINSET NOCOUNT ONDeclare @LoopID int ,@StrSQL nvarchar(max),@TabName nvarchar(100),@TempStr nvarchar(100),@WhereStr nvarchar(max),@HasRowint set @HasRow = 0IF(OBJECT_ID('Tempdb.dbo.#Tab') IS NOT NULL)BEGINDROP TABLE #Tab;ENDCreate table #Tab(ID int identity(1,1) primary key,TabName nvarchar(100),HasRow int default 0 not null,StrSQL nvarchar(max))insert into #Tab(TabName)select A.name from sys.tables A where is_ms_shipped = 0and exists(select 1 from sys.columns where object_id = A.object_id and system_type_id in(99,167,175,231,239))SET @LoopID = @@ROWCOUNT;WHILE(@LoopID >1)BEGINSELECT @TabName = TabName FROM #Tab WHERE ID = @LoopIDSET @TempStr = N' or ';SET @WhereStr = Stuff(Isnull((select @TempStr+N'['+B.name +N']'+N' like N'''+@SearchText+'%'''from sys.tables A,sys.columns B where B.object_id = A.object_id and B.system_type_id in(99,167,175,231,239) and is_ms_shipped = 0and A.name =@TabNamefor xml path('')),N''),1,LEN(@TempStr),N'')set @HasRow = 0;SET @StrSQL = N'SELECT @HasRow = count(1) FROM ['+@TabName+'] WHERE '+@WhereStrPrint @StrSQLEXEC SP_EXECUTESQL @StrSQL,N'@HasRow int output',@HasRow OUTPUT--SET @StrSQL = N'SELECT TOP 1 @HasRowOUT = 1 FROM @Table WHERE @Where'--EXEC SP_EXECUTESQL @StrSQL,N'@Table nvarchar(100),@Where nvarchar(max),@HasRowOUT bit output',@Table = @TabName,@Where = @WhereStr,@HasRowOUT = @HasRow OUTPUTIF(@HasRow >0)BEGINUPDATE #Tab SET HasRow = @HasRow ,StrSQL = @StrSQLWHERE ID = @LoopIDENDSET @LoopID = @LoopID -1;ENDSELECT TabName,HasRow,@SearchText, StrSQL FROM #Tab WHERE HasRow >0TRUNCATE TABLE #Tab;DROP TABLE #Tab;SET NOCOUNT OFFENDGO