| MSDE FunClub |
|
最終更新日 : 2000/07/22 |
|
Microsoft Data Engine FunClub
|
Since 2000.07.22
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第２章１９０ｐ 〜 １９４ｐ掲載】 |
-- [DDL系SQL文の基礎（例題11）EX3-10.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- テーブルに設定された主キーを削除します
-- [社員]テーブルの[社員コード]主キーを削除します
-- これから操作する既定のデータベースを MySampleTest にします
USE MySampleTest
GO
-- ( 件処理されました)のメッセージの表示を抑止します
SET NOCOUNT ON
GO
-- *********************************
-- 一時テーブルが存在したら削除する
-- *********************************
USE tempdb -- OBJECTPROPERTY命令が、カレントデータベースしか調査しない
-- 接続先を一時テーブルが作成されるtempdbにする
GO
IF( (object_id('tempdb..#RS_sp_pkeys') IS NOT NULL ) AND
OBJECTPROPERTY( object_id('tempdb..#RS_sp_pkeys' ) , 'ISTABLE' ) = 1 )
BEGIN
PRINT '一時テーブルを削除しました'
DROP TABLE #RS_sp_pkeys --前回に実行時エラーが起きたら残っているかも？
END
IF( (object_id('tempdb..#RS_sp_fkeys') IS NOT NULL ) AND
OBJECTPROPERTY( object_id('tempdb..#RS_sp_fkeys' ) , 'ISTABLE' ) = 1 )
BEGIN
PRINT '一時テーブルを削除しました'
DROP TABLE #RS_sp_fkeys --前回に実行時エラーが起きたら残っているかも？
END
GO
-- これから操作する既定のデータベースを MySampleTest に戻す
USE MySampleTest
GO
-- ****************************
-- [社員]表が存在しますか？
-- ****************************
IF( (object_id('社員') IS NULL) OR
OBJECTPROPERTY( object_id('社員') , 'ISTABLE' ) <> 1 )
BEGIN
RAISERROR('社員テーブルが存在しません',16,127)
RETURN
END
GO
-- **************************************
-- [社員]テーブルに主キーが存在しますか？
-- **************************************
-- sp_pkeys ストアドプロシージャが返す結果セットの受け取り
CREATE TABLE #RS_sp_pkeys (
TABLE_QUALIFIER sysname , --テーブル識別子の名前
TABLE_OWNER sysname , --所有者の名前
TABLE_NAME sysname , --テーブルの名前
COLUMN_NAME sysname , --列名
KEY_SEQ smallint , --列のシーケンス番号
PK_NAME sysname --主キー識別子
)
-- テーブルの主キー情報を取得する
-- ストアドプロシージャ sp_pkeysの実行
INSERT INTO #RS_sp_pkeys EXECUTE sp_pkeys '社員'
-- 主キーがありますか？
IF NOT EXISTS ( SELECT PK_NAME FROM #RS_sp_pkeys )
BEGIN
DROP TABLE #RS_sp_pkeys
RAISERROR('主キーがありませんので、中止します',16,127)
RETURN
END
PRINT '社員テーブルには、主キーがあります'
GO
-- ****************************************************
-- [社員コード]列を参照している外部キー側テーブルの
-- 参照整合性制約を先に削除しなければいけません
-- ****************************************************
-- sp_fkeys ストアドプロシージャが返す結果セットの受け取り
CREATE TABLE #RS_sp_fkeys (
PKTABLE_QUALIFIER sysname , --データベースの名前
PKTABLE_OWNER sysname , --所有者名
PKTABLE_NAME sysname , --主キーのテーブル名
PKCOLUMN_NAME sysname , --主キー列の名前
FKTABLE_QUALIFIER sysname , --データベースの名前
FKTABLE_OWNER sysname , --所有者名
FKTABLE_NAME sysname , --外部キーのテーブル名
FKCOLUMN_NAME varchar(32) , --外部キー列の名前
KEY_SEQ smallint , --列のシーケンス番号
UPDATE_RULE smallint , --動作
DELETE_RULE smallint , --動作
FK_NAME sysname , --外部キー識別子(FOREIGN KEY 制約の名前)
PK_NAME sysname , --主キー
DEFERRABILITY smallint --この列がマニュアルでは抜けています!!
)
-- 社員テーブルを参照する外部キー側テーブルの情報取得
-- ストアドプロシージャ sp_fkeysの実行
INSERT INTO #RS_sp_fkeys EXECUTE sp_fkeys '社員'
-- 外部キーの情報がありますか？
IF EXISTS ( SELECT FK_NAME FROM #RS_sp_fkeys )
BEGIN
-- テーブルの名前と制約の名前
DECLARE @TBLNM sysname , @CONSTNM sysname
-- 外部キーを削除するためカーソルを定義する
DECLARE Fkeys_Cur CURSOR
FOR SELECT FKTABLE_NAME , FK_NAME FROM #RS_sp_fkeys
WHERE ( PKCOLUMN_NAME = '社員コード' )
-- カーソルを開く
OPEN FKeys_Cur
-- 先頭レコードの取得
FETCH NEXT FROM FKeys_Cur INTO @TBLNM , @CONSTNM
-- すべてのレコードを処理する
WHILE( @@FETCH_STATUS = 0 )
BEGIN
--外部キー側の参照整合性制約を削除する
EXEC( 'ALTER TABLE ' + @TBLNM + ' DROP CONSTRAINT ' + @CONSTNM )
--次のレコードへ
FETCH NEXT FROM FKeys_Cur INTO @TBLNM , @CONSTNM
END
-- カーソルを閉じる
CLOSE FKeys_Cur
-- カーソルを破棄する
DEALLOCATE FKeys_Cur
END
-- 一時テーブルはもう不要です。削除する
DROP TABLE #RS_sp_fkeys
GO
-- ****************************************************
-- [社員]テーブルの[社員コード]列の主キーを削除します
-- ****************************************************
--[社員]テーブルに主キーがないというエラーが起きていますか？
--クエリアナライザでは、エラーが起きてもこの部分が実行されます
--エラー発生時では、一時テーブルは削除済みです
IF( object_id('tempdb..#RS_sp_pkeys') IS NULL ) RETURN -- 一時テーブルが存在しません
-- バッチを終えます
--[社員]テーブルの主キーの制約名を取得
DECLARE @PName sysname
SELECT @PName = PK_NAME FROM #RS_sp_pkeys
--主キーを削除します
--ALTER TABLE文に直接変数名を書くことはできません
--SQL文を作成し、EXECで実行します
EXEC( 'ALTER TABLE 社員 DROP CONSTRAINT ' + @PName )
PRINT '[社員]テーブルの主キーを削除しました'
-- 一時テーブルはもう不要です。削除する
DROP TABLE #RS_sp_pkeys
GO
