Найти значение в любом месте базы данных
Учитывая #, как узнать, в какой таблице и столбце она может быть найдена?
Мне все равно, если это быстро, просто нужно работать.
Ответы
Ответ 1
Это может помочь вам. - от Нараяны Вьяс. Он ищет все столбцы всех таблиц в данной базе данных. Я использовал его раньше, и он работает.
Это хранимый код из приведенной выше ссылки - единственное изменение, которое я сделал, заключалось в замене таблицы temp на переменную таблицы, поэтому вам не нужно помнить о том, чтобы каждый раз ее бросать.
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 + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results
END
Ответ 2
Если вам нужно выполнить такой поиск только один раз, то вы, вероятно, можете использовать любой из сценариев, уже показанных в других ответах. Но в противном случае, я рекомендую использовать ApexSQL Search для этого. Это бесплатный плагин SSMS, и он действительно сэкономил мне много времени.
Перед запуском любого из сценариев вы должны настроить его в зависимости от типа данных, которые вы хотите найти. Если вы знаете, что ищете столбец datetime, тогда нет необходимости искать по столбцам nvarchar. Это ускорит все запросы выше.
Ответ 3
На основании ответа bnkdev я изменил код Нараяны для поиска всех столбцов, даже числовых.
Он будет работать медленнее, но эта версия на самом деле находит все совпадения не только в текстовых столбцах.
Я не могу поблагодарить этого парня. Спасенные дни поиска вручную!
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
Ответ 4
Это мой независимый подход к этому вопросу, который я использую для своей собственной работы. Он работает в SQL2000 и выше, позволяет использовать подстановочные знаки, фильтрацию столбцов и будет искать большинство обычных типов данных.
Описание псевдокода может быть select * from * where any like 'foo'
--------------------------------------------------------------------------------
-- Search all columns in all tables in a database for a string.
-- Does not search: image, sql_variant or user-defined types.
-- Exact search always for money and smallmoney; no wildcards for matching these.
--------------------------------------------------------------------------------
declare @SearchTerm nvarchar(4000) -- Can be max for SQL2005+
declare @ColumnName sysname
--------------------------------------------------------------------------------
-- SET THESE!
--------------------------------------------------------------------------------
set @SearchTerm = N'foo' -- Term to be searched for, wildcards okay
set @ColumnName = N'' -- Use to restrict the search to certain columns, wildcards okay, null or empty string for all cols
--------------------------------------------------------------------------------
-- END SET
--------------------------------------------------------------------------------
set nocount on
declare @TabCols table (
id int not null primary key identity
, table_schema sysname not null
, table_name sysname not null
, column_name sysname not null
, data_type sysname not null
)
insert into @TabCols (table_schema, table_name, column_name, data_type)
select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
from INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA
and t.TABLE_NAME = c.TABLE_NAME
where 1 = 1
and t.TABLE_TYPE = 'base table'
and c.DATA_TYPE not in ('image', 'sql_variant')
and c.COLUMN_NAME like case when len(@ColumnName) > 0 then @ColumnName else '%' end
order by c.TABLE_NAME, c.ORDINAL_POSITION
declare
@table_schema sysname
, @table_name sysname
, @column_name sysname
, @data_type sysname
, @exists nvarchar(4000) -- Can be max for SQL2005+
, @sql nvarchar(4000) -- Can be max for SQL2005+
, @where nvarchar(4000) -- Can be max for SQL2005+
, @run nvarchar(4000) -- Can be max for SQL2005+
while exists (select null from @TabCols) begin
select top 1
@table_schema = table_schema
, @table_name = table_name
, @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @sql = 'select ''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, * from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @where = ''
from @TabCols
order by id
while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin
select top 1
@column_name = column_name
, @data_type = data_type
from @TabCols
where table_schema = @table_schema
and table_name = @table_name
order by id
-- Special case for money
if @data_type in ('money', 'smallmoney') begin
if isnumeric(@SearchTerm) = 1 begin
set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards
end
end
-- Special case for xml
else if @data_type = 'xml' begin
set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + ''''
end
-- Special case for date
else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin
set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + ''''
end
-- Search all other types
else begin
set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + ''''
end
delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name
end
set @run = 'if exists(' + @exists + @where + ') begin ' + @sql + @where + ' print ''' + @table_name + ''' end'
print @run
exec sp_executesql @run
end
set nocount off
Я не помещаю его в форму proc, так как я не хочу поддерживать его через сотни БД, и это действительно для ad-hoc-работы в любом случае. Пожалуйста, не стесняйтесь комментировать исправления ошибок.
Ответ 5
Я оптимизировал ответ Allain Lalonde (fooobar.com/questions/33259/...). Числовые значения по-прежнему поддерживаются. Должно быть примерно в 4-5 раз быстрее (1:03 против 4:30), протестировано на рабочем столе с базой данных 7 ГБ. http://developer.azurewebsites.net/2015/01/mssql-searchalltables/
IF OBJECT_ID ('dbo.SearchAllTables', 'P') IS NOT NULL
DROP PROCEDURE dbo.SearchAllTables;
GO
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
-- Customized and modified: 2014-01-21
-- Tested on: SQL Server 2008 R2
DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256)
DECLARE @ColumnName nvarchar(128)
DECLARE @DataType nvarchar(128)
DECLARE @SearchStr2 nvarchar(110)
DECLARE @SearchDecimal decimal(38,19)
DECLARE @Query nvarchar(4000)
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')
SET @SearchDecimal = CASE WHEN ISNUMERIC(@SearchStr) = 1 THEN CONVERT(decimal(38,19), @SearchStr) ELSE NULL END
PRINT '@SearchStr2: ' + @SearchStr2
PRINT '@SearchDecimal: ' + CAST(@SearchDecimal AS nvarchar)
SET @TableName = ''
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))
DATA_TYPE
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', 'bigint', 'tinyint', 'numeric', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
SET @DataType =
(
SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND QUOTENAME(COLUMN_NAME) = @ColumnName
)
PRINT @TableName + '.' + @ColumnName + ' (' + @DataType + ')'
IF @ColumnName IS NOT NULL
BEGIN
IF @DataType IN ('int', 'bigint', 'tinyint', 'numeric', 'decimal')
BEGIN
IF @SearchDecimal IS NOT NULL
BEGIN
SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CAST(' + @ColumnName + ' AS nvarchar(110)), 3630) ' +
'FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' = ' + CAST(@SearchDecimal AS nvarchar)
PRINT ' ' + @Query
INSERT INTO @Results
EXEC (@Query)
END
END
ELSE
BEGIN
SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) ' +
'FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
PRINT ' ' + @Query
INSERT INTO @Results
EXEC (@Query)
END
END
END
END
SELECT ColumnName, ColumnValue FROM @Results
END
Ответ 6
Спасибо за действительно полезный script.
Возможно, вам потребуется добавить следующую модификацию кода, если в ваших таблицах есть неконвертируемые поля:
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 NOT IN ('text', 'image', 'ntext')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
Крис
Ответ 7
Это мой способ решить этот вопрос. Протестировано на SQLServer2008R2
CREATE PROC SearchAllTables
@SearchStr nvarchar(100)
AS
BEGIN
DECLARE @dml nvarchar(max) = N''
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE dbo.#Results
CREATE TABLE dbo.#Results
([tablename] nvarchar(100),
[ColumnName] nvarchar(100),
[Value] nvarchar(max))
SELECT @dml += ' SELECT ''' + s.name + '.' + t.name + ''' AS [tablename], ''' +
c.name + ''' AS [ColumnName], CAST(' + QUOTENAME(c.name) +
' AS nvarchar(max)) AS [Value] FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +
' (NOLOCK) WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ' + '''%' + @SearchStr + '%'''
FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c .user_type_id = ty .user_type_id
WHERE t.is_ms_shipped = 0 AND ty.name NOT IN ('timestamp', 'image', 'sql_variant')
INSERT dbo.#Results
EXEC sp_executesql @dml
SELECT *
FROM dbo.#Results
END
Ответ 8
У меня есть решение от того, что я продолжал совершенствоваться. Также выполняет поиск в столбцах 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/
Ответ 9
Здесь очень сладкое и маленькое решение:
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';
Ответ 10
Я написал один раз инструмент для себя, чтобы сделать именно эту вещь:
a7 SqlTools
Он бесплатный и с открытым исходным кодом:
ссылка github
Ответ 11
Вероятно, вам придется написать короткий script запрос метаданных (в данном случае список таблиц/столбцов) из базы данных и выпустить ряд операторов выбора, которые ищут значение.
Ответ 12
Другой способ использования JOIN и CURSOR:
USE My_Database;
-- Store results in a local temp table so that. I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
create table #tmp (
tbl nvarchar(max),
col nvarchar(max),
val nvarchar(max)
);
declare @tbl nvarchar(max);
declare @col nvarchar(max);
declare @q nvarchar(max);
declare @search nvarchar(max) = 'my search key';
-- Create a cursor on all columns in the database
declare c cursor for
SELECT tbls.TABLE_NAME, cols.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES AS tbls
JOIN INFORMATION_SCHEMA.COLUMNS AS cols
ON tbls.TABLE_NAME = cols.TABLE_NAME
-- For each table and column pair, see if the search value exists.
open c
fetch next from c into @tbl, @col
while @@FETCH_STATUS = 0
begin
-- Look for the search key in current table column and if found add it to the results.
SET @q = 'INSERT INTO #tmp SELECT ''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%'''
EXEC SP_EXECUTESQL @q
fetch next from c into @tbl, @col
end
close c
deallocate c
-- Get results
select * from #tmp
-- Remove local temp table.
drop table #tmp
Ответ 13
Возможно, вам понадобится создать инвертированный индекс для вашей базы данных. Он уверен, что будет довольно быстро.
Ответ 14
-- 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
Ответ 15
Для целей разработки вы можете просто экспортировать необходимые данные таблиц в один HTML-код и выполнить прямой поиск по ним.
Ответ 16
Предположим, если вы хотите получить всю таблицу с именем, имя столбца, содержащее время входа в базу данных, MyDatabase ниже приведен пример кода
use MyDatabase
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%logintime%'
ORDER BY schema_name, table_name;
Ответ 17
Смотрите мой пост для поиска значения во всех базах данных, таблицах и столбцах.
Найти значение и показывает мне базу данных, таблицу, столбец, первичный ключ найденного значения.
Надеюсь, это поможет.
