列を検索するストアドプロシージャと、列名に含まれるパターンやキーワードを見つけるSELECTステートメントを使ったソリューション

IT Computer Work IT
IT Computer Work
2009.09.03

ストアドプロシージャは、カーソルの代わりに1つのWHILEループを使用しており、SQL Server 2005以降のSQL Serverで使われるXMLデータ型を除く、すべての文字データ型を考慮に入れています。このコードでは、まず情報スキーマテーブルから取り出したスキーマ名、テーブル名、列名、さらには検索文字列値を格納するための一時テーブルを作成します。その後、見つかった下記のデータ型の各列をループで処理していきます。

  • char
  • varchar
  • text
  • nchar
  • nvarchar
  • ntext

SearchAllTablesAllColumnsストアドプロシージャ

CREATE PROC [dbo].[SearchAllTablesAllColumns]
  (
   @SearchStr nvarchar(100)
  )
  AS
  BEGIN 
-- Purpose: To search all columns in all tables for a given search string
-- Written by: Francisco Tapia
-- Site: http://sqlthis.blogspot.com
-- Inspired by: Narayana Vyas Kondreddi
-- http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
-- Tested on: SQL Server 7.0, 2000 and 2005
-- Date modified: June 23, 2009

  CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  SET NOCOUNT ON
  DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT
  SELECT @TableName = '', @ColumnName = ''<br>SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')
  WHILE (@ColumnName IS NOT NULL) 
  BEGIN
  SET @ColumnName = (
     SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name)
     + '|' + QUOTENAME(C.Column_name))
           FROM INFORMATION_SCHEMA.COLUMNS c
              INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema 
                          = t.table_schema and c.Table_name = t.table_name
           WHERE    T.TABLE_TYPE = 'BASE TABLE'
              AND C.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
              AND   QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) 
                    + '.' + QUOTENAME(COLUMN_NAME) > @TableName '.' + @ColumnName ) SET @Parse = PATINDEX ('%|%', @ColumnName) SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1) SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName)) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT + @TableName + '.' + @ColumnName + , SUBSTRING(' + @ColumnName + ',1, 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr ) END END SELECT ColumnName, ColumnValue FROM #Results
ORDER BY ColumnName
END

このストアドプロシージャを実行するときには、次のようにして検索文字列を渡します。

EXEC SearchAllTablesAllColumns <検索文字列>

コメント