在数据库中任何表的任何列中搜索一个值

是否有一种方法可以在 MS SQL Server 数据库中的任何表的任何列中搜索一个值(在我的例子中是 char(64)类型的 UID) ？

我坐在一个巨大的数据库前，完全不知道这些表是如何连接在一起的。为了找到答案，我想列出所有的表和那些在任何行中包含某个值的列。这可能吗？

一种方法是将整个数据库转储到一个文本文件中，然后使用任何文本编辑器来搜索值——但是如果数据库太大，这将是非常痛苦的。

215048 次浏览
小开
最佳答案

How to search all columns of all tables in a database for a keyword?

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

EDIT: Here's the actual T-SQL, in case of link rot: 

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

There is a nice script available on http://www.reddyss.com/SQLDownloads.aspx

To be able to use it on any database you can create it like in: http://nickstips.wordpress.com/2010/10/18/sql-making-a-stored-procedure-available-to-all-databases/

Not sure if there is other way.

To use it then use something like this:

use name_of_database


EXEC spUtil_SearchText 'value_searched', 0, 0
小开

I expanded the code, because it's not told me the 'record number', and I must to refind it.

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


-- Copyright @ 2012 Gyula Kulifai. All rights reserved.
-- Extended By: Gyula Kulifai
-- Purpose: To put key values, to exactly determine the position of search
-- Resources: Anatoly Lubarsky
-- Date extension: 19th October 2012 12:24 GMT
-- Tested on: SQL Server 10.0.5500 (SQL Server 2008 SP3)


CREATE TABLE #Results (TableName nvarchar(370), KeyValues nvarchar(3630), ColumnName nvarchar(370), ColumnValue nvarchar(3630))


SET NOCOUNT ON


DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
,@TableShortName nvarchar(256)
,@TableKeys nvarchar(512)
,@SQL nvarchar(3830)


SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')


WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''


-- Scan Tables
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
)
Set @TableShortName=PARSENAME(@TableName, 1)
-- print @TableName + ';' + @TableShortName +'!' -- *** DEBUG LINE ***


-- LOOK Key Fields, Set Key Columns
SET @TableKeys=''
SELECT @TableKeys = @TableKeys + '''' + QUOTENAME([name]) + ': '' + CONVERT(nvarchar(250),' + [name] + ') + ''' + ',' + ''' + '
FROM syscolumns
WHERE [id] IN (
SELECT [id]
FROM sysobjects
WHERE [name] = @TableShortName)
AND colid IN (
SELECT SIK.colid
FROM sysindexkeys SIK
JOIN sysobjects SO ON
SIK.[id] = SO.[id]
WHERE
SIK.indid = 1
AND SO.[name] = @TableShortName)
If @TableKeys<>''
SET @TableKeys=SUBSTRING(@TableKeys,1,Len(@TableKeys)-8)
-- Print @TableName + ';' + @TableKeys + '!' -- *** DEBUG LINE ***


-- Search in Columns
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
) -- Set ColumnName


IF @ColumnName IS NOT NULL
BEGIN
SET @SQL='
SELECT
''' + @TableName + '''
,'+@TableKeys+'
,''' + @ColumnName + '''
,LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
--Print @SQL -- *** DEBUG LINE ***
INSERT INTO #Results
Exec (@SQL)
END -- IF ColumnName
END -- While Table and Column
END --While Table


SELECT TableName, KeyValues, ColumnName, ColumnValue FROM #Results
END
小开

All third=party tools mentioned below are 100% free.

I’ve used ApexSQL Search with good success for searching both objects and data in tables. It comes with several other features such as relationship diagrams and such…

I was a bit slow on large (40GB TFS Database) databases though…

enter image description here

Apart from this there is also SSMS Tools pack that offers a lot of other features that are quite useful even though these are not directly related to searching text.

小开

Source: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

I have a solution from a while ago that I kept improving. Also searches within XML columns if told to do so, or searches integer values if providing a integer only string.

/* 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


SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType
小开

I found a fairly robust solution at https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58 , which I thought was worth pointing out. It searches columns of these types: varchar, char, nvarchar, nchar, text. It works great and supports specific table-searching as well as multiple search-terms.

小开

After trying @regeter's solution and seeing it didn't solve my problem when I was searching for a foreign/primary key to see all tables/columns where it exists, it didn't work. After reading how it failed for another who tried to use a unique identifier, I have made the modifications and here is the updated result: (works with both int, and guids... you will see how to easily extend)

CREATE PROC [dbo].[SearchAllTables_Like]
(
@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 ('guid', 'int', '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

提交答案