logo标签列表

如何在SQL Server数据库中搜索字符串？

sql-serverfunctionsearchstored-procedures
147

我知道是可以做到的，但我不知道如何实现。

我需要在 SQL Server 数据库中搜索特定字符串的所有出现情况。

例如：我想要搜索所有表、视图、函数、存储过程等，以查找字符串 "tblEmployes"（而不是表内的数据）。

我需要这样做的原因之一是我想要删除一些额外的数据表，但我担心它们可能在某些过程或函数中被使用。

7
Redgate SQL Search 是一个 SQL Server 管理工具，可帮助用户快速搜索 SQL Server 对象并进行文本查找。此工具可以作为 SQL Server Management Studio 的插件使用，也可以独立运行。 - Mikael Eriksson
1
希望这能帮助到某些人，http://stackoverflow.com/questions/13174627/searching-text-from-db-sql-server/22854200#22854200 - NoNaMe
16个回答
199
这将搜索特定数据库中每个表的每个列。在要搜索的数据库上创建存储过程。 < p > SQL Server 十大最常问问题及其答案 ： 
CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'Base Table'
            And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')


DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                            THEN 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       = ''' + @DataToFind + '''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            ELSE 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       Like ''%' + @DataToFind + '%''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            END,
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i


        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

        SET @i = @i + 1
    END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

要运行它，只需执行以下操作：

exec FindMyData_string 'google', 0

它运行得非常好！！！

2
第二个参数“exactMatch = 0”是什么意思？ - Junchen Liu
11
它只返回找到的第一个结果，没有其他任何内容。有没有办法让它返回数据库中字符串的所有实例？ - qroberts
2
我该把这个脚本保存在哪里？文件需要什么扩展名才能被执行？我该在哪里执行exec FindMyData_string 'google', 0？ - Black
一些数据库不区分大小写，请在您的代码中使用INFORMATION_SCHEMA.COLUMNS。否则，此脚本将抛出“无效对象名称Information_Schema”的错误。 - Fatih
我尝试编辑以适应区分大小写的排序规则，但出了些问题，它告诉我“编辑队列已满”。 - Chris
显示剩余3条评论
62

如果您需要通过名称查找数据库对象（例如表、列和触发器），可以使用免费的Redgate软件工具SQL Search，它可以搜索整个数据库中的任何字符串。

Enter image description here

Enter image description here

这是一款对于任何DBA或数据库开发人员来说必备的工具 - 我已经提到它绝对是免费使用的，不管用途如何？
30
好的工具，但它无法在表格中搜索字符串。 - JGilmartin
2
不搜索实际行 - LearningJrDev
9
@LearningJrDev: 不会 - 它通过名称搜索数据库对象，如表、视图、存储过程等。 它不会搜索表中包含的数据 - 我从未声称它会这样做！ - marc_s
4
@JGilmartin，让我引用问题的一部分：“我想搜索所有表、视图、函数、存储过程……中的字符串“tblEmployes”。（不是表中的数据）。”如果您想搜索表中的数据，您可以使用 T-SQL 语言。这个工具非常适合重构任务。 - nemke
51

您也可以尝试使用ApexSQL Search——这是一个类似于SQL Search的免费SSMS插件。

如果您真的只想使用SQL，您可能想尝试使用此脚本：

select
S.name as [Schema],
o.name as [Object],
o.type_desc as [Object_Type],
C.text as [Object_Definition]
from sys.all_objects O inner join sys.schemas S on O.schema_id = S.schema_id
inner join sys.syscomments C on O.object_id = C.id
where S.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemas
and C.text like '%ICE_%'
order by [Schema]
6
此查询仅搜索对象。我们需要在所有现有的表格中搜索一个字符串。 - César León
ApexSQL非常出色，可以让您选择您的类型。 - PeterFnet
3
现在它不再是免费的了 :( - labilbe
30

您可以将数据库（如果较小）导出到硬盘/桌面上，然后通过文本搜索程序或文本编辑器进行字符串搜索。

你也可以使用脚本。但是一个好的文本编辑器几乎可以为SQL代码提供所需的一切。 - Glorious Kale
2
哪个文本编辑器会愿意加载许多GB的数据呢？ - Bohdan
可能没有，但在这种情况下，您可以使用第三方文件搜索引擎，并且有一些应用程序可以将文件分成任意多个部分。 - Glorious Kale
2
MS SQL的导出文件是二进制文件，因此无法使用您提出的方法进行导出、读取或搜索。 - Spencer Hill
这真的可能吗？我在许多其他系统中都这样做（每个表一个文件，每行一个记录，然后只需使用grep...但在MSSQL中似乎根本不存在任何SQL“dump”... 编辑我忘了，在MacOS上... Azure不支持它，而SQL Server Management Studio也不可用。 - estani
@estani 这对你有用吗？https://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/ - 使用sqlcmd在SQL中转储数据库 - 但不幸的是，Spencer说MSSQL转储文件是二进制的。 - Glorious Kale
16

在SQL Server中按名称获取表的方法：

SELECT *
FROM sys.Tables
WHERE name LIKE '%Employees%'

查找存储过程的方法：

SELECT name
FROM sys.objects
WHERE name = 'spName'

获取与表相关的所有存储过程：

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
4
这些查询只搜索对象。我们需要在所有现有的表中搜索一个字符串。 - César León
12

这段代码是在搜索过程中寻找函数和过程，但不会在表格中进行搜索 :)

SELECT name 
FROM   sys.all_objects 
WHERE  Object_definition(object_id) 
LIKE '%text%' 
ORDER BY name
5
所有存储过程、视图和函数的内容都存储在表格 sysComments 的 text 字段中。所有对象的名称都存储在表格 sysObjects 中，列存储在 sysColumns 中。
有了这些信息，您可以使用以下代码在视图、存储过程和函数的内容中搜索指定的单词：
Select b.name from syscomments a
inner join sysobjects b on a.id = b.id
where text like '%tblEmployes%'

这个查询将会给出包含单词"tblEmployes"的对象。

如果按照对象名称进行搜索，您可以使用以下代码：

Select name from sysobjects
where name like  '%tblEmployes%'

最后，要找到至少有一个列包含单词“tblEmployes”的对象，您可以使用以下代码：

Select b.name from syscolumns a inner join sysobjects b on a.id = b.id
where a.name like  '%tblEmployes%'

您可以使用union将这三个查询组合起来：

Select distinct b.name from syscomments a
inner join sysobjects b on a.id = b.id
where text like '%tblEmployes%'
union
Select distinct name from sysobjects
where name like  '%tblEmployes%'
union
Select distinct b.name from syscolumns a inner join sysobjects b on a.id = b.id
where a.name like  '%tblEmployes%'

通过此查询，您可以获取所有包含单词“tblEmployes”在内容、名称或列中的对象。
4

你可以：

  1. 将数据库脚本写入单个文件，然后使用文本编辑器搜索文件中的tblEmployees。在SQL Server Management Studio（SSMS）中，右键单击数据库，选择生成脚本。
  2. 通过右键单击tblEmployees并选择"查看依赖项"来使用SSMS的"查看依赖项"功能，以查看哪些其他对象依赖于它。
  3. 使用免费的第三方工具，例如Redgate Software's SQL Search，按名称和关键字搜索所有数据库对象。
#1 听起来不错。但我无法在服务器上运行它，因为我没有访问权限。 - bobetko
谢谢。不确定为什么有人给你-1分。我已经修复了。尝试了RedGate... 它完全符合我的要求。 - bobetko
3
我被授予了访问数据库的权限，但没有访问存储我的查询的表格的权限。
受@marc_s的回答启发，我看了一下HeidiSQL。这是一个可以处理MySQL、SQL Server和PostgreSQL的Windows程序。
我发现它还可以在数据库中搜索字符串。

Click Search, then Find text on Server

Search tool open. Make sure the DB is selected

它将搜索每个表，并提供每个表中找到该字符串的次数！
3

我的版本...

我将它命名为“大海捞针”，这个名字很容易理解。

它会在每一行和每一列中搜寻一个特定的值，而不是搜索列名等内容。

执行搜索（当然需要将前两个变量替换为相应的值）：

DECLARE @SEARCH_DB VARCHAR(100)='REPLACE_WITH_YOUR_DB_NAME'
DECLARE @SEARCH_VALUE_LIKE NVARCHAR(100)=N'%REPLACE_WITH_SEARCH_STRING%'

SET NOCOUNT ON;
DECLARE col_cur CURSOR FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.columns WHERE TABLE_CATALOG=@SEARCH_DB AND DATA_TYPE NOT IN ('timestamp', 'datetime');

DECLARE @TOTAL int = (SELECT COUNT(*)
FROM information_schema.columns WHERE TABLE_CATALOG=@SEARCH_DB AND DATA_TYPE NOT IN ('timestamp', 'datetime'));


DECLARE @TABLE_CATALOG nvarchar(500), @TABLE_SCHEMA nvarchar(500), @TABLE_NAME nvarchar(500), @COLUMN_NAME nvarchar(500), @DATA_TYPE nvarchar(500);
DECLARE @SQL nvarchar(4000)='';

PRINT '-------- BEGIN SEARCH --------';
OPEN col_cur;

FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;

BEGIN TRY DROP TABLE ##RESULTS; END TRY BEGIN CATCH END CATCH
CREATE TABLE ##RESULTS( TABLE_CATALOG nvarchar(500), TABLE_SCHEMA nvarchar(500), TABLE_NAME nvarchar(500), COLUMN_NAME nvarchar(500), DATA_TYPE nvarchar(500), RECORDS int)
DECLARE @SHOULD_CAST bit=0
DECLARE @i int =0
DECLARE @progress_sum bigint=0

WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT '' + CAST(@i as varchar(100)) +' of ' + CAST(@TOTAL as varchar(100)) + '  ' + @TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME+': '+@COLUMN_NAME+' ('+@DATA_TYPE+')';

    SET @SHOULD_CAST = (SELECT CASE @DATA_TYPE
                                WHEN 'varchar' THEN 0
                                WHEN 'nvarchar' THEN 0
                                WHEN 'char' THEN 0
                                ELSE 1 END)

    SET @SQL='SELECT '''+@TABLE_CATALOG+''' catalog_name, '''+@TABLE_SCHEMA+''' schema_name, '''+@TABLE_NAME+''' table_name, '''+@COLUMN_NAME+''' column_name, '''+@DATA_TYPE+''' data_type, ' +
            +' COUNT(['+@COLUMN_NAME+']) records '+
            +' FROM '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME +
            +' WHERE ' + CASE WHEN @SHOULD_CAST=1 THEN 'CAST(['+@COLUMN_NAME + '] as NVARCHAR(max)) ' ELSE ' ['+@COLUMN_NAME + '] ' END
            +' LIKE '''+ @SEARCH_VALUE_LIKE + ''' '

    -- PRINT @SQL;

    IF @i % 100 = 0
        BEGIN
            SET @progress_sum = (SELECT SUM(RECORDS) FROM ##RESULTS)
            PRINT CAST (@i as varchar(100)) +' of ' + CAST(@TOTAL as varchar(100)) +': '+ CAST (@progress_sum as varchar(100))
        END

    INSERT INTO ##RESULTS (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, RECORDS)
    EXEC(@SQL)

    FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
    SET @i=@i+1
    -- IF @i > 1000
    --     BREAK
END
CLOSE col_cur;
DEALLOCATE col_cur;

SELECT * FROM ##RESULTS WHERE RECORDS>0;

然后，如果想要在执行过程中从另一个窗口查看结果，请执行以下操作：

DECLARE @SEARCH_VALUE_LIKE NVARCHAR(100)=N'%@FLEX@%'
SELECT * FROM ##RESULTS WHERE RECORDS>0;

SET NOCOUNT ON;
DECLARE col_cur CURSOR FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM ##RESULTS WHERE RECORDS>0;

DECLARE @TABLE_CATALOG nvarchar(500), @TABLE_SCHEMA nvarchar(500), @TABLE_NAME nvarchar(500), @COLUMN_NAME nvarchar(500), @DATA_TYPE nvarchar(500);
DECLARE @SQL nvarchar(4000)='';

OPEN col_cur;

FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
DECLARE @i int =0
DECLARE @SHOULD_CAST bit=0

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SHOULD_CAST = (SELECT CASE @DATA_TYPE
                                WHEN 'varchar' THEN 0
                                WHEN 'nvarchar' THEN 0
                                WHEN 'char' THEN 0
                                ELSE 1 END)

    SET @SQL='SELECT '''+@TABLE_CATALOG+''' catalog_name, '''+@TABLE_SCHEMA+''' schema_name, '''+@TABLE_NAME+''' table_name, '''+@COLUMN_NAME+''' column_name, '''+@DATA_TYPE+''' data_type, ' +
            +' ['+@COLUMN_NAME+']'+
            +', * '
            +' FROM '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME +
            +' WHERE ' + CASE WHEN @SHOULD_CAST=1 THEN 'CAST(['+@COLUMN_NAME + '] as NVARCHAR(max)) ' ELSE ' ['+@COLUMN_NAME + '] ' END
            +' LIKE '''+ @SEARCH_VALUE_LIKE + ''' '

    PRINT @SQL;

    EXEC(@SQL)

    FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
    SET @i=@i+1
    -- IF @i > 10
    --    BREAK
END
CLOSE col_cur;
DEALLOCATE col_cur;

以下是相关内容：

  • 它使用游标而不是阻塞的while循环
  • 可以打印进度（如果需要，请取消注释）
  • 可以在几次尝试后退出（取消结尾处的IF注释）
  • 显示所有记录
  • 您可以根据需要进行微调

免责声明：

  • 请勿在生产环境中运行！
  • 它非常慢。如果数据库被其他服务/用户访问，请在所有选择语句中的每个表名后添加“ WITH（NOLOCK）”，特别是动态选择语句。
  • 它不能验证/保护免受各种SQL注入选项的攻击。
  • 如果您的数据库非常大，请为一些睡眠做好准备，确保查询不会在几分钟后被终止。
  • 它将一些值转换为字符串，包括整数/大整数/小整数/微整数。如果您不需要这些，请将它们与脚本顶部的时间戳放在同一个排除列表中。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接