搜索当前数据库中含有指定字符串的表列

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects
                where id = object_id(N'[dbo].[spFindTextInColumns]')
                      and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFindTextInColumns]
GO

/*
    搜索当前数据库中含有指定字符串的表列

    调用:
    EXECUTE spFindTextInColumns 'AA', 'tcart' --搜索指定表
    EXECUTE spFindTextInColumns default, 'tcart' --搜索所有表
    EXECUTE spFindTextInColumns default, '[吖-丫]' --搜索中文
   
*/
IF OBJECT_ID('spFindTextInColumns') IS NOT NULL
DROP PROCEDURE spFindTextInColumns
GO
CREATE PROCEDURE spFindTextInColumns (@TableName sysname = NULL,
 @StringToLookFor varchar(500))
AS

 DECLARE @columnName nvarchar(128),
  @dateType nvarchar(128),
  @row smallint,
  @rowCount int,
  @sql nvarchar(1000)

 DECLARE string_find_cursor CURSOR FAST_FORWARD FOR
  SELECT [name]
  FROM SYSOBJECTS
  WHERE (
    OBJECTPROPERTY(id, N'IsMsShipped') = 0
    AND OBJECTPROPERTY(id, N'IsUserTable') = 1
   ) AND (@TableName IS NULL OR [name] = @TableName)
  ORDER BY [name]
 
 OPEN string_find_cursor
 
 FETCH NEXT FROM string_find_cursor
 INTO @tableName
 
 SET @StringToLookFor = '%' + @StringToLookFor + '%'
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @row = 1
 
  SELECT @rowCount = MAX([ORDINAL_POSITION])
  FROM [INFORMATION_SCHEMA].[COLUMNS]
  WHERE [TABLE_NAME] = @tableName
  GROUP BY [ORDINAL_POSITION]
 
  WHILE @row <= @rowCount
  BEGIN
   SELECT @columnName = '[' + [COLUMN_NAME] + ']',
    @dateType = [DATA_TYPE]
   FROM [INFORMATION_SCHEMA].[COLUMNS]
   WHERE [TABLE_NAME] = @tableName
    AND [ORDINAL_POSITION] = @row
   ORDER BY [ORDINAL_POSITION]
 
   SET @row = @row + 1
   
   SET @sql = NULL
 
   IF @dateType IN ( N'char', N'varchar', N'text')
    SET @sql = 'SELECT * FROM [' + @tableName + '] WHERE PATINDEX(''' + @StringToLookFor + ''', ' + @columnName + ') > 0'
   ELSE IF @dateType IN (N'nchar', N'nvarchar', N'ntext')
    SET @sql = 'SELECT * FROM [' + @tableName + '] WHERE PATINDEX(''' + @StringToLookFor + ''', CAST(' + @columnName + ' As TEXT)) > 0'
   
   IF @sql IS NOT NULL
   BEGIN
    SET @sql = 'IF EXISTS(' + @sql + ') PRINT ''[' + @tableName + '].' + @columnName + ''''
    --PRINT (@sql)
    EXEC (@sql)
   END
  END
 
  FETCH NEXT FROM string_find_cursor
  INTO @tableName
 END
 
 CLOSE string_find_cursor
 DEALLOCATE string_find_cursor
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO