CodeZine(コードジン)

特集ページ一覧

構造がわからないデータベースで検索を行うには

ストアドプロシージャ、SELECTステートメントを使って特定の内容を検索

  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加
2009/09/02 14:00

 データベースの構造がわからなくても、あきらめることはありません。テーブルや列の詳細がわからない場合でも、目的の値や列を見つけることは可能です。この記事では、構造がわからないデータベースで検索を行う時に役立つ「ストアドプロシージャ」、「SELECTステートメント」について説明していきます。

目次

はじめに

 データベースに含まれる特定の内容や列を検索することは、SQL Serverの管理者や開発者がよく行うタスクです。データベースの構造をよく知っている場合や、データベースがきちんと文書化されている場合なら、検索は困難なことではありません。けれども、常にそうだとは限りません。検索すべきデータベースのことをよく知らないとか、そのデータベースが文書化されていないといったケースもあるでしょう。そのようなケースで特定の内容や列を検索しようとすると、かなり苦労するはずです。本稿では、この問題を解決するための方法として、列を検索するストアドプロシージャと、列名に含まれるパターンやキーワードを見つけるSELECTステートメントを使ったソリューションを紹介します。

 インターネットを検索してみると、詳細がわからないテーブルや列から特定の内容を見つけ出すための使えそうなソリューションがすぐにいくつか見つかりますが、こうしたソリューションは大抵どこかに限界があります。一部の文字データ型しか考慮していないものや、カーソルを使用するというあまりお勧めできないアプローチを採用しているものもあります。カーソルはきちんとクローズしないとメモリリークを生じさせるおそれがあるため、カーソルの使用は避けるべきです。大抵の場合、未クローズのカーソルにはSPID(接続ID)を検索してkillするという方法で対処できますが、未クローズのカーソルは予想外の結果を引き起こす可能性があります。リソースを復元するためにSQL Serverサービスの再起動が必要になる場合もあり、ユーザーやサーバ全体に影響が出るおそれがあります。

改良版のソリューション:すべての文字データ型を対象にし、カーソルを使わない

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

  • char
  • varchar
  • text
  • nchar
  • nvarchar
  • ntext
リスト1 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 = ''
   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個見つかっています。このストアドプロシージャを使用すれば、検索文字列に一致する値を見つけられるだけでなく、それらがどこ(どのテーブルのどの列)にあるのかも知ることができます。

図1 検索文字列を渡した結果: SearchAllTablesAllColumnsストアドプロシージャは1つのパラメータ(検索文字列)を受け取る
図1 検索文字列を渡した結果: SearchAllTablesAllColumnsストアドプロシージャは1つのパラメータ(検索文字列)を受け取る

 PATINDEXSUBSTRINGを使った簡単な構文解析により、結果の文字列が分割されます。このケースでは、SUBSTRINGの方がLEFTよりも柔軟です。なぜなら、すべての文字列型の列に対応できるからです。LEFTはcharデータ型に対してはうまく働きますが、textデータ型に対してはあまり役に立ちません。SUBSTRING関数を使用すれば、1回のパスで結果を取得できます。その後、サブクエリによって値が@columname変数に渡されるので、レコードセットの終わりに達したときにnullを許容し、ループからスマートに抜けることが可能になります。

 このコードでは、テーブル名と列名をcolumnnameという列に格納します。パラメータはテーブル名(このケースでは、名前に#が付いた一時テーブル)、列名、およびそれぞれのデータ型です。余分の文字スペース(256)によって長い列名とテーブル名が許容されます。@SearchStr = QUOTENAMEによってテキストが有効なUnicode表現として設定されます。MIN関数によって最小の列(例えば、aより1が前で、bよりaが前など)が取得され、その列に対して検索文字列の検索が実行されます。その列にキーワード値が含まれていれば、その値が一時テーブルに格納されます。含まれていなければ、次の列名が新たな対象になります(@ColumnNameよりも大きい最小の列名を検索するため)。

 SearchAllTablesAllColumnsストアドプロシージャは与えられた列内の文字列を検索するので、内容のキーワードはわかっているけれどもそのデータが具体的にどの列に入っているかはわからないという場合に、該当する列を突き止めるために役立ちます。


  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加

あなたにオススメ

著者プロフィール

  • japan.internet.com(ジャパンインターネットコム)

    japan.internet.com は、1999年9月にオープンした、日本初のネットビジネス専門ニュースサイト。月間2億以上のページビューを誇る米国 Jupitermedia Corporation (Nasdaq: JUPM) のニュースサイト internet.com や EarthWeb.c...

  • Francisco Tapia(Francisco Tapia)

     ある大手製造会社のデータベース管理者。SQL Serverはバージョン6.5の頃から使用。詳しくはsqlthis.blogspot.comを参照。

  • Susan Sales Harkins(Susan Sales Harkins)

     独立コンサルタントとして活動するかたわら、データベーステクノロジに関する記事や書籍を執筆。最近の著書は『Mastering Microsoft SQL Server 2005 Express』(Mike Gunderloyと共著、Sybex刊)。

バックナンバー

連載:japan.internet.com翻訳記事

もっと読む

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5