指定した列名を含むテーブル名の一覧を取得する方法＠SQLServer

ちゃんとしたシステムを作るときはER図を作ったりデータベースレイアウトを残しておくのですが、ちょっとしたツールを作る時にはなかなかそこまで手をかけて作りません。もちろん外部キーとかも設定していないので結局一つ一つのテーブルを見直したりしますが、結構面倒だし、場合によってはもれてしまう可能性もあります。

そんな時には、列名をキーにしてその列名を含むテーブル名称の一覧を取得するこのバッチファイルが役に立ちます。

@ECHO OFF

SET WRKSQL="%TEMP%\wrk.sql"
SET SQLTOOL=OSQL
SET SVNAME=%COMPUTERNAME%
SET DBNAME=master
SET COLUMNNAME=UserName
SET LOGFILE="%TEMP%\conf.log"
SET CONF=Y
SET CONNUSER=sa
SET CONNPASS=XXXXXXXX

:INPUTSV
CLS
ECHO 接続先サーバ名を入力してください
SET /PSVNAME=[初期値=%SVNAME%]：
ECHO.
%SQLTOOL% -U%CONNUSER% -P%CONNPASS% -S%SVNAME% -dmaster -Q"SET NOCOUNT ON" | FIND "DBNETLIB" > NUL
IF "%ERRORLEVEL%"=="0" (
  ECHO サーバに接続出来ません[%SVNAME%]
  ECHO.
  PAUSE > NUL
  GOTO INPUTSV
)


:INPUTDB
IF "%DBNAME%"=="?" (
  SET DBNAME=master
)
ECHO 接続先データベース名を入力してください
ECHO 　[?と入れるとデータベース名の一覧を表示します]
SET /PDBNAME=[初期値=%DBNAME%]：
ECHO.
IF "%DBNAME%"=="?" (
  %SQLTOOL% -U%CONNUSER% -P%CONNPASS% -S%SVNAME% -dmaster -n -Q"SELECT Convert(varchar(20),name) as データベース名 FROM sysdatabases order by 1"
  ECHO.
  GOTO INPUTDB
)

ECHO 検索列名を入力してください
SET /PCOLUMNNAME=[初期値=%COLUMNNAME%]：
ECHO.

CLS
ECHO ------------------------------------------------------
ECHO ★入力された情報は以下のとおりです
ECHO  間違いが無いかどうか確認してください
ECHO.
ECHO  [1] 接続先サーバ名 　　　= %SVNAME%
ECHO  [2] 接続先データベース名 = %DBNAME%
ECHO  [3] 検索対象列名　　　　 = %COLUMNNAME%
ECHO ------------------------------------------------------
ECHO.
ECHO 再度入力する場合はN or nを入力してください
SET /PCONF=[N or n:再入力   ←以外は続行]

IF "%CONF%"=="N" GOTO INPUTSV
IF "%CONF%"=="n" GOTO INPUTSV

:// クエリファイル削除
IF EXIST %WRKSQL% (
  DEL %WRKSQL% /F /Q
)

:// ログファイル初期化
ECHO ★列名一覧取得 [実行者：%USERNAME% %DATE% %TIME%] > %LOGFILE%
ECHO. >> %LOGFILE%
ECHO  [1] 接続先サーバ名 　　　= %SVNAME% >> %LOGFILE%
ECHO  [2] 接続先データベース名 = %DBNAME% >> %LOGFILE%
ECHO  [3] 検索対象列名　　　　 = %COLUMNNAME% >> %LOGFILE%
ECHO. >> %LOGFILE%

:// クエリファイル作成
ECHO SELECT Convert(varchar(20),so.name) as TableName > %WRKSQL%
ECHO   FROM syscolumns as sc inner join sysobjects as so >> %WRKSQL%
ECHO     on (sc.id = so.id) >> %WRKSQL%
ECHO  where (so.type = 'U') and (sc.name = '%COLUMNNAME%') ORDER BY 1 >> %WRKSQL%

ECHO. >> %LOGFILE%
ECHO [実行クエリファイル] >> %LOGFILE%
TYPE %WRKSQL% >> %LOGFILE%
ECHO. >> %LOGFILE%

:// クエリを実行
%SQLTOOL% -U%CONNUSER% -P%CONNPASS% -d%DBNAME% -S%SVNAME% -n -w2000 -i%WRKSQL% >> %LOGFILE%

:// ログファイル確認
NOTEPAD %LOGFILE%

:// クエリファイル削除
IF EXIST %WRKSQL% (
  DEL %WRKSQL% /F /Q
)

変数が多過ぎて分かりにくいのですが、そこはご容赦を。基本的にはsysobjects , syscolumns からデータを取得します。