SQL Server. Поиск по всем таблицам и колонкам в Базе Данных

Есть несколько способов произвести поиск по БД, когда мы не понимаем где нам нужно искать:

1. Подумать как могут называться нужные нам таблицы и произвести поиск по названиям таблиц, после чего поискать там
2. Поиска можно не только по таблицам, но и по колонкам
3. Два предыдущих способа не дают 100% результата, поэтому предлагаю вам рассмотреть ещё один. Этот способ даёт больше шансов найти то что мы ищем, но он значительно дольше и тратит ресурсы нашего сервера.
4. Можно воспользоваться плагином для SSMS — ApexSQL Search

Рассмотрим 3-ий способ подробнее. Суть заключается в том, чтобы произвести поиск по всем таблицам и колонкам в Базе Данных. Чтобы скриптом было удобно пользоваться, мы обернём его в процедуру SearchAllTables, которая ищет по всем колонкам типа char, varchar, nchar, nvarchar по всей БД (системные таблицы исключены из поиска)

Вывод этой процедуры состоит из 2х колонок:

1. Имя таблица и колонки, в которой были найдены совпадения
2. Текст, в котором были найдены совпадения (будут отображены только первые 3630 символов)

Будьте осторожны, на больших Базах Данных скрипт может работать очень долго (часы). Так что запуская его на больших БД, будьте готовы ждать.

Создайте процедуру в той БД, в которой необходимо произвести поиск:

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN
	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

Вызов процедуры выглядит так:

EXEC SearchAllTables 'Computer'
GO

Так же есть очень похожий вариант этого скрипта через другие представления. На этот раз мы обойдёмся без процедур. Строку поиска необходимо указать в @SearchText :

DECLARE
   @SearchText varchar(200),
   @Table varchar(100),
   @TableID int,
   @ColumnName varchar(100),
   @String varchar(1000);
--modify the variable, specify the text to search for
SET @SearchText = 'John';
DECLARE CursorSearch CURSOR
    FOR SELECT name, object_id
        FROM sys.objects
      WHERE type = 'U';
--list of tables in the current database. Type = 'U' = tables(user-defined)
OPEN CursorSearch;
FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
WHILE
       @@FETCH_STATUS
       =
       0
    BEGIN
        DECLARE CursorColumns CURSOR
            FOR SELECT name
                  FROM sys.columns
                WHERE
                       object_id
                       =
                       @TableID AND system_type_id IN(167, 175, 231, 239);
        -- the columns that can contain textual data        
--167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar        
OPEN CursorColumns;
        FETCH NEXT FROM CursorColumns INTO @ColumnName;
        WHILE
               @@FETCH_STATUS
               =
               0
            BEGIN
                SET @String = 'IF EXISTS (SELECT * FROM '
                            + @Table
                            + ' WHERE '
                            + @ColumnName
                            + ' LIKE ''%'
                            + @SearchText
                            + '%'') PRINT '''
                            + @Table
                            + ', '
                            + @ColumnName
                            + '''';
                EXECUTE (@String);
                FETCH NEXT FROM CursorColumns INTO @ColumnName;
            END;
        CLOSE CursorColumns;
        DEALLOCATE CursorColumns;
        FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
    END;
CLOSE CursorSearch;
DEALLOCATE CursorSearch;

Интересная, но более сложная, реализация поиска (проверено только на SQL Server 2014)

Вырезка из статей:

1. How to search all columns of all tables in a database for a keyword?
2. How to quickly search for SQL database data and objects