복붙노트

[SQL] DB를 모든 테이블, 행과 열에서 문자열 검색

SQL

DB를 모든 테이블, 행과 열에서 문자열 검색

나는 큰 데이터베이스에서 길을 잃었 그리고 난 내가 할 데이터의 출처 찾을 수 없습니다입니다. 나는 데이터베이스의 모든 테이블, 행과 열에서 문자열을 검색하는 SQL 서버 2005 가능한 경우 궁금 해서요?

이 가능하며 어떻게하면합니까의 사람은 아이디어가있다?

해결법

  1. ==============================

    1.이 코드는 SQL 2005,하지만 몇 가지주의에 그것을 수행해야합니다

    이 코드는 SQL 2005,하지만 몇 가지주의에 그것을 수행해야합니다

    데이터베이스를 이해하지 못하거나 물건을 어디에서 오는 알 수없는 경우, 그때는 아마 않는 사람을 찾아야한다. 데이터가 어디에 있는지 찾을 수 있습니다하더라도, 그것은 중복 곳이 될 수 있습니다 또는 당신이 이해하지 않는 데이터베이스의 다른 측면이있을 수 있습니다. 회사의 아무도 데이터베이스를 이해하지 않으면 당신은 꽤 큰 혼란에있어.

    DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_schema   SYSNAME,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'Test'

DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_schema, @table_name

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL  -- Only strings have this and they always have it

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE ''%' + @search_string + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ', ' + QUOTENAME(@column_name) + ''''

        EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_schema, @table_name
END

CLOSE tables_cur

DEALLOCATE tables_cur
  2. ==============================

    2.난 당신이 자신에게 같은 ApexSQL 검색 이에 대한 제 3 자 도구를 찾을 수 있습니다 (너무 아마 거기 다른 사람이 있습니다를하지만 무료이기 때문에 나는이 하나를 사용) 건의 할 것입니다.

    난 당신이 자신에게 같은 ApexSQL 검색 이에 대한 제 3 자 도구를 찾을 수 있습니다 (너무 아마 거기 다른 사람이 있습니다를하지만 무료이기 때문에 나는이 하나를 사용) 건의 할 것입니다.

    당신이 정말로 SQL의 길을 가고 싶은 경우에 당신은 만든 저장 프로 시저를 사용하여 시도 할 수 있습니다 Sorna 쿠마 Muthuraj - 복사 된 코드는 다음과 같습니다. 그냥 (쉽게 동적 SQL과) 스키마에있는 모든 테이블이 저장 프로 시저를 실행

    CREATE PROCEDURE SearchTables 
 @Tablenames VARCHAR(500) 
,@SearchStr NVARCHAR(60) 
,@GenerateSQLOnly Bit = 0 
AS 

/* 
    Parameters and usage 

    @Tablenames        -- Provide a single table name or multiple table name with comma seperated.  
                        If left blank , it will check for all the tables in the database 
    @SearchStr        -- Provide the search string. Use the '%' to coin the search.  
                        EX : X%--- will give data staring with X 
                             %X--- will give data ending with X 
                             %X%--- will give data containig  X 
    @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.  
                        By default it is 0 and it will search. 

    Samples : 

    1. To search data in a table 

        EXEC SearchTables @Tablenames = 'T1' 
                         ,@SearchStr  = '%TEST%' 

        The above sample searches in table T1 with string containing TEST. 

    2. To search in a multiple table 

        EXEC SearchTables @Tablenames = 'T2' 
                         ,@SearchStr  = '%TEST%' 

        The above sample searches in tables T1 & T2 with string containing TEST. 

    3. To search in a all table 

        EXEC SearchTables @Tablenames = '%' 
                         ,@SearchStr  = '%TEST%' 

        The above sample searches in all table with string containing TEST. 

    4. Generate the SQL for the Select statements 

        EXEC SearchTables @Tablenames        = 'T1' 
                         ,@SearchStr        = '%TEST%' 
                         ,@GenerateSQLOnly    = 1 

*/ 

    SET NOCOUNT ON 

    DECLARE @CheckTableNames Table 
    ( 
    Tablename sysname 
    ) 

    DECLARE @SQLTbl TABLE 
    ( 
     Tablename        SYSNAME 
    ,WHEREClause    VARCHAR(MAX) 
    ,SQLStatement   VARCHAR(MAX) 
    ,Execstatus        BIT  
    ) 

    DECLARE @sql VARCHAR(MAX) 
    DECLARE @tmpTblname sysname 

    IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') 
    BEGIN 

        INSERT INTO @CheckTableNames 
        SELECT Name 
          FROM sys.tables 
    END 
    ELSE 
    BEGIN 

        SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' 

        INSERT INTO @CheckTableNames 
        EXEC(@sql) 

    END 

    INSERT INTO @SQLTbl 
    ( Tablename,WHEREClause) 
    SELECT SCh.name + '.' + ST.NAME, 
            ( 
                SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) 
                  FROM SYS.columns SC 
                  JOIN SYS.types STy 
                    ON STy.system_type_id = SC.system_type_id 
                   AND STy.user_type_id =SC.user_type_id 
                 WHERE STY.name in ('varchar','char','nvarchar','nchar') 
                   AND SC.object_id = ST.object_id 
                 ORDER BY SC.name 
                FOR XML PATH('') 
            ) 
      FROM  SYS.tables ST 
      JOIN @CheckTableNames chktbls 
                ON chktbls.Tablename = ST.name  
      JOIN SYS.schemas SCh 
        ON ST.schema_id = SCh.schema_id 
     WHERE ST.name <> 'SearchTMP' 
      GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ; 

      UPDATE @SQLTbl 
         SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 

      DELETE FROM @SQLTbl 
       WHERE WHEREClause IS NULL 

    WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
    BEGIN 

        SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement 
          FROM @SQLTbl  
         WHERE ISNULL(Execstatus ,0) = 0 



         IF @GenerateSQLOnly = 0 
         BEGIN 

            IF OBJECT_ID('SearchTMP','U') IS NOT NULL 
                DROP TABLE SearchTMP 
            EXEC (@SQL) 

            IF EXISTS(SELECT 1 FROM SearchTMP) 
            BEGIN 
                SELECT Tablename=@tmpTblname,* FROM SearchTMP 
            END 

         END 
         ELSE 
         BEGIN 
             PRINT REPLICATE('-',100) 
             PRINT @tmpTblname 
             PRINT REPLICATE('-',100) 
             PRINT replace(@sql,'INTO SearchTMP','') 
         END 

         UPDATE @SQLTbl 
            SET Execstatus = 1 
          WHERE Tablename = @tmpTblname 

    END 

    SET NOCOUNT OFF 

go
  3. ==============================

    3.솔루션 전에 발표 유효하고 일이지만, 나는 겸손하게 내가보기로 적어도, 청소기, 더 우아하고 더 나은 성능의 코드를 제공합니다.

    솔루션 전에 발표 유효하고 일이지만, 나는 겸손하게 내가보기로 적어도, 청소기, 더 우아하고 더 나은 성능의 코드를 제공합니다.

    왜 누구도 세계에 코드를 필요 맹목적 문자열을 찾습니다 첫째, 하나 요청할 수 있습니다? 이봐, 그들은 이미 당신이 모르는, 전체 텍스트를 발명?

    내 대답은 내 주로 작업은 시스템 통합 프로젝트에 있고, 나는 거의 발생하지 않는 새로운 undocummented 데이터베이스를 배우고 때마다 데이터가 기록되는 경우 발견하는 것이 중요합니다.

    또한, 코드 I 존재는 검색이 더 강력하고 위험한 스크립트의 버전을 박탈은 데이터베이스를 통해 텍스트를 대체합니다.

    CREATE TABLE #result(
  id      INT IDENTITY, -- just for register seek order
  tblName VARCHAR(255),
  colName VARCHAR(255),
  qtRows  INT
)
go

DECLARE @toLookFor VARCHAR(255)
SET @toLookFor = '[input your search criteria here]'

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
  '[' + usr.name + '].[' + tbl.name + ']' AS tblName,
  '[' + col.name + ']' AS colName,
  LOWER(typ.name) AS typName
FROM
  sysobjects tbl
    INNER JOIN(
      syscolumns col
        INNER JOIN systypes typ
        ON typ.xtype = col.xtype
    )
    ON col.id = tbl.id
    --
    LEFT OUTER JOIN sysusers usr
    ON usr.uid = tbl.uid

WHERE tbl.xtype = 'U'
  AND LOWER(typ.name) IN(
        'char', 'nchar',
        'varchar', 'nvarchar',
        'text', 'ntext'
      )
ORDER BY tbl.name, col.colorder
--
DECLARE @tblName VARCHAR(255)
DECLARE @colName VARCHAR(255)
DECLARE @typName VARCHAR(255)
--
DECLARE @sql  NVARCHAR(4000)
DECLARE @crlf CHAR(2)

SET @crlf = CHAR(13) + CHAR(10)

OPEN cCursor
FETCH cCursor
INTO @tblName, @colName, @typName

WHILE @@fetch_status = 0
BEGIN
  IF @typName IN('text', 'ntext')
  BEGIN
    SET @sql = ''
    SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
    SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
    SET @sql = @sql + 'FROM ' + @tblName + @crlf
    SET @sql = @sql + 'WHERE PATINDEX(''%'' + @toLookFor + ''%'', ' + @colName + ') > 0' + @crlf
  END
  ELSE
  BEGIN
    SET @sql = ''
    SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
    SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
    SET @sql = @sql + 'FROM ' + @tblName + @crlf
    SET @sql = @sql + 'WHERE ' + @colName + ' LIKE ''%'' + @toLookFor + ''%''' + @crlf
  END

  EXECUTE sp_executesql
            @sql,
            N'@tblName varchar(255), @colName varchar(255), @toLookFor varchar(255)',
            @tblName, @colName, @toLookFor

  FETCH cCursor
  INTO @tblName, @colName, @typName
END

SELECT *
FROM #result
WHERE qtRows > 0
ORDER BY id
GO

DROP TABLE #result
go
  4. ==============================

    4.당신이 응용 프로그램에서 "데이터를 가져 오는"경우, 분별있는 것은 프로파일 러를 사용하고 응용 프로그램을 실행하는 동안 데이터베이스를 프로파일 링하는 것입니다. 그것을 추적, 그 문자열에 대한 결과를 검색 할 수 있습니다.

    당신이 응용 프로그램에서 "데이터를 가져 오는"경우, 분별있는 것은 프로파일 러를 사용하고 응용 프로그램을 실행하는 동안 데이터베이스를 프로파일 링하는 것입니다. 그것을 추적, 그 문자열에 대한 결과를 검색 할 수 있습니다.

  5. ==============================

    5.SSMS 도구 마이크로 소프트 SQL Server Management Studio를위한 추가 기능 (추가 기능) 및 Microsoft SQL Server 관리 Studio Express는 정확히 당신이 필요로 할 것입니다 PACK. 큰 데이터베이스에 그것은 검색하는 데 시간이 걸리지 만, 이것은 예상 할 수있는 것이다. 또한 처음에 SQL Server 관리 Studio에 포함되어 있어야합니다 멋진 기능의 톤을 포함한다. 그것을 시도의 www.ssmstoolspack.com/주세요

    SSMS 도구 마이크로 소프트 SQL Server Management Studio를위한 추가 기능 (추가 기능) 및 Microsoft SQL Server 관리 Studio Express는 정확히 당신이 필요로 할 것입니다 PACK. 큰 데이터베이스에 그것은 검색하는 데 시간이 걸리지 만, 이것은 예상 할 수있는 것이다. 또한 처음에 SQL Server 관리 Studio에 포함되어 있어야합니다 멋진 기능의 톤을 포함한다. 그것을 시도의 www.ssmstoolspack.com/주세요

    당신은 SQL Server Management Studio에서이 도구를 실행하기 위해 설치를위한 SP2가 필요 없습니다.

  6. ==============================

    6.나는 절은 같은보다는 PATINDEX를 사용하여뿐만 아니라, 텍스트 / NTEXT 필드를 확인하려면 위치를 변경 원래 2002 년 나라야나 Vyas는 Kondreddi에 의해 작성된 스크립트를 적용. 나는 또한 약간 결과 테이블을 변경했습니다. 부당하게, 나는 변수 이름을 변경, 내가 (씨 Kondretti에 무례를) 선호하지 않기 때문에 정렬됩니다. 사용자는 유형을 검색 한 데이터를 변경할 수 있습니다. 나는 중간 처리를 조회 할 수 있도록 글로벌 테이블을 사용하지만, 영구 테이블이 갈 수있는 현명한 방법이 될 수 있습니다.

    나는 절은 같은보다는 PATINDEX를 사용하여뿐만 아니라, 텍스트 / NTEXT 필드를 확인하려면 위치를 변경 원래 2002 년 나라야나 Vyas는 Kondreddi에 의해 작성된 스크립트를 적용. 나는 또한 약간 결과 테이블을 변경했습니다. 부당하게, 나는 변수 이름을 변경, 내가 (씨 Kondretti에 무례를) 선호하지 않기 때문에 정렬됩니다. 사용자는 유형을 검색 한 데이터를 변경할 수 있습니다. 나는 중간 처리를 조회 할 수 있도록 글로벌 테이블을 사용하지만, 영구 테이블이 갈 수있는 현명한 방법이 될 수 있습니다.

    /* original script by Narayana Vyas Kondreddi, 2002 */
/* adapted by Oliver Holloway, 2009 */

/* these lines can be replaced by use of input parameter for a proc */
declare @search_string varchar(1000);
set @search_string = 'what.you.are.searching.for';

/* create results table */
create table ##string_locations (
  table_name varchar(1000),
  field_name varchar(1000),
  field_value varchar(8000)
)
;
/* special settings */
set nocount on
;
/* declare variables */
declare
  @table_name varchar(1000),
  @field_name varchar(1000)
;
/* variable settings */
set @table_name = ''
;
set @search_string = QUOTENAME('%' + @search_string + '%','''')
;
/* for each table */
while @table_name is not null
begin

  set @field_name = ''
  set @table_name = (
    select MIN(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name))
    from INFORMATION_SCHEMA.TABLES
    where 
      table_type = 'BASE TABLE' and
      QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) > @table_name and
      OBJECTPROPERTY(OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)), 'IsMSShipped') = 0
  )

  /* for each string-ish field */
  while (@table_name is not null) and (@field_name is not null)
  begin
    set @field_name = (
      select MIN(QUOTENAME(column_name))
      from INFORMATION_SCHEMA.COLUMNS
      where 
        table_schema    = PARSENAME(@table_name, 2) and
        table_name  = PARSENAME(@table_name, 1) and
        data_type in ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext') and
        QUOTENAME(column_name) > @field_name
    )

    /* search that field for the string supplied */
    if @field_name is not null
    begin
      insert into ##string_locations
      exec(
        'select ''' + @table_name + ''',''' + @field_name + ''',' + @field_name + 
        'from ' + @table_name + ' (nolock) ' +
        'where patindex(' + @search_string + ',' + @field_name + ') > 0'  /* patindex works with char & text */
      )
    end
    ;
  end
  ;
end
;

/* return results */
select table_name, field_name, field_value from ##string_locations (nolock)
;
/* drop temp table */
--drop table ##string_locations
;
  7. ==============================

    7.다른 답변을 잘 또는 더 나은 동일하게 작동 할 수 이미 게시,하지만 난 그들을 사용하지 않았습니다. 그러나 다음 SQL 내가 사용하고, 나는 거대한 (매우 unorganzied) SQL Server 데이터베이스와 리버스 엔지니어링 큰 시스템에 시도 할 때 정말 나를 도왔다.

    다른 답변을 잘 또는 더 나은 동일하게 작동 할 수 이미 게시,하지만 난 그들을 사용하지 않았습니다. 그러나 다음 SQL 내가 사용하고, 나는 거대한 (매우 unorganzied) SQL Server 데이터베이스와 리버스 엔지니어링 큰 시스템에 시도 할 때 정말 나를 도왔다.

    이건 내 코드가 아닙니다. 나는 원래 저자를 신용 할 수 있지만이 기사에 대한 링크를 더 이상 찾을 수 없습니다 바랍니다 :(

    Use 
go

declare @SearchChar varchar(8000)
Set @SearchChar =  -- Like 'A%', '11/11/2006'

declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000)
declare @ColumnName varchar(100),@TableName varchar(100)

declare dbTable cursor for 
SELECT 
Distinct b.Name as TableName
FROM 
sysobjects b
WHERE 
b.type='u' and b.Name  'dtproperties'
order by b.name
open dbTable
fetch next from dbTable into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
declare db cursor for 
SELECT 
c.Name as ColumnName
FROM 
sysobjects b,
syscolumns c
WHERE 
C.id = b.id and
b.type='u' and b.Name = @TableName
order by b.name
open db
fetch next from db into @ColumnName
set @CMDMain = 'SELECT ' + char(39) + @TableName + char(39) + ' as TableName,'+ 
' ['+ @TableName + '].* FROM [' + @TableName + ']'+
' WHERE '
set @CMDMainCount = 'SELECT Count(*) FROM [' + @TableName + '] Where '
Set @CMDJoin = ''
WHILE @@FETCH_STATUS = 0
BEGIN
set @CMDJoin = @CMDJoin + 'Convert(varchar(5000),[' +@ColumnName + ']) like ' + char(39) + @SearchChar + char(39) + ' OR '

fetch next from db into @ColumnName
end
close db
deallocate db

Set @CMDMainCount = 'If ('+ @CMDMainCount + Left(@CMDJoin, len(@CMDJoin) - 3)+ ') > 0 Begin '
Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) - 3)
Set @CMDMain = @CMDMain + ' End '

Print @CMDMain

exec (@CMDMain)
fetch next from dbTable into @TableName
end
close dbTable
deallocate dbTable
  8. ==============================

    8.사실 필자는이 경우에 대한 사용 프로파일에 더 나은 MikeW (+1)에 동의합니다.

    사실 필자는이 경우에 대한 사용 프로파일에 더 나은 MikeW (+1)에 동의합니다.

    어쨌든, 당신이 정말로 DB의 모든 (n)이 VARCHAR 컬럼을 잡고 검색을해야합니다. 아래를 참조하십시오. 나는 INFORMATION_SCHEMA.TABLES + 동적 SQL을 사용하는 가정합니다. 일반 검색 :

    DECLARE @SearchText VARCHAR(100) 
SET @SearchText = '12'
DECLARE @Tables TABLE(N INT, TableName VARCHAR(100), ColumnNamesCSV VARCHAR(2000), SQL VARCHAR(4000))

INSERT INTO @Tables (TableName, ColumnNamesCSV)
SELECT  T.TABLE_NAME AS TableName, 
        ( SELECT C.Column_Name + ',' 
          FROM   INFORMATION_SCHEMA.Columns C 
          WHERE  T.TABLE_NAME = C.TABLE_NAME 
                 AND C.DATA_TYPE IN ('nvarchar','varchar') 
                 FOR XML PATH('')
        )
FROM    INFORMATION_SCHEMA.Tables T 

DELETE FROM @Tables WHERE ColumnNamesCSV IS NULL

INSERT INTO @Tables (N, TableName, ColumnNamesCSV)
SELECT ROW_NUMBER() OVER(ORDER BY TableName), TableName, ColumnNamesCSV  
FROM   @Tables

DELETE FROM @Tables WHERE N IS NULL

UPDATE @Tables 
SET ColumnNamesCSV = SUBSTRING(ColumnNamesCSV, 0, LEN(ColumnNamesCSV))

UPDATE @Tables 
SET SQL = 'SELECT * FROM ['+TableName+'] WHERE '''+@SearchText+''' IN ('+ColumnNamesCSV+')'

DECLARE @C INT, 
        @I INT, 
        @SQL VARCHAR(4000)

SELECT @I = 1, 
       @C = COUNT(1) 
FROM   @Tables

WHILE @I <= @C BEGIN
    SELECT @SQL = SQL FROM @Tables WHERE N = @I
    SET @I = @I+1
    EXEC(@SQL)
END

    및 LIKE 절 하나 :

    DECLARE @SearchText VARCHAR(100) 
SET @SearchText = '12'

DECLARE @Tables TABLE(N INT, TableName VARCHAR(100), ColumnNamesCSVLike VARCHAR(2000), LIKESQL VARCHAR(4000))

INSERT INTO @Tables (TableName, ColumnNamesCSVLike)
SELECT   T.TABLE_NAME AS TableName, 
         (   SELECT  C.Column_Name + ' LIKE ''%'+@SearchText+'%'' OR ' 
             FROM    INFORMATION_SCHEMA.Columns C 
             WHERE   T.TABLE_NAME = C.TABLE_NAME 
                     AND C.DATA_TYPE IN ('nvarchar','varchar') 
          FOR XML PATH(''))
FROM     INFORMATION_SCHEMA.Tables T

DELETE FROM @Tables WHERE ColumnNamesCSVLike IS NULL

INSERT INTO @Tables (N, TableName, ColumnNamesCSVLike)
SELECT ROW_NUMBER() OVER(ORDER BY TableName), TableName, ColumnNamesCSVLike 
FROM @Tables

DELETE FROM @Tables WHERE N IS NULL

UPDATE @Tables 
SET  ColumnNamesCSVLike = SUBSTRING(ColumnNamesCSVLike, 0, LEN(ColumnNamesCSVLike)-2)

UPDATE @Tables SET LIKESQL = 'SELECT * FROM ['+TableName+'] WHERE '+ColumnNamesCSVLike

DECLARE @C INT, 
        @I INT, 
        @LIKESQL VARCHAR(4000)

SELECT @I = 1, 
       @C = COUNT(1) 
FROM @Tables

WHILE @I <= @C BEGIN
    SELECT @LIKESQL = LIKESQL FROM @Tables WHERE N = @I
    SET @I = @I +1
    EXEC(@LIKESQL)
END
  9. ==============================

    9.@NLwino 키워드 사용에 대한 몇 가지 오류가있는 yery 좋은 쿼리. 나는 []와 키워드를 감싸고 또한 문자와 NTEXT 열을보고 그것을 조금 수정했다.

    @NLwino 키워드 사용에 대한 몇 가지 오류가있는 yery 좋은 쿼리. 나는 []와 키워드를 감싸고 또한 문자와 NTEXT 열을보고 그것을 조금 수정했다.

        DECLARE @searchstring  NVARCHAR(255)
    SET @searchstring = '%WDB1014%'

    DECLARE @sql NVARCHAR(max)

    SELECT @sql = STUFF((
      SELECT ' UNION ALL SELECT ''' + TABLE_NAME + ''' AS tbl, ''' + COLUMN_NAME + ''' AS col, [' + COLUMN_NAME + '] AS val' + 
        ' FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + 
        '] WHERE [' + COLUMN_NAME + '] LIKE ''' + @searchstring + ''''
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE DATA_TYPE in ('nvarchar', 'varchar', 'char', 'ntext')
                  FOR XML PATH('')
             ) ,1, 11, '')

    Exec (@sql)

    나는 2.5 GB의 데이터베이스를 실행하고 51 초 만에 돌아왔다

  10. ==============================

    10.이것은 더 커서 나 같은 것도, 하나의 동적 쿼리를 사용하지 않습니다.

    이것은 더 커서 나 같은 것도, 하나의 동적 쿼리를 사용하지 않습니다.

    또한이 사용하는 같은 있습니다. 그게 내가 필요로 무엇을 할 일이 있기 때문에. 그것은 모든 스키마, 모든 테이블 작동 만 쿼리은 UDDT이 있더라도 NVARCHAR 또는 VARCHAR입니다 그 열을합니다.

    DECLARE @searchstring  NVARCHAR(255)
SET @searchstring = '%searchstring%'

DECLARE @sql NVARCHAR(max)

SELECT @sql = STUFF((
    SELECT ' UNION ALL SELECT ''' + TABLE_NAME + ''' AS tablename, ''' + COLUMN_NAME + ''' AS columnname, ' + COLUMN_NAME + ' AS valuename' + 
    ' FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME + 
    ' WHERE ' + COLUMN_NAME + ' LIKE ''' + @searchstring + ''''
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE DATA_TYPE in ('nvarchar', 'varchar')
    FOR XML PATH('')
) ,1, 11, '')

EXEC(@sql)

    출력은 당신에게 테이블, 열 및 가치를 제공합니다. 작은 데이터베이스에 실행 시간, 2 ~ 3 초였다 3000 개 결과에 대한했다.

  11. ==============================

    11.

    /*
This procedure is for finding any string or date in all tables
if search string is date, its format should be yyyy-MM-dd
eg. 2011-07-05
*/

-- ================================================
-- Exec SearchInTables 'f6f56934-a5d4-4967-80a1-1a2223b9c7b1'

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Joshy,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE SearchInTables
@myValue nvarchar(1000)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
        DECLARE @searchsql nvarchar(max)
        DECLARE @table_name nvarchar(1000)
        DECLARE @Schema_name nvarchar(1000)
        DECLARE @ParmDefinition nvarchar(500)
        DECLARE @XMLIn nvarchar(max)
        SET @ParmDefinition = N'@XMLOut varchar(max) OUTPUT'

        SELECT A.name,b.name 
        FROM sys.tables A 
            INNER JOIN sys.schemas B ON A.schema_id=B.schema_id
        WHERE A.name like 'tbl_Tax_Sections' 

        DECLARE tables_cur CURSOR FOR 
                            SELECT A.name,b.name FOM sys.tables A 
                            INNER JOIN sys.schemas B ON A.schema_id=B.schema_id
                            WHERE A.type = 'U'  
        OPEN tables_cur  
        FETCH NEXT FROM tables_cur INTO @table_name , @Schema_name 
            WHILE (@@FETCH_STATUS = 0) 
            BEGIN
                SET @searchsql ='SELECT @XMLOut=(SELECT PATINDEX(''%'+ @myValue+ '%'''
                SET @searchsql =@searchsql  + ', (SELECT * FROM '+@Schema_name+'.'+@table_name+' FOR XML AUTO) ))'
                --print @searchsql 
                EXEC sp_executesql @searchsql, @ParmDefinition, @XMLOut=@XMLIn OUTPUT
                --print @XMLIn 

                IF @XMLIn <> 0 PRINT @Schema_name+'.'+@table_name

                FETCH NEXT FROM tables_cur INTO @table_name , @Schema_name 

            END
        CLOSE tables_cur 
        DEALLOCATE tables_cur 
    RETURN
END
GO
  12. ==============================

    12."내가 할 데이터의 출처 찾기"에, 당신은 SQL 프로파일 러를 시작 보고서 또는 응용 프로그램을 시작, 당신은 당신의 데이터베이스에 대해 실행 된 모든 쿼리를 볼 수 있습니다.

    "내가 할 데이터의 출처 찾기"에, 당신은 SQL 프로파일 러를 시작 보고서 또는 응용 프로그램을 시작, 당신은 당신의 데이터베이스에 대해 실행 된 모든 쿼리를 볼 수 있습니다.

  13. ==============================

    13.나는이 커서를 사용 수리공 없이도와 XML에 대한 데이터베이스의 모든 행에 문자열을 찾을 수있는 가장 쉬운 방법이 될 수 있다고 생각합니다.

    나는이 커서를 사용 수리공 없이도와 XML에 대한 데이터베이스의 모든 행에 문자열을 찾을 수있는 가장 쉬운 방법이 될 수 있다고 생각합니다.

    CREATE PROCEDURE SPFindAll (@find VARCHAR(max) = '')
AS
BEGIN
    SET NOCOUNT ON;
    --
    DECLARE @query VARCHAR(max) = ''

    SELECT  @query = @query + 
            CASE 
                WHEN @query = '' THEN '' 
                ELSE ' UNION ALL '
            END +
            'SELECT ''' + s.name + ''' As schemaName, ''' + t.name + ''' As tableName, ''' + c.name + ''' As ColumnName, [' + c.name + '] COLLATE DATABASE_DEFAULT As [Data] FROM [' + s.name + '].[' + t.name + '] WHERE [' + c.name + '] Like ''%' + @find + '%'''
    FROM 
        sys.schemas s
        INNER JOIN
        sys.tables t ON s.[schema_id] = t.[schema_id]
        INNER JOIN 
        sys.columns c ON t.[object_id] = c.[object_id]
        INNER JOIN
        sys.types ty ON c.user_type_id = ty.user_type_id
    WHERE
        ty.name LIKE '%char'

    EXEC(@query)
END

    이 저장 프로 시저를 작성하여 당신은 당신이 이런 식으로 찾아 원하는 문자열을 실행할 수 있습니다 :

    EXEC SPFindAll 'Hello World'

    그 결과는 다음과 같이 될 것입니다 :

    schemaName | tableName | columnName | Data
-----------+-----------+------------+-----------------------
schema1    | Table1    | Column1    | Hello World
schema1    | Table1    | Column1    | Hello World!
schema1    | Table2    | Column1    | I say "Hello World".
schema1    | Table2    | Column2    | Hello World
  14. ==============================

    14.FullParam SQL 블로그 : 또는, 당신은 당신이 검색 할 각 DB에 대한 sprocs가를 만들 필요 후 간단합니다, 여기 내 쿼리를 사용할 수 있습니다

    FullParam SQL 블로그 : 또는, 당신은 당신이 검색 할 각 DB에 대한 sprocs가를 만들 필요 후 간단합니다, 여기 내 쿼리를 사용할 수 있습니다

    /* 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 #Results FROM 개수 GROUP BY TABLENAME,의 ColumnName, ColumnValue, ColumnType

  15. ==============================

    15.이 쿼리는 당신을 위해 일을 할 수 있습니다.

    이 쿼리는 당신을 위해 일을 할 수 있습니다.

    DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = 'StringtoSearch'

DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id 
        AND system_type_id IN (167, 175, 231, 239, 99)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] 
            LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

            EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
        END

    CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur
DEALLOCATE tables_cur

    16. from https://stackoverflow.com/questions/591853/search-for-a-string-in-all-tables-rows-and-columns-of-a-db by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 쿼리 기존 테이블에 대한 스크립트를 생성 생성  (0) 2020.03.31
[SQL] SQLite는 데이터베이스에서 중복 행을 삭제  (0) 2020.03.31
[SQL] 테이블에 MySQL의 :: 삽입, 다른 테이블에서 데이터?  (0) 2020.03.31
[SQL] 다른 테이블에 데이터를 복사  (0) 2020.03.31
[SQL] 오라클 데이터베이스의 모든 부울 유형이 있습니까?  (0) 2020.03.31
,

티스토리툴바