【SQLSERVER】システムデータベースに関するテクニック集

SQLServer独自のシステムデータベースを使用することでデータベースオブジェクトを自由に参照することができます。
またテーブルのデータを簡単にバックアップ作成する方法も紹介します。

データベースオブジェクトの存在を調べる

「SELECT * FROM sys.objects」よりデータベースに登録しているオブジェクトを取得することができます。
これを利用することで任意のテーブルやストアドプロシージャを検索することが可能です。

例えば、登録したストアドプロシージャが既にデータベースにあれば何もせず、
なければ登録するという具合にエラーを発生させずにSQLクエリを作ることが可能です。

実務でもかなり使うので覚えておくと便利です！

IF (
  EXISTS
    (
      SELECT	* 
      FROM	sys.objects
      WHERE name = 'sq_test1'
      AND type_desc = 'SQL_STORED_PROCEDURE'
    )
  )
BEGIN
  -- 該当ストアドプロシージャを削除
  DROP PROCEDURE sq_test1;
END
GO

-- ストアドプロシージャを作成します
CREATE PROCEDURE sq_test1
AS
BEGIN
  SELECT  *
  FROM	TASK_TBL;
END
GO

仮に「sp_test1」というストアドプロシージャを作成する場合、ストアドプロシージャを作成する前に一度削除して、再度作るようにします。
このようにすることで、ストアドプロシージャを更新した際や新規にデータベースに登録するときなど、常に新しいストアドプロシージャを登録することができます。

テーブルを参照しているデータベースオブジェクトを調べる

テーブルを参照しているオブジェクトなどを調べることができます。
「SELECT * FROM sys.sysdepends」
これを実行すると、idとdepidを取得できます。
これらはそれぞれオブジェクトの従属関係を示します。

-- データベースオブジェクトの従属関係を表示
SELECT	DISTINCT
    (SELECT name FROM sys.objects AS o WHERE o.object_id = d.id) AS 主,
    (SELECT name FROM sys.objects AS o WHERE o.object_id = d.depid) AS 従属
FROM sys.sysdepends AS d

このようにすると、テーブル（従属）を参照しているオブジェクト（主）を検索することができます。
したがって、テーブルを参照しているストアドプロシージャを見つけることができます。

テーブルのカラムや定義に変更が入るときの影響調査に使用できるのでかなり便利です。

楽にテーブルのバックアップを取る方法

タスクからバックアップを取ればデータベースのバックアップを作成することは簡単です。
しかし、バックアップをするごとにファイルが増えたり、ログ作成に時間がかかったりします。
また1テーブルだけというバックアップファイルを作成することはできないため、かなり不便です。

このようなときはSELECT * INTO ///]を使用することで容易にテーブルをコピーできます。
私がよく使うサンプル文を作成しました。

-- 「コピー先」テーブルの存在チェック
IF (EXISTS (SELECT * FROM sys.tables WHERE name = 'CopyShainTBL' ))
BEGIN
  --
  PRINT N'「CopyShainTBL」テーブルはすでに存在します。';
END
ELSE
BEGIN
  -- コピー先からコピー元の内容追加
  SELECT * 
  INTO CopyShainTBL
  FROM ShainTBL;

  -- 作成したらメッセージを表示
  PRINT N'「CopyShainTBL」テーブルを作成しました。';
END

またFROM句の後にWHERE句でコピーする記録を絞り込むことができます。

おまけ（一時テーブル）

一時テーブルを作成することで余計なテーブルを用意することなく効率よくSQLクエリを実行することができます。
「＃」がつくSQLクエリを見たことがありませんか？

それが一時テーブルです。
そのクエリが実行されている間だけのテーブルですので、他のセッションからそのテーブルへのアクセスはできません。
セッションIDは「@@spid」で取得することができます。

PRINT @@spid

CREATE TABLE #work
(
  id INT,
  name VARCHAR(10)
);

SELECT * FROM #work

このようにするとこのspidで参照できる#workを作成することができます。
また余談ですが、「##work」とするとグローバル化することができるので、このspid以外にも参照が可能です。

しかし、グローバルは扱いが難しいためあまりお勧めできません…。
バグの原因にもなりますしー。

また、私がスクリプトやストアドプロシージャ内で一時テーブルを利用する場合は以下のような感じで書きます。

-- SQLを格納する変数を定義
DECLARE @sql VARCHAR(8000)
SET @sql = '';

-- テンポラリテーブルを作成するSQLを定義
SET @sql = @sql + ' CREATE TABLE ##work' + CONVERT(varchar, @@SPID);
SET @sql = @sql + ' (';
SET @sql = @sql + '   id	INT';
SET @sql = @sql + ' , name	VARCHAR(10)';
SET @sql = @sql + ' );';

-- 構成したSQL文（サンプルなので追加）
PRINT @sql;

-- SQLを格納した変数を実行
EXECUTE(@sql);

基本的にスクリプト内でSQLを動的に発行して使います。
また、重複しないように@@SPIDよりセッションごとに動的にテーブルを用意します。

このようにすることでテーブル競合を防ぎます。
ぜひ使ってみてください！