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

我知道有可能，但我不知道怎么做。

我需要在 SQLServer 数据库中搜索特定字符串的所有提及。

例如: 我希望搜索所有表、视图、函数、存储过程，... 以获得字符串“ tblEmployes”(而不是表中的数据)。

我需要这样做的原因之一是，我想删除一些额外的数据表，创建，但我担心他们可能是用在过程或函数的地方。

502706 次浏览
小开

在 SQLServer 中按名称获取表:

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%'
小开

这将搜索特定数据库中每个表的每一列。在要搜索的数据库上创建存储过程。

返回文章页面十大 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

它的工作令人惊讶的好! ! ！

小开

你可以

  1. 将数据库编写到单个文件中，并使用文本编辑器在该文件中搜索 tblEmployes。在 SQLServerManagementStudio(SSMS)中，右键单击数据库并选择 生成脚本。
  2. 使用 SSMS“查看依赖项”，右键单击 tblEmployes 查看哪些其他对象依赖于它
  3. 使用免费的第三方工具(如 Redgate 软件公司 SQL Search)按名称和内容按关键字搜索所有数据库对象。
小开

如果你需要通过名称来查找数据库对象(例如表、列和触发器)——看看 自由 Redgate 软件工具 SQL 搜索，它可以做到这一点——它会在你的整个数据库中搜索任何类型的字符串。

Enter image description here

Enter image description here

对于任何 DBA 或数据库开发人员来说，它都是一个很棒的必备工具——我是否已经提到过它绝对是 自由，可以用于任何用途？

小开

您可以导出您的数据库(如果小)到您的硬盘驱动器/桌面，然后只是做一个字符串搜索通过文本搜索程序或文本编辑器。

小开

您也可以尝试 ApexSQL 搜索-这是一个免费的 短信外接程序类似于 SQL 搜索。

如果您真的只想使用 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]
小开

这将在每个数据库上搜索一个字符串:

declare @search_term varchar(max)
set @search_term = 'something'


select @search_term = 'use ? SET QUOTED_IDENTIFIER ON
select
''[''+db_name()+''].[''+c.name+''].[''+b.name+'']'' as [object],
b.type_desc as [type],
d.obj_def.value(''.'',''varchar(max)'') as [definition]
from (
select distinct
a.id
from sys.syscomments a
where a.[text] like ''%'+@search_term+'%''
) a
inner join sys.all_objects b
on b.[object_id] = a.id
inner join sys.schemas c
on c.[schema_id] = b.[schema_id]
cross apply (
select
[text()] = a1.[text]
from sys.syscomments a1
where a1.id = a.id
order by a1.colid
for xml path(''''), type
) d(obj_def)
where c.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemas
and db_id() not in (1,2,3,4) -- avoid sys databases'


if object_id('tempdb..#textsearch') is not null drop table #textsearch
create table #textsearch
(
[object] varchar(300),
[type] varchar(300),
[definition] varchar(max)
)


insert #textsearch
exec sp_MSforeachdb @search_term


select *
from #textsearch
order by [object]
小开

此代码搜索过程和函数但 不要在表中搜索:)

SELECT name
FROM   sys.all_objects
WHERE  Object_definition(object_id)
LIKE '%text%'
ORDER BY name
小开

我的版本。

我把它命名为“大海捞针”原因显而易见。

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

执行搜索(替换前两个变量的值) :

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 注入选项。
  • 如果你的数据库很大，准备睡一会儿，确保查询不会在几分钟后被关闭。
  • 它将一些值强制转换为字符串，包括 int/bigint/smallints/tinyint。如果您不需要它们，请将它们放在脚本顶部带有时间戳的相同排除列表中。
小开

我可以访问一个数据库，但不能访问存储查询的表。

受到 @ marc _ s 的回答r 的启发，我看了一下 HeidSQL，它是一个 Windows 程序，可以处理 MySQL、 SQLServer 和 PostgreSQL。

我发现它还可以在数据库中搜索字符串。

Click Search, then Find text on Server

Search tool open. Make sure the DB is selected

它将搜索每个表，并告诉您它找到每个表的字符串的次数！

小开

下面是与 用户 l ——“”“——”——-“”“”相同的脚本，但是经过了修正，可以用于区分大小写的 SQL 实例，并进行了一些其他小的改进。

DROP PROCEDURE IF EXISTS dbo.spFind_Text_In_Database
GO


CREATE PROCEDURE dbo.spFind_Text_In_Database
@strText_To_Find NVARCHAR(4000),
@bitExact_Match 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 @bitExact_Match = 1
THEN 'If Exists(Select *
From   ReplaceTableName
Where  Convert(nVarChar(4000), [ReplaceColumnName])
= ''' + @strText_To_Find + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From   ReplaceTableName
Where  Convert(nVarChar(4000), [ReplaceColumnName])
Like ''%' + @strText_To_Find + '%''
)
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
小开

使用 SQLServerManagementStudio(SSMS)可以使用以下方法搜索 SQL 数据库对象，使用 SSMS 对象搜索: 对象资源管理器详细信息或 T-SQL脚本，如下所述:

小开

所有存储过程、视图和函数的内容都存储在表 注释的字段 短信中。所有对象的名称存储在表 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”的所有对象。

小开

下面是如何使用 FMDB 库在 Swift 中搜索数据库。

首先，转到这个链接并将其添加到您的项目中: FMDB。当你做到这一点，然后这里是你如何做到这一点。例如，您有一个名为 Person 的表，您有 firstName 和 Second Name，并且您希望通过名字查找数据，这里有一个代码:

    func loadDataByfirstName(firstName : String, completion: @escaping CompletionHandler){
if isDatabaseOpened {
let query = "select * from Person where firstName like '\(firstName)'"
do {
let results = try database.executeQuery(query, values: [firstName])
while results.next() {
let firstName = results.string(forColumn: "firstName") ?? ""
let lastName = results.string(forColumn: "lastName") ?? ""
let newPerson = Person(firstName: firstName, lastName: lastName)
self.persons.append(newPerson)
}
completion(true)
}catch let err {
completion(false)
print(err.localizedDescription)
}
database.close()
}
}

然后在 ViewController 中编写以下内容，找到您要查找的人员详细信息:

  override func viewWillAppear(_ animated: Bool) {
super.viewWillAppear(animated)
SQLManager.instance.openDatabase { (success) in
if success {
SQLManager.instance.loadDataByfirstName(firstName: "Hardi") { (success) in
if success {
// You have your data Here
}
}
}
}
}
小开

如果我想找到我想要搜索的东西，我会使用下面的方法:

DECLARE @search_string    varchar(200)
SET @search_string = '%myString%'


SELECT DISTINCT
o.name AS Object_Name,
o.type_desc,
m.definition
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like @search_string;

提交答案