반응형
데이터베이스에서 값 찾기
번호를 지정하면 어떤 테이블과 컬럼에서 찾을 수 있는지 어떻게 알 수 있습니까?
빠르든 말든 그냥 작동만 하면 돼.
이게 도움이 될 거야 - 나라야나 바야스에서지정된 데이터베이스 내의 모든 테이블의 모든 열을 검색합니다.전에 써봤는데 효과가 있어요.
위 링크의 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'
이러한 검색을 한 번만 실행해야 하는 경우 다른 응답에 이미 표시된 스크립트 중 하나를 사용할 수 있습니다.그렇지 않으면 ApexSQL Search를 사용할 것을 권장합니다.이것은 무료 SSMS 애드인으로 많은 시간을 절약할 수 있었습니다.
스크립트를 실행하기 전에 검색할 데이터 유형에 따라 스크립트를 커스터마이즈해야 합니다.datetime 열을 검색하는 경우 nvarchar 열을 검색할 필요가 없습니다.그러면 위의 모든 쿼리 속도가 빨라집니다.
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
이것은 내가 내 일을 위해 사용하는 이 질문에 대한 나의 독립적인 견해이다.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
수백 개의 DB에서 유지보수를 하고 싶지 않고 임시 작업용이기 때문에 proc 형식으로 작성하지 않습니다.버그 수정에 대해 자유롭게 코멘트해 주세요.
Alain Lalonde 답변(https://stackoverflow.com/a/436676/412368)을 최적화했습니다.숫자 값은 계속 지원됩니다.7GB 데이터베이스를 탑재한 데스크톱에서 테스트한 속도(1:03 대 4:30)는 약 4-5배 빨라집니다.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
저는 아까부터 계속 개선해 온 해결책이 있습니다.또한 지시된 경우 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 2008R2에서 테스트 완료
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
정말 유용한 대본 감사합니다.
테이블에 변환 불가능한 필드가 있는 경우 다음 변경을 코드에 추가해야 할 수 있습니다.
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
)
크리스
여기 매우 작고 달콤한 솔루션이 있습니다.
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';
phpMyAdmin이 설치되어 있는 경우 검색 기능을 사용합니다.
데이터베이스를 선택합니다.
테이블이 아닌 DataBase를 선택했는지 확인하십시오. 그렇지 않으면 완전히 다른 검색 대화 상자가 표시됩니다.
- [ Search ]탭을 클릭합니다.
- List item 원하는 검색어를 선택합니다.
- 검색할 테이블 선택
JOIN과 커서를 사용하는 다른 방법:
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
데이터베이스에 대해 반전 인덱스를 작성해야 할 수 있습니다.꽤 빠르다고 확신해요.
-- 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
개발 목적으로 필요한 테이블 데이터를 단일 HTML로 내보내고 직접 검색할 수 있습니다.
데이터베이스 MyDatabase에 logintime이 포함된 열 이름을 가진 모든 테이블을 가져오려면 아래의 코드 샘플이 필요합니다.
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;
숫자 값 = 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
이미 실행 가능한 답변이 많이 있습니다.제가 쓴 기능 중 추가 기능이 있는 것을 공유하고자 합니다.
--=======================================================================
-- 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;
*********************************************************************/
지금까지 발견된 솔루션 중 가장 우수하고 범용적인 솔루션은 검색 대상의 grep에 db 덤프를 연결하는 것입니다.
예: Mysql의 경우:
mysqldump -pPASSWORD database | grep 'search phrase'
또는 결과가 너무 많으면 파일로 출력할 수 있습니다.
mysqldump -pPASSWORD database | grep 'search phrase' > results.txt
언급URL : https://stackoverflow.com/questions/436351/find-a-value-anywhere-in-a-database
반응형
'programing' 카테고리의 다른 글
| MemoryStream에서 문자열을 가져오려면 어떻게 해야 합니까? (0) | 2023.04.07 |
|---|---|
| "복원할 백업 세트가 선택되지 않았습니다" SQL Server 2012 (0) | 2023.04.07 |
| SQL Output 절에서 삽입되지 않은 열을 반환할 수 있습니까? (0) | 2023.04.07 |
| 시간을 클리어 또는 정지하는 방법angularjs 간격? (0) | 2023.04.02 |
| 단위 테스트 약속 기반 코드(Angularjs) (0) | 2023.04.02 |