반응형
데이터베이스 내의 테이블 열에서 하나의 값을 검색합니다.
char(64)하나의 MS SQL Server 데이터베이스 내의 테이블 열 내에서 하나의 값 (내 경우에는 유형의 UID)을 검색하는 방법이 있습니까?
나는 테이블이 어떻게 서로 연결되어야하는지 전혀 모르고 거대한 데이터베이스 앞에 앉아있다. 이를 확인하기 위해 모든 테이블을 나열하고 모든 행에 특정 값을 포함하는 열을 나열하고 싶습니다. 가능합니까?
한 가지 방법은 전체 데이터베이스를 텍스트 파일로 덤프하고 텍스트 편집기를 사용하여 값을 검색하는 것입니다.하지만 데이터베이스가 너무 크면 이것은 순수한 고통입니다.
데이터베이스에있는 모든 테이블의 모든 열에서 키워드를 검색하는 방법은 무엇입니까?
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
편집 : 링크 부패의 경우 실제 T-SQL은 다음과 같습니다.
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
아래에 언급 된 모든 타사 도구는 100 % 무료입니다.
테이블에서 개체와 데이터를 모두 검색 하는 데 성공적으로 ApexSQL 검색 을 사용 했습니다. 관계 다이어그램 등과 같은 몇 가지 다른 기능과 함께 제공됩니다.
큰 (40GB TFS 데이터베이스) 데이터베이스에서는 약간 느 렸습니다.
이 외에도 텍스트 검색과 직접 관련이 없지만 매우 유용한 많은 다른 기능을 제공하는 SSMS 도구 팩 도 있습니다 .
' record number' 가 표시되지 않았기 때문에 코드를 확장했으며 수정 해야합니다.
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
출처 : http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/
나는 내가 계속 개선하는 얼마 전의 해결책을 가지고 있습니다. 또한 그렇게하도록 지시 된 경우 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
https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58 에서 상당히 강력한 솔루션을 찾았으며 지적 할 가치가 있다고 생각했습니다. varchar, char, nvarchar, nchar, text 유형의 열을 검색합니다. 그것은 훌륭하게 작동하며 특정 테이블 검색과 여러 검색어를 지원합니다.
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
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
반응형
'Programing' 카테고리의 다른 글
| Python의 다른 클래스 내부에 클래스를 정의하면 이점이 있습니까? (0) | 2020.09.03 |
|---|---|
| 여러 테이블에서 기본 키를 참조하는 외래 키? (0) | 2020.09.03 |
| SSD 드라이브 및 Visual Studio IDE. (0) | 2020.09.03 |
| reflect를 사용하여 struct 필드의 값을 어떻게 설정합니까? (0) | 2020.09.03 |
| 자체 템플릿으로 기본 제공 암호 재설정 / 변경보기를 사용하는 방법 (0) | 2020.09.03 |