在数据库的所有表、行和列中搜索字符串

小开

事实上，我同意 MikeW (+ 1) ，这是更好的使用剖析器为这种情况。

无论如何，如果您确实需要获取 db 中的所有(n) varchar 列并进行搜索，请参见下面的内容。我假设使用 INFORATION _ SCHEMA. Tables + Dynamic SQL。普通搜索:

DECLARE @SearchText VARCHAR(100)
SET @SearchText = '12'
DECLARE @Tables TABLE(N INT, TableName VARCHAR(100), ColumnNamesCSV VARCHAR(2000), SQL VARCHAR(4000))


INSERT INTO @Tables (TableName, ColumnNamesCSV)
SELECT  T.TABLE_NAME AS TableName,
( SELECT C.Column_Name + ','
FROM   INFORMATION_SCHEMA.Columns C
WHERE  T.TABLE_NAME = C.TABLE_NAME
AND C.DATA_TYPE IN ('nvarchar','varchar')
FOR XML PATH('')
)
FROM    INFORMATION_SCHEMA.Tables T


DELETE FROM @Tables WHERE ColumnNamesCSV IS NULL


INSERT INTO @Tables (N, TableName, ColumnNamesCSV)
SELECT ROW_NUMBER() OVER(ORDER BY TableName), TableName, ColumnNamesCSV
FROM   @Tables


DELETE FROM @Tables WHERE N IS NULL


UPDATE @Tables
SET ColumnNamesCSV = SUBSTRING(ColumnNamesCSV, 0, LEN(ColumnNamesCSV))


UPDATE @Tables
SET SQL = 'SELECT * FROM ['+TableName+'] WHERE '''+@SearchText+''' IN ('+ColumnNamesCSV+')'


DECLARE @C INT,
@I INT,
@SQL VARCHAR(4000)


SELECT @I = 1,
@C = COUNT(1)
FROM   @Tables


WHILE @I <= @C BEGIN
SELECT @SQL = SQL FROM @Tables WHERE N = @I
SET @I = @I+1
EXEC(@SQL)
END

还有一个是 LIKE 条款:

DECLARE @SearchText VARCHAR(100)
SET @SearchText = '12'


DECLARE @Tables TABLE(N INT, TableName VARCHAR(100), ColumnNamesCSVLike VARCHAR(2000), LIKESQL VARCHAR(4000))


INSERT INTO @Tables (TableName, ColumnNamesCSVLike)
SELECT   T.TABLE_NAME AS TableName,
(   SELECT  C.Column_Name + ' LIKE ''%'+@SearchText+'%'' OR '
FROM    INFORMATION_SCHEMA.Columns C
WHERE   T.TABLE_NAME = C.TABLE_NAME
AND C.DATA_TYPE IN ('nvarchar','varchar')
FOR XML PATH(''))
FROM     INFORMATION_SCHEMA.Tables T


DELETE FROM @Tables WHERE ColumnNamesCSVLike IS NULL


INSERT INTO @Tables (N, TableName, ColumnNamesCSVLike)
SELECT ROW_NUMBER() OVER(ORDER BY TableName), TableName, ColumnNamesCSVLike
FROM @Tables


DELETE FROM @Tables WHERE N IS NULL


UPDATE @Tables
SET  ColumnNamesCSVLike = SUBSTRING(ColumnNamesCSVLike, 0, LEN(ColumnNamesCSVLike)-2)


UPDATE @Tables SET LIKESQL = 'SELECT * FROM ['+TableName+'] WHERE '+ColumnNamesCSVLike


DECLARE @C INT,
@I INT,
@LIKESQL VARCHAR(4000)


SELECT @I = 1,
@C = COUNT(1)
FROM @Tables


WHILE @I <= @C BEGIN
SELECT @LIKESQL = LIKESQL FROM @Tables WHERE N = @I
SET @I = @I +1
EXEC(@LIKESQL)
END

小开

最佳答案

这段代码在 SQL2005中应该可以做到这一点，但是需要注意以下几点:

慢得可笑。我在一个只有几张表的小型数据库上进行了测试，花了很多分钟才完成。如果你的数据库太大，你无法理解它，那么这个数据库很可能无法使用。
这是我即兴写的。我没有放入任何错误处理，可能还有其他一些草率，特别是因为我不经常使用游标。例如，我认为有一种方法可以刷新列光标，而不是每次都关闭/释放/重新创建它。

如果你不能理解数据库或者不知道东西是从哪里来的，那么你应该找一个能理解的人。即使您能够找到数据所在的位置，它也可能在某个地方被复制，或者数据库的其他方面您可能不了解。如果你的公司里没有人了解数据库，那么你就陷入了一个相当大的混乱之中。

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_schema   SYSNAME,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)


SET @search_string = 'Test'


DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'


OPEN tables_cur


FETCH NEXT FROM tables_cur INTO @table_schema, @table_name


WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL  -- Only strings have this and they always have it


OPEN columns_cur


FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE ''%' + @search_string + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ', ' + QUOTENAME(@column_name) + ''''


EXECUTE(@sql_string)


FETCH NEXT FROM columns_cur INTO @column_name
END


CLOSE columns_cur


DEALLOCATE columns_cur


FETCH NEXT FROM tables_cur INTO @table_schema, @table_name
END


CLOSE tables_cur


DEALLOCATE tables_cur

小开

如果您从应用程序“获取数据”，明智的做法是在运行应用程序时使用分析器并分析数据库。跟踪它，然后在结果中搜索该字符串。

小开

我改编了一个最初由 2002年的 Narayana Vyas Kondreddi编写的脚本。通过使用 patindex 而不是 like，我修改了 where 子句以检查 text/ntext 字段。我还稍微修改了结果表。不合理地，我改变了变量名，并按照我喜欢的方式进行了对齐(无意冒犯 Kondretti 先生)。用户可能希望更改所搜索的数据类型。我使用了一个全局表来允许在处理过程中进行查询，但是使用永久表可能是一种更明智的方法。

/* original script by Narayana Vyas Kondreddi, 2002 */
/* adapted by Oliver Holloway, 2009 */


/* these lines can be replaced by use of input parameter for a proc */
declare @search_string varchar(1000);
set @search_string = 'what.you.are.searching.for';


/* create results table */
create table ##string_locations (
table_name varchar(1000),
field_name varchar(1000),
field_value varchar(8000)
)
;
/* special settings */
set nocount on
;
/* declare variables */
declare
@table_name varchar(1000),
@field_name varchar(1000)
;
/* variable settings */
set @table_name = ''
;
set @search_string = QUOTENAME('%' + @search_string + '%','''')
;
/* for each table */
while @table_name is not null
begin


set @field_name = ''
set @table_name = (
select MIN(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name))
from INFORMATION_SCHEMA.TABLES
where
table_type = 'BASE TABLE' and
QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) > @table_name and
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)), 'IsMSShipped') = 0
)


/* for each string-ish field */
while (@table_name is not null) and (@field_name is not null)
begin
set @field_name = (
select MIN(QUOTENAME(column_name))
from INFORMATION_SCHEMA.COLUMNS
where
table_schema    = PARSENAME(@table_name, 2) and
table_name  = PARSENAME(@table_name, 1) and
data_type in ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext') and
QUOTENAME(column_name) > @field_name
)


/* search that field for the string supplied */
if @field_name is not null
begin
insert into ##string_locations
exec(
'select ''' + @table_name + ''',''' + @field_name + ''',' + @field_name +
'from ' + @table_name + ' (nolock) ' +
'where patindex(' + @search_string + ',' + @field_name + ') > 0'  /* patindex works with char & text */
)
end
;
end
;
end
;


/* return results */
select table_name, field_name, field_value from ##string_locations (nolock)
;
/* drop temp table */
--drop table ##string_locations
;

小开

SSMS Tools pACK Add-In (Add-On)用于 Microsoft SQL Server 和 Microsoft SQL Server 的 Management Studio Express，可以完全满足您的需要。在较大的数据库中，搜索需要一些时间，但这是可以预料到的。它还包含了大量很酷的特性，这些特性本应该首先包含在 SQLServerManagementStudio 中。试试 Www.ssmstoolspack.com/

要运行这些工具，需要安装 SP2 for SQLServerManagementStudio。

小开

要“查找数据的来源”，您可以启动 SQL Profiler、启动报告或应用程序，然后您将看到针对数据库发出的所有查询。

小开

已经发布的其他答案可能同样有效或更好，但我还没有使用它们。然而，下面的 SQL 我已经使用，它真的帮助了我，当我试图逆向工程一个大系统与一个巨大的(和非常杂乱的) SQL Server 数据库。

这不是我的原则。我希望我可以归功于原作者，但我找不到文章的链接了: (

Use
go


declare @SearchChar varchar(8000)
Set @SearchChar =  -- Like 'A%', '11/11/2006'


declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000)
declare @ColumnName varchar(100),@TableName varchar(100)


declare dbTable cursor for
SELECT
Distinct b.Name as TableName
FROM
sysobjects b
WHERE
b.type='u' and b.Name  'dtproperties'
order by b.name
open dbTable
fetch next from dbTable into @TableName


WHILE @@FETCH_STATUS = 0
BEGIN
declare db cursor for
SELECT
c.Name as ColumnName
FROM
sysobjects b,
syscolumns c
WHERE
C.id = b.id and
b.type='u' and b.Name = @TableName
order by b.name
open db
fetch next from db into @ColumnName
set @CMDMain = 'SELECT ' + char(39) + @TableName + char(39) + ' as TableName,'+
' ['+ @TableName + '].* FROM [' + @TableName + ']'+
' WHERE '
set @CMDMainCount = 'SELECT Count(*) FROM [' + @TableName + '] Where '
Set @CMDJoin = ''
WHILE @@FETCH_STATUS = 0
BEGIN
set @CMDJoin = @CMDJoin + 'Convert(varchar(5000),[' +@ColumnName + ']) like ' + char(39) + @SearchChar + char(39) + ' OR '


fetch next from db into @ColumnName
end
close db
deallocate db


Set @CMDMainCount = 'If ('+ @CMDMainCount + Left(@CMDJoin, len(@CMDJoin) - 3)+ ') > 0 Begin '
Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) - 3)
Set @CMDMain = @CMDMain + ' End '


Print @CMDMain


exec (@CMDMain)
fetch next from dbTable into @TableName
end
close dbTable
deallocate dbTable

小开

/*
This procedure is for finding any string or date in all tables
if search string is date, its format should be yyyy-MM-dd
eg. 2011-07-05
*/


-- ================================================
-- Exec SearchInTables 'f6f56934-a5d4-4967-80a1-1a2223b9c7b1'


-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Joshy,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE SearchInTables
@myValue nvarchar(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


-- Insert statements for procedure here
DECLARE @searchsql nvarchar(max)
DECLARE @table_name nvarchar(1000)
DECLARE @Schema_name nvarchar(1000)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @XMLIn nvarchar(max)
SET @ParmDefinition = N'@XMLOut varchar(max) OUTPUT'


SELECT A.name,b.name
FROM sys.tables A
INNER JOIN sys.schemas B ON A.schema_id=B.schema_id
WHERE A.name like 'tbl_Tax_Sections'


DECLARE tables_cur CURSOR FOR
SELECT A.name,b.name FOM sys.tables A
INNER JOIN sys.schemas B ON A.schema_id=B.schema_id
WHERE A.type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name , @Schema_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @searchsql ='SELECT @XMLOut=(SELECT PATINDEX(''%'+ @myValue+ '%'''
SET @searchsql =@searchsql  + ', (SELECT * FROM '+@Schema_name+'.'+@table_name+' FOR XML AUTO) ))'
--print @searchsql
EXEC sp_executesql @searchsql, @ParmDefinition, @XMLOut=@XMLIn OUTPUT
--print @XMLIn


IF @XMLIn <> 0 PRINT @Schema_name+'.'+@table_name


FETCH NEXT FROM tables_cur INTO @table_name , @Schema_name


END
CLOSE tables_cur
DEALLOCATE tables_cur
RETURN
END
GO

小开

虽然之前提出的解决方案是有效的和可行的，但是我谦虚地提供了一个更清晰、更优雅、性能更好的代码，至少在我看来是这样的。

首先，人们可能会问: 为什么会有人需要一个代码片段来全局地盲目地查找字符串？嘿，他们已经发明了全文，你不知道吗？

我的答案是: 我的主要工作是在系统集成项目中，当我学习一个新的、没有文档的数据库时，发现数据的写入位置非常重要，这种情况很少发生。

此外，我提出的代码是一个更强大和更危险的脚本，搜索和替换整个数据库的文本的精简版本。

CREATE TABLE #result(
id      INT IDENTITY, -- just for register seek order
tblName VARCHAR(255),
colName VARCHAR(255),
qtRows  INT
)
go


DECLARE @toLookFor VARCHAR(255)
SET @toLookFor = '[input your search criteria here]'


DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
'[' + usr.name + '].[' + tbl.name + ']' AS tblName,
'[' + col.name + ']' AS colName,
LOWER(typ.name) AS typName
FROM
sysobjects tbl
INNER JOIN(
syscolumns col
INNER JOIN systypes typ
ON typ.xtype = col.xtype
)
ON col.id = tbl.id
--
LEFT OUTER JOIN sysusers usr
ON usr.uid = tbl.uid


WHERE tbl.xtype = 'U'
AND LOWER(typ.name) IN(
'char', 'nchar',
'varchar', 'nvarchar',
'text', 'ntext'
)
ORDER BY tbl.name, col.colorder
--
DECLARE @tblName VARCHAR(255)
DECLARE @colName VARCHAR(255)
DECLARE @typName VARCHAR(255)
--
DECLARE @sql  NVARCHAR(4000)
DECLARE @crlf CHAR(2)


SET @crlf = CHAR(13) + CHAR(10)


OPEN cCursor
FETCH cCursor
INTO @tblName, @colName, @typName


WHILE @@fetch_status = 0
BEGIN
IF @typName IN('text', 'ntext')
BEGIN
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
SET @sql = @sql + 'FROM ' + @tblName + @crlf
SET @sql = @sql + 'WHERE PATINDEX(''%'' + @toLookFor + ''%'', ' + @colName + ') > 0' + @crlf
END
ELSE
BEGIN
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
SET @sql = @sql + 'FROM ' + @tblName + @crlf
SET @sql = @sql + 'WHERE ' + @colName + ' LIKE ''%'' + @toLookFor + ''%''' + @crlf
END


EXECUTE sp_executesql
@sql,
N'@tblName varchar(255), @colName varchar(255), @toLookFor varchar(255)',
@tblName, @colName, @toLookFor


FETCH cCursor
INTO @tblName, @colName, @typName
END


SELECT *
FROM #result
WHERE qtRows > 0
ORDER BY id
GO


DROP TABLE #result
go

小开

我建议你为自己找一个第三方工具，比如 ApexSQL 搜索(可能还有其他工具，但我用这个，因为它是免费的)。

如果您真的想使用 SQL 方式，可以尝试使用 Sorna Kumar Muthuraj -复制的代码如下。只需对模式中的所有表执行此存储过程(使用动态 SQL 很容易)

CREATE PROCEDURE SearchTables
@Tablenames VARCHAR(500)
,@SearchStr NVARCHAR(60)
,@GenerateSQLOnly Bit = 0
AS


/*
Parameters and usage


@Tablenames        -- Provide a single table name or multiple table name with comma seperated.
If left blank , it will check for all the tables in the database
@SearchStr        -- Provide the search string. Use the '%' to coin the search.
EX : X%--- will give data staring with X
%X--- will give data ending with X
%X%--- will give data containig  X
@GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.
By default it is 0 and it will search.


Samples :


1. To search data in a table


EXEC SearchTables @Tablenames = 'T1'
,@SearchStr  = '%TEST%'


The above sample searches in table T1 with string containing TEST.


2. To search in a multiple table


EXEC SearchTables @Tablenames = 'T2'
,@SearchStr  = '%TEST%'


The above sample searches in tables T1 & T2 with string containing TEST.


3. To search in a all table


EXEC SearchTables @Tablenames = '%'
,@SearchStr  = '%TEST%'


The above sample searches in all table with string containing TEST.


4. Generate the SQL for the Select statements


EXEC SearchTables @Tablenames        = 'T1'
,@SearchStr        = '%TEST%'
,@GenerateSQLOnly    = 1


*/


SET NOCOUNT ON


DECLARE @CheckTableNames Table
(
Tablename sysname
)


DECLARE @SQLTbl TABLE
(
Tablename        SYSNAME
,WHEREClause    VARCHAR(MAX)
,SQLStatement   VARCHAR(MAX)
,Execstatus        BIT
)


DECLARE @sql VARCHAR(MAX)
DECLARE @tmpTblname sysname


IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
BEGIN


INSERT INTO @CheckTableNames
SELECT Name
FROM sys.tables
END
ELSE
BEGIN


SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + ''''


INSERT INTO @CheckTableNames
EXEC(@sql)


END


INSERT INTO @SQLTbl
( Tablename,WHEREClause)
SELECT SCh.name + '.' + ST.NAME,
(
SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10)
FROM SYS.columns SC
JOIN SYS.types STy
ON STy.system_type_id = SC.system_type_id
AND STy.user_type_id =SC.user_type_id
WHERE STY.name in ('varchar','char','nvarchar','nchar')
AND SC.object_id = ST.object_id
ORDER BY SC.name
FOR XML PATH('')
)
FROM  SYS.tables ST
JOIN @CheckTableNames chktbls
ON chktbls.Tablename = ST.name
JOIN SYS.schemas SCh
ON ST.schema_id = SCh.schema_id
WHERE ST.name <> 'SearchTMP'
GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ;


UPDATE @SQLTbl
SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)


DELETE FROM @SQLTbl
WHERE WHEREClause IS NULL


WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
BEGIN


SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement
FROM @SQLTbl
WHERE ISNULL(Execstatus ,0) = 0






IF @GenerateSQLOnly = 0
BEGIN


IF OBJECT_ID('SearchTMP','U') IS NOT NULL
DROP TABLE SearchTMP
EXEC (@SQL)


IF EXISTS(SELECT 1 FROM SearchTMP)
BEGIN
SELECT Tablename=@tmpTblname,* FROM SearchTMP
END


END
ELSE
BEGIN
PRINT REPLICATE('-',100)
PRINT @tmpTblname
PRINT REPLICATE('-',100)
PRINT replace(@sql,'INTO SearchTMP','')
END


UPDATE @SQLTbl
SET Execstatus = 1
WHERE Tablename = @tmpTblname


END


SET NOCOUNT OFF


go

小开

这不使用游标或类似的东西，只使用一个动态查询。

还要注意，这里使用的是 LIKE。因为那正是我需要的。它适用于所有模式、所有表和只查询 NVARCHAR或 VARCHAR的那些列，即使它们具有 UDDT。

DECLARE @searchstring  NVARCHAR(255)
SET @searchstring = '%searchstring%'


DECLARE @sql NVARCHAR(max)


SELECT @sql = STUFF((
SELECT ' UNION ALL SELECT ''' + TABLE_NAME + ''' AS tablename, ''' + COLUMN_NAME + ''' AS columnname, ' + COLUMN_NAME + ' AS valuename' +
' FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME +
' WHERE ' + COLUMN_NAME + ' LIKE ''' + @searchstring + ''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE in ('nvarchar', 'varchar')
FOR XML PATH('')
) ,1, 11, '')


EXEC(@sql)

输出给出了表、列和值。在一个小型数据库上执行的时间大约是3秒，有大约3000个结果。

小开

@ NLwino，很好的查询，有一些关键字使用的错误。我不得不对它进行一些修改，以便用[]包装关键字，并查看 char 和 ntext 列。

    DECLARE @searchstring  NVARCHAR(255)
SET @searchstring = '%WDB1014%'


DECLARE @sql NVARCHAR(max)


SELECT @sql = STUFF((
SELECT ' UNION ALL SELECT ''' + TABLE_NAME + ''' AS tbl, ''' + COLUMN_NAME + ''' AS col, [' + COLUMN_NAME + '] AS val' +
' FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME +
'] WHERE [' + COLUMN_NAME + '] LIKE ''' + @searchstring + ''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE in ('nvarchar', 'varchar', 'char', 'ntext')
FOR XML PATH('')
) ,1, 11, '')


Exec (@sql)

我在2.5 GB 的数据库里运行了一下，结果51秒就出来了

小开

我认为这可能是一个最简单的方法，找到一个字符串在您的数据库的所有行-不使用光标和 用于 XML-。

CREATE PROCEDURE SPFindAll (@find VARCHAR(max) = '')
AS
BEGIN
SET NOCOUNT ON;
--
DECLARE @query VARCHAR(max) = ''


SELECT  @query = @query +
CASE
WHEN @query = '' THEN ''
ELSE ' UNION ALL '
END +
'SELECT ''' + s.name + ''' As schemaName, ''' + t.name + ''' As tableName, ''' + c.name + ''' As ColumnName, [' + c.name + '] COLLATE DATABASE_DEFAULT As [Data] FROM [' + s.name + '].[' + t.name + '] WHERE [' + c.name + '] Like ''%' + @find + '%'''
FROM
sys.schemas s
INNER JOIN
sys.tables t ON s.[schema_id] = t.[schema_id]
INNER JOIN
sys.columns c ON t.[object_id] = c.[object_id]
INNER JOIN
sys.types ty ON c.user_type_id = ty.user_type_id
WHERE
ty.name LIKE '%char'


EXEC(@query)
END

通过创建这个存储过程，您可以像下面这样为您想要查找的任何字符串运行它:

EXEC SPFindAll 'Hello World'

结果会是这样的:

schemaName | tableName | columnName | Data
-----------+-----------+------------+-----------------------
schema1    | Table1    | Column1    | Hello World
schema1    | Table1    | Column1    | Hello World!
schema1    | Table2    | Column1    | I say "Hello World".
schema1    | Table2    | Column2    | Hello World

小开

或者，您可以在这里使用我的查询，应该比必须为您想要搜索的每个数据库创建 sProcs 更简单: 博客

/* Reto Egeter, fullparam.wordpress.com */


DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */


IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))


SET NOCOUNT ON


DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))


WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM    INFORMATION_SCHEMA.TABLES
WHERE       TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
IF @TableName IS NOT NULL
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE       TABLE_SCHEMA    = PARSENAME(''' + @TableName + ''', 2)
AND TABLE_NAME  = PARSENAME(''' + @TableName + ''', 1)
AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
INSERT INTO @ColumnNameTable
EXEC (@sql)
WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
BEGIN
PRINT @ColumnName
SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
INSERT INTO #Results
EXEC(@sql)
IF @@ROWCOUNT > 0 IF @FullRowResult = 1
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
EXEC(@sql)
END
DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
END
END
END
SET NOCOUNT OFF

选择 TableName，ColumnName，ColumnValue，ColumnType，COUNT (*)作为 COUNT FROM # Results GROUP BY TableName，ColumnName，ColumnValue，ColumnType

小开

这个查询可以为您解决这个问题。

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)


SET @search_string = 'StringtoSearch'


DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'


OPEN tables_cur


FETCH NEXT FROM tables_cur INTO @table_name, @table_id


WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id
AND system_type_id IN (167, 175, 231, 239, 99)


OPEN columns_cur


FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + ']
LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''


EXECUTE(@sql_string)


FETCH NEXT FROM columns_cur INTO @column_name
END


CLOSE columns_cur


DEALLOCATE columns_cur


FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END


CLOSE tables_cur
DEALLOCATE tables_cur