给定一个数字，我如何发现在什么表和列中可以找到它?
我不在乎速度快不快，只要管用就行。
当前回答
已经有很多可行的答案了。我只是想分享一个我写的有额外功能的。
--=======================================================================
-- MSSQL Unified Search
-- Minimum compatibility level = 130 (SQL Server 2016)
-- NOTE: The minimum compatibility level is required by the built-in STRING_SPLIT() function.
-- However, you can create the STRING_SPLIT() function at the bottom of this script for
-- lower versions of MSSQL Server.
--
-- Usage:
-- Set the parameters below and execute this script.
--
/************************ Enter Parameters Here ************************/
/**/
/**/ DECLARE @SearchString VARCHAR(1000) = 'string to search for'; -- Accepts SQL wilcards
/**/
/**/ DECLARE @IncludeUserTables BIT = 1;
/**/ DECLARE @IncludeViews BIT = 0;
/**/ DECLARE @IncludeStoredProcedures BIT = 0;
/**/ DECLARE @IncludeFunctions BIT = 0;
/**/ DECLARE @IncludeTriggers BIT = 0;
/**/
/**/ DECLARE @DebugMode BIT = 0;
/**/ DECLARE @ExcludeColumnTypes NVARCHAR(500) = 'text, ntext, char, nchar, timestamp, bigint, tinyint, smallint, bit, date, time, smalldatetime, datetime, datetime2, real, money, float, decimal, binary, varbinary, image'; -- Comma delimited list
/**/
/***********************************************************************/
SET NOCOUNT ON;
SET @SearchString = QUOTENAME(@SearchString,'''');
DECLARE @Results TABLE ([ObjectType] NVARCHAR(200), [ObjectName] NVARCHAR(200), [ColumnName] NVARCHAR(400), [Value] NVARCHAR(MAX), [SelectStatement] NVARCHAR(1000));
DECLARE @ExcludeColTypes TABLE (system_type_id INT);
INSERT INTO @ExcludeColTypes ([system_type_id])
SELECT [system_type_id]
FROM sys.types WHERE
[name] IN (
SELECT LTRIM(RTRIM([value])) FROM STRING_SPLIT(@ExcludeColumnTypes,',')
);
DECLARE @ObjectType NVARCHAR(200);
DECLARE @ObjectName NVARCHAR(200);
DECLARE @Value NVARCHAR(MAX);
DECLARE @SelectStatement NVARCHAR(1000);
DECLARE @Query NVARCHAR(4000);
/********************* Table Objects *********************/
IF (@IncludeUserTables = 1)
BEGIN
DECLARE @TableObjectId INT = (SELECT MIN([object_id]) FROM sys.tables);
DECLARE @ColumnId INT;
WHILE @TableObjectId IS NOT NULL
BEGIN
SELECT @ObjectType = 'USER TABLE';
SELECT @ObjectName = '[' + SCHEMA_NAME([schema_id]) + '].[' + OBJECT_NAME(@TableObjectId) + ']' FROM sys.tables WHERE [object_id] = @TableObjectId;
SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId);
WHILE @ColumnId IS NOT NULL
BEGIN
SELECT @Value = '[' + [name] +']' FROM sys.columns WHERE [object_id] = @TableObjectId AND column_id = @ColumnId;
SET @SelectStatement = 'SELECT * FROM ' + @ObjectName + ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';
SET @Query = 'SELECT '
+ QUOTENAME(@ObjectType, '''')
+ ', ' + QUOTENAME(@ObjectName, '''')
+ ', ' + QUOTENAME(@Value, '''')
+ ', ' + @Value
+ ', ''' + REPLACE(@SelectStatement,'''','''''') + ''''
+ ' FROM ' + @ObjectName
+ ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';
IF @DebugMode = 0
BEGIN
INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
PRINT 'Select Statement: ' + @SelectStatement;
PRINT 'Query: ' + @Query;
END;
SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId AND [column_id] > @ColumnId);
END;
SET @TableObjectId = (SELECT MIN([object_id]) FROM sys.tables WHERE [object_id] > @TableObjectId);
END;
END;
/********************* Objects Other than Tables *********************/
SET @Query = 'SELECT ' +
'ObjectType = CASE ' +
'WHEN b.[type] = ''V'' THEN ''VIEW'' ' +
'WHEN b.[type] = ''P'' THEN ''STORED PROCEDURE'' ' +
'WHEN b.[type] = ''FN'' THEN ''SCALAR-VALUED FUNCTION'' ' +
'WHEN b.[type] = ''IF'' THEN ''TABLE-VALUED FUNCTION'' ' +
'WHEN b.[type] = ''TR'' THEN ''TRIGGER'' ' +
'END ' +
',[ObjectName] = ''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'' ' +
',[ColumnName] = NULL ' +
',[Value] = a.[definition] ' +
',[SelectStatement] = ''SP_HELPTEXT '' + QUOTENAME(''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'','''''''') + '';'' ' +
'FROM [sys].[sql_modules] a ' +
'JOIN [sys].[objects] b ON a.[object_id] = b.[object_id] ' +
'WHERE ' +
'( ' +
' a.[definition] LIKE ' + @SearchString +
') ' +
'AND ' +
'( ' +
' ( ' +
CAST(@IncludeViews AS VARCHAR(1)) + ' = 1 ' +
' AND ' +
' b.[type] IN (''V'') ' +
' ) ' +
' OR ' +
' ( ' +
CAST(@IncludeStoredProcedures AS VARCHAR(1)) + ' = 1 ' +
' AND ' +
' b.[type] IN (''P'') ' +
' ) ' +
' OR ' +
' ( ' +
CAST(@IncludeFunctions AS VARCHAR(1)) + ' = 1 ' +
' AND ' +
' b.[type] IN (''FN'',''IF'') ' +
' ) ' +
' OR ' +
' ( ' +
CAST(@IncludeTriggers AS VARCHAR(1)) + ' = 1 ' +
' AND ' +
' b.[type] IN (''TR'') ' +
' ) ' +
'); ';
IF @DebugMode = 0
BEGIN
INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
PRINT 'Select Statement: ' + @SelectStatement;
PRINT 'Query: ' + @Query;
END;
IF @DebugMode = 0
BEGIN
SELECT
[ObjectType]
,[ObjectName]
,[ColumnName]
,[Value]
,[Count] = CASE
WHEN [ObjectType] IN ('USER TABLE') THEN COUNT(1)
ELSE NULL
END
,[SelectStatement]
FROM @Results
GROUP BY [ObjectType], [ObjectName], [ColumnName], [Value], [SelectStatement]
ORDER BY [Value];
END;
/********************** STRING_SPLIT() FUNCTION **********************
CREATE FUNCTION STRING_SPLIT (
@Expression nvarchar(4000)
,@Delimiter nvarchar(100)
)
RETURNS @Ret TABLE ([value] NVARCHAR(4000))
AS
BEGIN
DECLARE @Start INT = 0, @End INT, @Length INT;
SELECT @End = CHARINDEX(@Delimiter,@Expression), @Length = @End - @Start;
IF @End <= 0
BEGIN
INSERT INTO @Ret ([value]) VALUES (@Expression);
END
ELSE
BEGIN
WHILE @Length >= 0
BEGIN
INSERT INTO @Ret ([value])
SELECT ltrim(rtrim(substring(@Expression,@Start,@Length)));
SELECT @Start = @End + LEN(@Delimiter)
SELECT @End = CHARINDEX(@Delimiter,@Expression,@Start)
IF @End < 1
SELECT @End = LEN(@Expression) + 1;
SELECT @Length = @End - @Start;
END;
END;
RETURN;
END;
*********************************************************************/
其他回答
在这里，非常甜蜜和小的解决方案:
1) create a store procedure:
create procedure get_table
@find_str varchar(50)
as
begin
declare @col_name varchar(500), @tab_name varchar(500);
declare @find_tab TABLE(table_name varchar(100), column_name varchar(100));
DECLARE tab_col cursor for
select C.name as 'col_name', T.name as tab_name
from sys.tables as T
left outer join sys.columns as C on C.object_id=T.object_id
left outer join sys.types as TP on C.system_type_id=TP.system_type_id
where type='U'
and TP.name in('text','ntext','varchar','char','nvarchar','nchar');
open tab_col
fetch next from tab_col into @col_name, @tab_name
while @@FETCH_STATUS = 0
begin
insert into @find_tab
exec('select ''' + @tab_name + ''',''' + @col_name + ''' from ' + @tab_name +
' where ' + @col_name + '=''' + @find_str + ''' group by ' +
@col_name + ' having count(*)>0');
fetch next from tab_col into @col_name, @tab_name;
end
CLOSE tab_col;
DEALLOCATE tab_col;
select table_name, column_name from @find_tab;
end
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
2) call procedure by calling store procedure:
exec get_table 'serach_string';
-- exec pSearchAllTables 'M54*'
ALTER PROC pSearchAllTables (@SearchStr NVARCHAR(100))
AS
BEGIN
-- A procedure to search all tables in a database for a value
-- Note: Use * or % for wildcard
DECLARE
@Results TABLE([Schema.Table.ColumnName] NVARCHAR(370), ColumnValue NVARCHAR(3630))
SET NOCOUNT ON
DECLARE
@TableName NVARCHAR(256) = ''
, @ColumnName NVARCHAR(128)
, @SearchStr2 NVARCHAR(110) = QUOTENAME(REPLACE(@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
[Schema.Table.ColumnName]
, ColumnValue
FROM @Results
GROUP BY
[Schema.Table.ColumnName]
, ColumnValue
END
如果您只需要运行一次这样的搜索，那么您可能可以使用其他答案中已经显示的任何脚本。但除此之外，我建议使用ApexSQL搜索。这是一个免费的SSMS插件，它真的为我节省了很多时间。
在运行任何脚本之前，您都应该根据要搜索的数据类型自定义脚本。如果你知道你正在搜索datetime列，那么就没有必要通过nvarchar列进行搜索。这将加快上面所有查询的速度。
我正在寻找一个数值= 6.84 -使用这里的其他答案，我能够将我的搜索限制在这个范围内
Declare @sourceTable Table(id INT NOT NULL IDENTITY PRIMARY KEY, table_name varchar(1000), column_name varchar(1000))
Declare @resultsTable Table(id INT NOT NULL IDENTITY PRIMARY KEY, table_name varchar(1000))
Insert into @sourceTable(table_name, column_name)
select schema_name(t.schema_id) + '.' + t.name as[table], c.name as column_name
from sys.columns c
join sys.tables t
on t.object_id = c.object_id
where type_name(user_type_id) in ('decimal', 'numeric', 'smallmoney', 'money', 'float', 'real')
order by[table], c.column_id;
DECLARE db_cursor CURSOR FOR
Select table_name, column_name from @sourceTable
DECLARE @mytablename VARCHAR(1000);
DECLARE @mycolumnname VARCHAR(1000);
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @mytablename, @mycolumnname
WHILE @ @FETCH_STATUS = 0
BEGIN
Insert into @ResultsTable(table_name)
EXEC('SELECT ''' + @mytablename + '.' + @mycolumnname + ''' FROM ' + @mytablename + ' (NOLOCK) ' +
' WHERE ' + @mycolumnname + '=6.84')
FETCH NEXT FROM db_cursor INTO @mytablename, @mycolumnname
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
Select Distinct(table_name) from @ResultsTable
我之前有一个解决方案，我一直在改进。如果被告知在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/
推荐文章
- 确定记录是否存在的最快方法
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 外键约束可能导致循环或多条级联路径?
- 如何选择每一行的列值不是独特的
- nvarchar(max)非文本
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- 修改列，添加默认约束
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- 如何检查SQL Server文本列是否为空?
- 如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?
