DB内のテーブルをすべて削除する

あまりにテーブル数があるDBのテストデータ作成で、いったんすべてのテーブルをクリアしたいという要望が出てきた。

で、すべてのテーブルをテーブル情報からとってきてがっ！と消すストアドを作ってみた。

さらに、ただ消すだけだと、外部キー制約に引っかかるので、いったん外部キー制約を無効にするためのストアドを拾った。


が、しかし。

外部キー制約を無効にしたままエラーで落ちてしまったら恐ろしいため、この案は却下。

もしかして、今後使えるかもしれないので残すことにする。

以下、一括ですべてのテーブルをクリアするストアド

    DECLARE @tableName NVARCHAR(255)
    DECLARE @schemaName NVARCHAR(255)
    DECLARE TABLE_LIST CURSOR FOR
        SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES

    SET NOCOUNT ON

    OPEN TABLE_LIST

    FETCH NEXT FROM TABLE_LIST
    INTO @tableName, @schemaName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        EXECUTE ('TRUNCATE TABLE ' + @schemaName + '.' + @tableName)

        FETCH NEXT FROM TABLE_LIST
        INTO @tableName, @schemaName
    END

    CLOSE TABLE_LIST
    DEALLOCATE TABLE_LIST

すべてのスキーマを対象にしているので、最初のテーブル一覧を取得するところで適当に絞ってね！

次に、外部キーをがっとオンオフ

CREATE PROCEDURE pr_Disable_Triggers_v2
    @disable BIT = 1
AS
    DECLARE
        @sql VARCHAR(500),
        @tableName VARCHAR(128),
        @tableSchema VARCHAR(128)

    -- List of all tables
    DECLARE triggerCursor CURSOR
        FOR
    SELECT
        t.TABLE_NAME AS TableName,
        t.TABLE_SCHEMA AS TableSchema
    FROM
        INFORMATION_SCHEMA.TABLES t
    ORDER BY
        t.TABLE_NAME,
        t.TABLE_SCHEMA

    OPEN triggerCursor

    FETCH NEXT FROM triggerCursor
    INTO @tableName, @tableSchema

    WHILE ( @@FETCH_STATUS = 0 )
        BEGIN
            IF @disable = 1
                SET @sql = 'ALTER TABLE ' + @tableSchema
                    + '.[' + @tableName + '] DISABLE TRIGGER ALL'
            ELSE
                SET @sql = 'ALTER TABLE ' + @tableSchema
                    + '.[' + @tableName + '] ENABLE TRIGGER ALL'

            PRINT 'Executing Statement - ' + @sql

            EXECUTE ( @sql )
            FETCH NEXT FROM triggerCursor
            INTO @tableName, @tableSchema
        END

    CLOSE triggerCursor
    DEALLOCATE triggerCursor

これ、実は、拾ったやつそのまま。

一応拾った場所のアドレスは以下
http://msdn.microsoft.com/ja-jp/magazine/cc163442.aspx

テーブルが大量にあるDBを使うプロジェクトでテストのときに使えそうだね。確かに。

以上！！