如何使用T-SQL在数据库中的所有文本字段中搜索某些子字符串 [英] How to search all text fields in a DB for some substring with T-SQL
问题描述
我有一个庞大的架构，有数百个表和数千个列。我知道一个特定的IP地址存储在此数据库中的多个位置，但是我不确定它存储在哪个表或列中。基本上，我试图在各处找到IP地址存储在数据库中，因此我可以在所有这些位置将其更新为新值。
I have a huge schema, with several hundreds of tables and several thousands of columns. I'd know that a specific IP address is stored in this database in several places, but I'm not sure what table(s) or column(s) it is stored in. Basically, I'm trying to find everywhere that this IP address is stored in the DB so I can update it to a new value in all those places.
这是我第一次破解T-SQL语句以打印出数据库中每个包含子字符串10.15.13的文本列的表和列的名称以及值。
Here's my first crack at a T-SQL statement to print out the table and column name, and the value, for every text column in the database that has the substring 10.15.13 in it.
现在，这有点有用。问题是，当我在Management Studio中执行它时，对sp_executesql的调用实际上将返回每个不返回任何内容的查询的所有空结果（即，该列没有该子字符串的任何记录），并且它填充了结果窗口达到最大值，然后我实际上看不到是否打印了任何内容。
Now, this works, sort of. The problem is, when I execute it in Management Studio, the call to sp_executesql will actually return all the empty results from every query that returns nothing (i.e. the column doesn't have any records with that substring), and it fills the result window to its max, and then I don't actually see if anything was printed.
是否有更好的方法编写此查询？还是可以以其他方式运行它，以便仅向我显示该子字符串所在的表和列？
Is there a better way to write this query? Or can I run it in some different way so that it only shows me the Tables and Columns where this substring exists?
DECLARE
@SchemaName VARCHAR(50),
@TableName VARCHAR(50),
@ColumnName VARCHAR(50);
BEGIN
DECLARE textColumns CURSOR FOR
SELECT s.Name, tab.Name, c.Name
FROM Sys.Columns c, Sys.Types t, Sys.Tables tab, Sys.Schemas s
WHERE s.schema_id = tab.schema_id AND tab.object_id = c.object_id AND c.user_type_id = t.user_type_id
AND t.Name in ('TEXT','NTEXT','VARCHAR','CHAR','NVARCHAR','NCHAR');
OPEN textColumns
FETCH NEXT FROM textColumns
INTO @SchemaName, @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(MAX),
@ParamDef NVARCHAR(MAX),
@result NVARCHAR(MAX);
SET @sql = N'SELECT ' + @ColumnName + ' FROM ' + @SchemaName + '.' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%10.15.13%''';
SET @ParamDef = N'@resultOut NVARCHAR(MAX) OUTPUT';
EXEC sp_executesql @sql, @ParamDef, @resultOut = @result OUTPUT;
PRINT 'Column = ' + @TableName + '.' + @ColumnName + ', Value = ' + @result;
FETCH NEXT FROM textColumns
INTO @SchemaName, @TableName, @ColumnName
END
CLOSE textColumns;
DEALLOCATE textColumns;
END
我希望看到这样的结果，该结果显示在表格/在其中找到子字符串的列，以及该列中的完整值...
I'd like to see results something like this where it shows the table/column that the substring was found in, and the full value in that column...
Column = SomeTable.SomeTextColumn, Value = 'https://10.15.13.210/foo'
Column = SomeTable.SomeOtherColumn, Value = '10.15.13.210'
等。
推荐答案
您将关闭。将此示例与您进行比较：在SQL Server表的所有列中搜索并找到字符串值
You'r close. Compare yours with this example: Searching and finding a string value in all columns in a SQL Server table
上面的链接用于搜索单个表，但是这里是包含所有表的另一个链接：如何在数据库中所有表的所有列中搜索关键字？
The above link is for searching a single table, however here is another link which includes all tables: How to search all columns of all tables in a database for a keyword?
编辑：以防万一链接变坏，这是该链接的解决方案...
EDIT : Just in case the link ever goes bad, here's the solution from that link...
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
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')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
EXEC SearchAllTables '<yourSubstringHere>'
注意：
如代码段中的注释所示，它已使用旧版本的SQL Server进行了测试。这可能不适用于SQL Server 2012。
Note: As the comment suggests in the code snippet, it was tested using older versions of SQL Server. This may not work on SQL Server 2012.
这篇关于如何使用T-SQL在数据库中的所有文本字段中搜索某些子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
