MSDE FunClub 現在までのアクセスカウント数 最終更新日 : 2000/11/03
Microsoft Data Engine FunClub
Since 2000.11.03
SQLServer7.0/MSDE 完全トレーニングテキスト（下巻）
【第２２章４３１ｐ 〜 ４３６ｐ掲載】 
-- [SQL22_01.SQL]
--             SQLServer7.0  Transact-SQL言語
--             日本技術ソフト開発  堀川　明
--            http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
--   Access テーブルエクスポート機能によって作成された
--   テーブルに対して、制約を設定するストアドプロシージャです
--

-- ( 件処理されました)のメッセージの表示を抑止します
    SET NOCOUNT ON
GO

--  接続先データベースの定義
    USE MySampleTest
GO

-- **********************************************************
--   指定されたテーブルの指定された列に NOT NULL 属性を設定
-- **********************************************************
    IF( (OBJECT_ID('ALTTBL_NotNULL') IS NOT NULL) AND
         OBJECTPROPERTY(OBJECT_ID('ALTTBL_NotNULL'),'IsProcedure') = 1 )
      BEGIN
             DROP PROCEDURE ALTTBL_NotNULL
      END
GO
    CREATE PROCEDURE ALTTBL_NotNULL
               @tblnm  sysname ,       --テーブルの名前
               @colnm  sysname ,       --列の名前
               @type   sysname         --データ型
   AS
      SET NOCOUNT ON
      DECLARE @cmd varchar(256)
      --ALTER TABLE文を組み立てる
      SELECT @cmd = 'ALTER TABLE [' + @tblnm + '] ' +
                    'ALTER COLUMN [' + @colnm + '] ' + @type + ' NOT NULL'
      --RAISERROR('%s',0,1,@cmd)
      EXEC( @cmd )
GO


-- **********************************************************
--     指定されたテーブルの指定された列に CHECK制約を設定
-- **********************************************************
    IF( (OBJECT_ID('ALTTBL_SetCHECK') IS NOT NULL) AND
         OBJECTPROPERTY(OBJECT_ID('ALTTBL_SetCHECK'),'IsProcedure') = 1 )
      BEGIN
             DROP PROCEDURE ALTTBL_SetCHECK
      END
GO
    CREATE PROCEDURE ALTTBL_SetCHECK
               @tblnm  sysname ,       --テーブルの名前
               @colnm  sysname ,       --列の名前
               @check  varchar(256)    --制約式の内容
   AS
      SET NOCOUNT ON
      DECLARE @cmd varchar(256)
      DECLARE @cnm sysname
      --CHECK制約名
      SELECT @cnm = '[CK_' + @tblnm + '_' + @colnm + ']'
 
      --制約が存在していれば、それを削除する
      IF(  (OBJECT_ID(@cnm) IS NOT NULL) AND
           OBJECTPROPERTY(OBJECT_ID(@cnm),'IsCheckCnst') = 1 )
         BEGIN
               --制約削除
               SELECT @cmd = 'ALTER TABLE ' + @tblnm + ' DROP CONSTRAINT ' + @cnm
               EXEC( @cmd )
         END

      --制約作成
      SELECT @cmd = 'ALTER TABLE ' + @tblnm + ' ADD ' + 
                      'CONSTRAINT ' + @cnm + ' CHECK ( ' +  @check + ' )'
      --RAISERROR('%s',0,1,@cmd)
      EXEC( @cmd )
GO


-- **********************************************************
--     指定されたテーブルの指定された列に DEFAULT制約を設定
-- **********************************************************
    IF( (OBJECT_ID('ALTTBL_SetDEFAULT') IS NOT NULL) AND
         OBJECTPROPERTY(OBJECT_ID('ALTTBL_SetDEFAULT'),'IsProcedure') = 1 )
      BEGIN
             DROP PROCEDURE ALTTBL_SetDEFAULT
      END
GO
    CREATE PROCEDURE ALTTBL_SetDEFAULT
               @tblnm  sysname ,       --テーブルの名前
               @colnm  sysname ,       --列の名前
               @value  varchar(256)    --デフォルト値の値
   AS
      SET NOCOUNT ON
      DECLARE @cmd varchar(256)
      DECLARE @cnm sysname
      --DEFAULT制約名
      SELECT @cnm = '[DEFAULT_' + @tblnm + '_' + @colnm + ']'
 
      --制約が存在していれば、それを削除する
      IF(  (OBJECT_ID(@cnm) IS NOT NULL) AND
           OBJECTPROPERTY(OBJECT_ID(@cnm),'IsDefaultCnst') = 1 )
         BEGIN
               --制約削除
               SELECT @cmd = 'ALTER TABLE ' + @tblnm + ' DROP CONSTRAINT ' + @cnm
               EXEC( @cmd )
         END

      --制約作成
      SELECT @cmd = 'ALTER TABLE ' + @tblnm + ' ADD ' + 
                      'CONSTRAINT ' + @cnm + ' DEFAULT  ' +  @value + ' FOR ' + @colnm
      --RAISERROR('%s',0,1,@cmd)
      EXEC( @cmd )
GO


-- **************************************************************
--     指定されたテーブルの指定された列に Primary Key制約を設定
-- **************************************************************
    IF( (OBJECT_ID('ALTTBL_SetPrimaryKey') IS NOT NULL) AND
         OBJECTPROPERTY(OBJECT_ID('ALTTBL_SetPrimaryKey'),'IsProcedure') = 1 )
      BEGIN
             DROP PROCEDURE ALTTBL_SetPrimaryKey
      END
GO
    CREATE PROCEDURE ALTTBL_SetPrimaryKey
               @tblnm  sysname ,       --テーブルの名前
               @colnm  sysname         --列の名前
   AS
      SET NOCOUNT ON
      DECLARE @cmd varchar(256)
      DECLARE @cnm sysname
      --PrimaryKey制約名
      SELECT @cnm = '[PK_' + @tblnm + ']'
 
      --制約が存在していれば、それを削除する
      IF(  (OBJECT_ID(@cnm) IS NOT NULL) AND
           OBJECTPROPERTY(OBJECT_ID(@cnm),'IsPrimaryKey') = 1 )
         BEGIN
               --制約削除
               SELECT @cmd = 'ALTER TABLE ' + @tblnm + ' DROP CONSTRAINT ' + @cnm
               EXEC( @cmd )
         END

      --制約作成
      SELECT @cmd = 'ALTER TABLE ' + @tblnm + ' ADD ' + 
                      'CONSTRAINT ' + @cnm + ' PRIMARY KEY ( [' + @colnm + '] )'
      --RAISERROR('%s',0,1,@cmd)
      EXEC( @cmd )
GO


-- *****************************************************************
--    指定されたテーブルの指定された列に インデックスを作成する
-- *****************************************************************
    IF( (OBJECT_ID('ALTTBL_MakeIDX') IS NOT NULL) AND
         OBJECTPROPERTY(OBJECT_ID('ALTTBL_MakeIDX'),'IsProcedure') = 1 )
      BEGIN
             DROP PROCEDURE ALTTBL_MakeIDX
      END
GO
    CREATE PROCEDURE ALTTBL_MakeIDX
               @tblnm  sysname ,       --テーブルの名前
               @colnm  sysname ,       --列の名前
               @type   int             --0:重複あり  1:UNIQUE
   AS
      SET NOCOUNT ON
      DECLARE @cnm sysname

      --Indexの名前
      SELECT @cnm = 'IDX_' + @tblnm + '_' + @colnm
 
      DECLARE @cmd varchar(256)
 
      --INDEXが存在していれば、それを削除する
      IF EXISTS (SELECT name FROM sysindexes WHERE( name = @cnm ) )
            EXEC('DROP INDEX ' + @tblnm + '.' + @cnm )
      
      --インデックスを作成する
      IF( @type = 0 )
         SELECT @cmd = 'CREATE INDEX  ' + @cnm + ' ON ' + @tblnm + '( ' + @colnm + ' )' 
      ELSE
         SELECT @cmd = 'CREATE UNIQUE INDEX  ' + @cnm + ' ON ' + @tblnm + '( ' + @colnm + ' )' 
      

      --RAISERROR('%s',0,1,@cmd)
      EXEC( @cmd )
GO


-- *****************************************************************
--                     参照整合性制約を設定する
--                         【１対多の設定】
-- *****************************************************************
    IF( (OBJECT_ID('ALTTBL_SetRelation') IS NOT NULL) AND
         OBJECTPROPERTY(OBJECT_ID('ALTTBL_SetRelation'),'IsProcedure') = 1 )
      BEGIN
             DROP PROCEDURE ALTTBL_SetRelation
      END
GO
    CREATE PROCEDURE ALTTBL_SetRelation
               @tblnmPK  sysname ,     --主キー側のテーブルの名前
               @colnmPK  sysname ,     --主キー側の列の名前
               @tblnmFK  sysname ,     --外部キー側のテーブルの名前
               @colnmFK  sysname       --外部キー側の列の名前
   AS
      SET NOCOUNT ON
      DECLARE @cnm sysname
      DECLARE @cmd varchar(256)


      --参照整合性の名前を定義
      SELECT @cnm = 'FK_' +  @tblnmFK + '_' + @tblnmPK

      --参照整合性が存在していれば、それを削除する
      IF( (OBJECT_ID(@cnm) IS NOT NULL) AND
          OBJECTPROPERTY(OBJECT_ID(@cnm) ,'IsForeignKey') = 1 )
        BEGIN
              --制約削除
               SELECT @cmd = 'ALTER TABLE ' + @tblnmFK + ' DROP CONSTRAINT ' + @cnm
               EXEC( @cmd )
         END

      --参照整合性制約作成
      SELECT @cmd = 'ALTER TABLE ' + @tblnmFK + ' ADD ' + 
                    'CONSTRAINT ' + @cnm + ' FOREIGN KEY ( [' + @colnmFK + '] ) REFERENCES ' +
                    @tblnmPK + ' ( [' + @colnmPK + '] )'
 
      --RAISERROR('%s',0,1,@cmd)
      EXEC( @cmd )
GO
技術評論社の書籍ガイドへ
下巻：ISBN4-7741-0966-5

ウィンドウを閉じる

（株）日本技術ソフト開発　責任編集：堀川　明
 MSDE FunClubに関するご意見・ご要望等ございましたら、 msdefun@horikawa.ne.jp　までご連絡下さい。
HOME: http://www.horikawa.ne.jp/msde/

MSDE FunClubの運営は、マイクロソフト社とは一切の関係はありません