给定一个数字，我如何发现在什么表和列中可以找到它?
我不在乎速度快不快，只要管用就行。
当前回答
我之前有一个解决方案，我一直在改进。如果被告知在XML列中搜索，或者如果只提供整数字符串则搜索整数值。
/* 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
来源: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/
其他回答
这可能对你有帮助。——来自纳拉亚纳·维亚斯。它搜索给定数据库中所有表的所有列。我以前用过，效果很好。
这是上面链接中的Stored Proc -我所做的唯一更改是将临时表替换为表变量，这样您就不必每次都记得删除它。
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
DECLARE @Results TABLE(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 +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results
END
执行存储过程。
EXEC SearchAllTables 'YourStringHere'
根据bnkdev的回答，我修改了Narayana的代码，以搜索所有列，甚至是数字列。
它会运行得慢一些，但这个版本实际上会找到所有匹配项，而不仅仅是文本列中的匹配项。
我对他感激不尽。省了我好几天徒手找!
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 QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CONVERT(varchar(max), ' + @ColumnName + '), 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE CONVERT(varchar(max), ' + @ColumnName + ') LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
出于开发目的，您可以将所需的表数据导出到单个HTML中，并对其进行直接搜索。
我之前有一个解决方案，我一直在改进。如果被告知在XML列中搜索，或者如果只提供整数字符串则搜索整数值。
/* 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
来源: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/
如果您只需要运行一次这样的搜索，那么您可能可以使用其他答案中已经显示的任何脚本。但除此之外，我建议使用ApexSQL搜索。这是一个免费的SSMS插件，它真的为我节省了很多时间。
在运行任何脚本之前，您都应该根据要搜索的数据类型自定义脚本。如果你知道你正在搜索datetime列，那么就没有必要通过nvarchar列进行搜索。这将加快上面所有查询的速度。
推荐文章
- 等价的限制和偏移SQL Server?
- 如何从SQL Server中的字符串中剥离所有非字母字符?
- 为什么我不能在DELETE语句中使用别名?
- 多语句表值函数vs内联表值函数
- NOLOCK提示在SELECT语句中的作用
- SQL OVER()子句-它什么时候有用，为什么有用?
- 检查SQL Server登录是否已经存在
- 我如何使用ROW_NUMBER()?
- SQL或者TSQL是图灵完备的吗?
- 如何检查表上持有哪些锁
- SQL Server数据库备份恢复到低版本
- SQL Server:过滤sp_who2的输出
- 在SQL Server上使用varchar(MAX) vs TEXT
- Visual Studio: ContextSwitchDeadlock
- Sql Server字符串到日期的转换
