SQL Server：在所有数据库中搜索字符串，并列出所有数据库、表和相应的列。
SQL Server：在所有数据库中搜索字符串，并列出所有数据库、表和相应的列。
我想知道在SQL Server 2012中是否有一种方法可以查看所有数据库和相应的表，其中包含特定的字符串。\n我能够通过使用下面的存储过程在特定数据库的所有表中搜索字符串，这是我在网上找到的。但是我想在所有数据库中搜索它。\n
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + 'WITH (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
\n当我执行下面的查询时，它列出了所有的表以及列名。\n
exec SearchAllTables "B2"
\n输出：\n
ColumnName ColumnValue [dbo].[msm_temp].[molecule_nm] B2 [dbo].[msm_temp1].[molecule_nm] B2 [dbo].[msm_temp2].[molecule_nm] B2 [dbo].[msm_temp3].[molecule_nm] B2 [dbo].[msm_temp4].[molecule_nm] B2
\n我该如何解决这个问题？
SQL Server : 在所有数据库中搜索字符串并列出所有数据库、表和相应列的解决方法
在SQL Server中，如果您想要在所有数据库中搜索特定的字符串，并列出所有相关的数据库、表和列，可以使用以下存储过程：
CREATE PROCEDURE dbo.SearchAllDatabases
@SearchTerm NVARCHAR(255) = NULL
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF @SearchTerm IS NULL OR @SearchTerm NOT LIKE N'%[^%^_]%'
BEGIN
RAISERROR(N'Please enter a valid search term.', 11, 1);
RETURN;
END
CREATE TABLE #results
(
[database] SYSNAME,
[schema] SYSNAME,
[table] SYSNAME,
[column] SYSNAME,
ExampleValue NVARCHAR(1000)
);
DECLARE @DynamicSQL NVARCHAR(MAX) = N'';
DECLARE @DatabaseName SYSNAME = '';
SELECT @DynamicSQL = @DynamicSQL + N'
EXEC ' + QUOTENAME(name) + '.sys.sp_executesql
N''SELECT @DatabaseName = DB_NAME(),
@SchemaName = N'''' + s.name + N'''',
@TableName = N'''' + t.name + N'''',
@ColumnName = N'''' + c.name + N'''',
@ExampleValue = LEFT('''' + QUOTENAME(c.name) + '''', 1000)
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
WHERE ' + QUOTENAME(c.name) + N' LIKE @SearchTerm;''
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239)
AND c.max_length >= LEN(@SearchTerm);'
FROM sys.databases
WHERE database_id > 4 -- 非系统数据库
AND [state] = 0 -- 在线
AND user_access = 0; -- 多用户
SET @DynamicSQL = N'DECLARE @SearchTerm NVARCHAR(255),
@SearchTermEscaped NVARCHAR(MAX);
SELECT @SearchTerm = NCHAR(39),
@SearchTermEscaped = N''DECLARE @SearchTerm VARCHAR(255) = '''''' + REPLACE(@SearchTerm, NCHAR(39), NCHAR(39) + NCHAR(39)) + '''''';'';
SELECT @DynamicSQL = @DynamicSQL + CHAR(10) + N''
SELECT TOP(1)
[database] = @DatabaseName,
[schema] = @SchemaName,
[table] = @TableName,
[column] = @ColumnName,
ExampleValue = @ExampleValue
FROM ' + QUOTENAME(@DatabaseName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '
WHERE ' + QUOTENAME(@ColumnName) + N' LIKE @SearchTerm;''
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239)
AND c.max_length >= LEN(@SearchTermEscaped);
PRINT @DynamicSQL;
EXEC sys.sp_executesql @DynamicSQL,
N'@SearchTerm NVARCHAR(255)',
@SearchTerm = @SearchTerm;
INSERT #Results
(
[database],
[schema],
[table],
[column],
ExampleValue
)
EXEC [master].sys.sp_executesql @DynamicSQL,
N'@SearchTerm NVARCHAR(MAX), @DatabaseName SYSNAME',
@SearchTerm = @SearchTerm,
@DatabaseName = @DatabaseName;
SELECT 'Searched for' = @SearchTerm;
SELECT [database],[schema],[table],[column],ExampleValue
FROM #Results
ORDER BY [database],[schema],[table],[column];
END
这个存储过程可以帮助您在所有数据库中搜索字符串。但是，如果您的数据库中有大量数据，执行时间可能会很长。
以下是一些关于这个存储过程的问题和解答：
评论1：这个查询工作得很慢。有没有更优化的解决方案？
回复1：我不确定，但我尝试优化这个查询。因为我也需要在我的数据库中进行搜索。如果这个对你有用并且你喜欢的话，请接受它。
评论2：这个脚本还报错：SQL Server Database Error: Argument data type text is invalid for argument 1 of left function.
回复2：我期待有人能帮助我们升级这个查询或者提供更好的查询。但是目前这是我找到的最好的查询。
评论3：在我的情况下，这个查询不起作用。它没有报错，但是返回了一个空结果。我需要在特定的数据库中运行脚本并创建存储过程吗？
回复3：抱歉回答晚了，您应该在一个数据库中创建存储过程，并在同一个数据库中运行。但它会在您的服务器上显示所有数据库中的数据。
希望这能帮到您。