はじめに
データベースに含まれる特定の内容や列を検索することは、SQL Serverの管理者や開発者がよく行うタスクです。データベースの構造をよく知っている場合や、データベースがきちんと文書化されている場合なら、検索は困難なことではありません。けれども、常にそうだとは限りません。検索すべきデータベースのことをよく知らないとか、そのデータベースが文書化されていないといったケースもあるでしょう。そのようなケースで特定の内容や列を検索しようとすると、かなり苦労するはずです。本稿では、この問題を解決するための方法として、列を検索するストアドプロシージャと、列名に含まれるパターンやキーワードを見つけるSELECTステートメントを使ったソリューションを紹介します。
インターネットを検索してみると、詳細がわからないテーブルや列から特定の内容を見つけ出すための使えそうなソリューションがすぐにいくつか見つかりますが、こうしたソリューションは大抵どこかに限界があります。一部の文字データ型しか考慮していないものや、カーソルを使用するというあまりお勧めできないアプローチを採用しているものもあります。カーソルはきちんとクローズしないとメモリリークを生じさせるおそれがあるため、カーソルの使用は避けるべきです。大抵の場合、未クローズのカーソルにはSPID（接続ID）を検索してkillするという方法で対処できますが、未クローズのカーソルは予想外の結果を引き起こす可能性があります。リソースを復元するためにSQL Serverサービスの再起動が必要になる場合もあり、ユーザーやサーバ全体に影響が出るおそれがあります。
改良版のソリューション：すべての文字データ型を対象にし、カーソルを使わない
そこで、上記の問題点を改良したソリューションを考えたいと思います。リスト1のストアドプロシージャは、カーソルの代わりに1つのWHILEループを使用しており、SQL Server 2005以降のSQL Serverで使われるXMLデータ型を除く、すべての文字データ型を考慮に入れています。このコードでは、まず情報スキーマテーブルから取り出したスキーマ名、テーブル名、列名、さらには検索文字列値を格納するための一時テーブルを作成します。その後、見つかった下記のデータ型の各列をループで処理していきます。
- char
- varchar
- text
- nchar
- nvarchar
- ntext
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 = ''
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 <検索文字列>
図1は、AdventureWorks（SQL Serverに付属しているサンプルデータベース）から「brake」という文字列を検索した結果です。検索文字列のインスタンスが11個見つかっています。このストアドプロシージャを使用すれば、検索文字列に一致する値を見つけられるだけでなく、それらがどこ（どのテーブルのどの列）にあるのかも知ることができます。
PATINDEXとSUBSTRINGを使った簡単な構文解析により、結果の文字列が分割されます。このケースでは、SUBSTRINGの方がLEFTよりも柔軟です。なぜなら、すべての文字列型の列に対応できるからです。LEFTはcharデータ型に対してはうまく働きますが、textデータ型に対してはあまり役に立ちません。SUBSTRING関数を使用すれば、1回のパスで結果を取得できます。その後、サブクエリによって値が@columname変数に渡されるので、レコードセットの終わりに達したときにnullを許容し、ループからスマートに抜けることが可能になります。
このコードでは、テーブル名と列名をcolumnnameという列に格納します。パラメータはテーブル名（このケースでは、名前に#が付いた一時テーブル）、列名、およびそれぞれのデータ型です。余分の文字スペース（256）によって長い列名とテーブル名が許容されます。@SearchStr = QUOTENAMEによってテキストが有効なUnicode表現として設定されます。MIN関数によって最小の列（例えば、aより1が前で、bよりaが前など）が取得され、その列に対して検索文字列の検索が実行されます。その列にキーワード値が含まれていれば、その値が一時テーブルに格納されます。含まれていなければ、次の列名が新たな対象になります（@ColumnNameよりも大きい最小の列名を検索するため）。
SearchAllTablesAllColumnsストアドプロシージャは与えられた列内の文字列を検索するので、内容のキーワードはわかっているけれどもそのデータが具体的にどの列に入っているかはわからないという場合に、該当する列を突き止めるために役立ちます。
