| MSDE FunClub |
|
最終更新日 : 2000/07/22 |
|
Microsoft Data Engine FunClub
|
Since 2000.07.22
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第２章１９８ｐ 〜 ２００ｐ掲載】 |
-- [DDL系SQL文の基礎（例題14）EX3-12.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- 指定されたテーブルの指定された列のCHECK制約を設定します
-- これから操作する既定のデータベースを MySampleTest にします
USE MySampleTest
GO
-- ( 件処理されました)のメッセージの表示を抑止します
SET NOCOUNT ON
GO
-- ******************************************************
-- チェック制約を行なうストアドプロシージャの登録
-- ******************************************************
IF( (object_id('SetCheckConstraint') IS NOT NULL) AND
OBJECTPROPERTY( object_id('SetCheckConstraint' ) , 'IsProcedure' ) = 1 )
BEGIN
DROP PROCEDURE SetCheckConstraint
END
GO
CREATE PROCEDURE SetCheckConstraint
@TblName sysname , -- 対象となるテーブル
@ColName sysname , -- 対象となる列の名前
@Check varchar(256) , -- 設定を行なう制約式の値
@DelFlag int -- 0:削除を行なう
-- 1:削除と登録を行なう
AS
SET NOCOUNT ON
DECLARE @Tbl_id int -- テーブルのオブジェクトID値
DECLARE @Def varchar(80)
DECLARE @cmd varchar(256) -- 動的SQL文
DECLARE @Check_nm sysname -- CHECK制約の名前
DECLARE @Check_id int -- CHECK制約のID
DECLARE @Check_info smallint -- CHECK制約が付けられた列番号
DECLARE @Check_old varchar(100) -- 削除前のCHECK制約式
--テーブル名に対応するオブジェクトIDを求める
SELECT @tbl_id = object_id( @TblName )
-- 【このテーブルに設定されているCHECK制約すべてをカーソルで取得】
-- (1)xtype = 'D 'とすると、DEFALUT値制約が取得できる
-- (2)parent_objに、テーブルIDが記憶されている
-- (3)sysobjectsのinfo列は、内部使用で説明がないが、sp_helpconstraintの
-- プログラムを読むと、テーブルの列順序番号が入っていることがわかる
DECLARE Check_CUR INSENSITIVE CURSOR
FOR SELECT name , Id , info FROM sysobjects
WHERE( ( parent_obj = @tbl_id ) AND (xtype = 'C ' ) )
FOR READ ONLY
-- カーソルを開く
OPEN Check_CUR
-- 先頭行の読み込み
FETCH NEXT FROM Check_CUR INTO @Check_nm , @Check_id , @Check_info
WHILE( @@FETCH_STATUS = 0 )
BEGIN
--テーブルレベルのCHECK制約式は、削除しません
IF( @Check_info = 0 )
BEGIN
PRINT 'テーブルレベルのCHECK制約式を見つけました。削除処理はしません'
END
ELSE
BEGIN
--指定された列に設定されたCHECK制約ですか？
IF( @ColName = COL_NAME( @tbl_id , @Check_info ) )
BEGIN
--設定前のCheck制約式の取得(syscommentsテーブル)
--colidは、制約式が4000バイト以下のときは、常に1です
SELECT @Check_old = text FROM syscomments
WHERE( ( id = @Check_id ) AND ( colid = 1 ) )
PRINT '列名=[' + @ColName + ']に付けられたCHECK制約式=[' +
@Check_old + 'を削除します'
--制約式を削除します
EXEC( 'ALTER TABLE ' + @TblName + ' DROP CONSTRAINT [' + @Check_nm + ']' )
END
END
-- 次行の読み込み
FETCH NEXT FROM Check_CUR INTO @Check_nm , @Check_id , @Check_info
END
-- カーソルを閉じる
CLOSE Check_CUR
-- カーソルを破棄する
DEALLOCATE Check_CUR
-- 削除処理だけですか？
IF( @DelFlag = 0 ) RETURN 0
-- 新しい制約式を登録する
SELECT @cmd = 'ALTER TABLE ' + @TblName + ' WITH NOCHECK ADD ' +
'CONSTRAINT ' + '[CK_' + @ColName + '] CHECK ( ' + @Check + ' )'
PRINT @cmd
EXECUTE( @cmd )
RETURN 0
GO
-- CHECK制約の設定例
-- TableName ColumnName logical_expression 0 or 1
EXEC SetCheckConstraint '社員' , '社員コード' , '社員コード>10' , 1
EXEC SetCheckConstraint '社員' , '誕生日' , '誕生日 < getdate()' , 1
GO
-- プロシージャを削除
DROP PROCEDURE SetCheckConstraint
GO
