Поиск строки во всех таблицах, строках и столбцах базы данных

Ответ 1

Этот код должен делать это в 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

Id предлагает вам найти сторонний инструмент для этого, например ApexSQL Search (возможно, есть и другие, но я использую этот, потому что он бесплатный).

Если вы действительно хотите использовать способ SQL, вы можете попробовать использовать хранимую процедуру, созданную Sorna Kumar 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 [email protected],* 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

Несмотря на то, что решения, представленные ранее, действительны и работают, я смиренно предлагаю код, который чище, элегантнее и с лучшей производительностью, по крайней мере, как я вижу.

Во-первых, можно спросить: зачем кому-нибудь когда-нибудь понадобится фрагмент кода в глобальном масштабе и слепо искать строку? Эй, они уже придумали полный текст, разве ты не знаешь?

Мой ответ: в основном я работаю над проектами системной интеграции, и обнаружение того, где данные написаны, важно, когда я изучаю новую и непризнанную базу данных, что редко бывает.

Кроме того, представленный мной код является усеченной версией более мощного и опасного script, который выполняет поиск и заменяет текст по всей базе данных.

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

Если вы получаете данные из приложения, разумным было бы использовать профилировщик и профилировать базу данных при запуске приложения. Проследите его, затем найдите результаты для этой строки.

Ответ 5

Инструменты SSMS. Надстройка PACK (надстройка) для Microsoft SQL Server Management Studio и Microsoft SQL Server Management Studio Express сделает именно то, что вам нужно. В более крупной базе данных требуется некоторое время для поиска, но этого и следовало ожидать. Он также включает массу интересных функций, которые должны были быть включены в SQL Server Management Studio в первую очередь. Попробуйте www.ssmstoolspack.com/

Для запуска инструментов вам необходимо установить SP2 для SQL Server Management Studio.

Ответ 6

Я адаптировал script, первоначально написанный Нараяна Вьяс Кондредди в 2002 году. Я изменил предложение where, чтобы проверить поля text/ntext, используя, например, patindex, а не как. Я также немного изменил таблицу результатов. Неоправданно я изменил имена переменных и выровнял, как я предпочитаю (без неуважения к г-ну Кондретти). Пользователь может захотеть изменить поиск данных. Я использовал глобальную таблицу, позволяющую запрашивать среднюю обработку, но постоянная таблица может быть более разумным способом.

/* 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

На самом деле я согласен с MikeW (+1), что лучше использовать профилировщик для этого случая.

В любом случае, если вам действительно нужно захватить все (n) столбцы varchar в db и выполнить поиск. Смотри ниже. Я предполагаю использовать 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 '''[email protected]+''' 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 ''%'[email protected]+'%'' 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

Ответ 8

Другие опубликованные ответы могут работать одинаково хорошо или лучше, но я их не использовал. Тем не менее, следующий SQL, который я использовал, и это действительно помогло мне, когда я пытался перепроектировать большую систему с огромной (и очень неорганизованной) базой данных 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),[' [email protected] + ']) 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

Ответ 9

Здесь не используются курсоры или что-то в этом роде, только один динамический запрос.

Также обратите внимание, что это использует LIKE. Так как это было то, что мне нужно. Он работает для всех схем, всех таблиц и запрашивает только те столбцы, которые являются NVARCHAR или VARCHAR, даже если они имеют UDDT.

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)

Результат выводит таблицу, столбец и значение. Время выполнения на небольшой базе данных составляло ~ 3 секунды, было около 3000 результатов.

Ответ 10

@NLwino, хороший запрос с несколькими ошибками для использования ключевых слов. Мне пришлось немного изменить его, чтобы обернуть ключевые слова с помощью [], а также посмотреть столбцы char и 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 ГБ, и он вернулся через 51 секунду.

Ответ 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 [email protected]  + ', (SELECT * FROM '[email protected]_name+'.'[email protected]_name+' FOR XML AUTO) ))'
                --print @searchsql 
                EXEC sp_executesql @searchsql, @ParmDefinition, @[email protected] OUTPUT
                --print @XMLIn 

                IF @XMLIn <> 0 PRINT @Schema_name+'.'[email protected]_name

                FETCH NEXT FROM tables_cur INTO @table_name , @Schema_name 

            END
        CLOSE tables_cur 
        DEALLOCATE tables_cur 
    RETURN
END
GO

Ответ 12

Чтобы "найти, откуда получаются данные", вы можете запустить SQL Profiler, запустить свой отчет или приложение, и вы увидите все запросы, выпущенные против вашей базы данных.

Ответ 13

Я думаю, что это может быть самый простой способ найти строку во всех строках вашей базы данных - без использования курсоров и FOR 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

Или вы можете использовать мой запрос здесь, должно быть проще, тогда вам нужно создать sProcs для каждого БД, который вы хотите искать: Блог FullParam SQL

/* 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