新增文章

文章标题分类内容

DECLARE @SearchStr NVARCHAR(100) = '99291303-1d0d-42d2-9c5e-5ce8153172d2' -- 修改为你要查找的字符串
   DECLARE @SQL NVARCHAR(MAX)
   DECLARE @TableName NVARCHAR(256)
   DECLARE @ColumnName NVARCHAR(128)
   DECLARE @Schema NVARCHAR(128)
   DECLARE @DataType NVARCHAR(128)

-- 创建临时表存储结果

if  exists (select * from sys.objects where object_id = object_id(N'dbo.Tmp_FindTxt') and type in (N'U'))
               drop     table                                          dbo.Tmp_FindTxt

CREATE TABLE Tmp_FindTxt (

SchemaName NVARCHAR(128),
       TableName NVARCHAR(256),
       ColumnName NVARCHAR(128),
       ColumnValue NVARCHAR(MAX),
       MatchCount INT  -- 修改为MatchCount更准确
   )

-- 获取所有字符串类型的列（包括数据类型信息）

DECLARE TableCursor CURSOR FOR
   SELECT 
       s.name AS SchemaName,
       t.name AS TableName,
       c.name AS ColumnName,
       ty.name AS DataType
   FROM 
       sys.tables t
       INNER JOIN sys.columns c ON t.object_id = c.object_id
       INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
       INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
   WHERE 
       ty.name IN ('nvarchar','varchar','char','nchar','text','ntext')  --  and t.name = 'T_META_OBJECTTYPECACHE'

OPEN TableCursor
   FETCH NEXT FROM TableCursor INTO @Schema, @TableName, @ColumnName, @DataType

WHILE @@FETCH_STATUS = 0
   BEGIN
       -- 对text/ntext类型字段特殊处理
       IF @DataType IN ('text', 'ntext')
       BEGIN
           SET @SQL = 
           'INSERT INTO Tmp_FindTxt (SchemaName, TableName, ColumnName, ColumnValue, MatchCount)
           SELECT 
               ''' + REPLACE(@Schema, '''', '''''') + ''',
               ''' + REPLACE(@TableName, '''', '''''') + ''',
               ''' + REPLACE(@ColumnName, '''', '''''') + ''',
               CAST(SUBSTRING(CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)), 1, 100) AS NVARCHAR(100)),
               COUNT(*) AS MatchCount
           FROM 
               ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + '
           WHERE 
               CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)) LIKE ''%' + REPLACE(@SearchStr, '''', '''''') + '%''
           GROUP BY 
               CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX))'
       END
       ELSE
       BEGIN
           SET @SQL = 
           'INSERT INTO Tmp_FindTxt (SchemaName, TableName, ColumnName, ColumnValue, MatchCount)
           SELECT 
               ''' + REPLACE(@Schema, '''', '''''') + ''',
               ''' + REPLACE(@TableName, '''', '''''') + ''',
               ''' + REPLACE(@ColumnName, '''', '''''') + ''',
               CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)),
               COUNT(*) AS MatchCount
           FROM 
               ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + '
           WHERE 
               ' + QUOTENAME(@ColumnName) + ' LIKE ''%' + REPLACE(@SearchStr, '''', '''''') + '%''
           GROUP BY 
               ' + QUOTENAME(@ColumnName)
       END
    
       BEGIN TRY
           EXEC sp_executesql @SQL
       END TRY
       BEGIN CATCH
           PRINT 'Error searching [' + @Schema + '].[' + @TableName + '].[' + @ColumnName + ']: ' + ERROR_MESSAGE()
       END CATCH
    
       FETCH NEXT FROM TableCursor INTO @Schema, @TableName, @ColumnName, @DataType
   END

CLOSE TableCursor
   DEALLOCATE TableCursor

-- 显示结果

SELECT

'select '+ ColumnName +' ,* from '+ TableName +' where '+ ColumnName +' like ''%' + REPLACE(@SearchStr, '''', '''''') + '%'''  AS '查询',

SchemaName AS '架构名',
       TableName AS '表名',
       ColumnName AS '字段名',
       ColumnValue AS '包含的字段值',
       MatchCount AS '匹配行数'
   FROM 
       Tmp_FindTxt
   ORDER BY 
       SchemaName, TableName, ColumnName

结果：

查询	架构名	表名	字段名	包含的字段值	匹配行数
select FID ,* from T_META_OBJECTTYPECACHE where FID like '%99291303-1d0d-42d2-9c5e-5ce8153172d2%'	dbo	T_META_OBJECTTYPECACHE	FID	99291303-1d0d-42d2-9c5e-5ce8153172d2	1