在 MS SQL Server 中刪除表

-- 說明:<br />-- 1. 要刪除表 table_name, 須先判斷該表是否正被資料庫中其它表所引用.<br />-- 2. 如果未被引用, 可直接 DROP TABLE table_name; 否則必須先刪除參考資料表的約束, 再 DROP TABLE table_name.<br />-- 3. 另外, table_name 是否正引用其它表的情況無須考慮. </p><p>-- 建立主表<br />CREATE TABLE tParent<br />(<br /> tParent_Col1 int NOT NULL CONSTRAINT pkParent PRIMARY KEY,<br /> tParent_Col2 nvarchar(50) NOT NULL CONSTRAINT uktParent UNIQUE<br />) </p><p>INSERT INTO tParent VALUES (1, N'ParentRecord1')<br />INSERT INTO tParent VALUES (2, N'ParentRecord2')<br />INSERT INTO tParent VALUES (3, N'ParentRecord3')<br />SELECT * FROM tParent<br />-- ===========================================</p><p>-- 建立從表 1<br />CREATE TABLE tChild1<br />(<br /> tChild1_Col1 int NOT NULL CONSTRAINT fkChild1Parent FOREIGN KEY REFERENCES tParent (tParent_Col1),<br /> tChild1_Col2 nvarchar(50) NOT NULL<br />) </p><p>INSERT INTO tChild1 VALUES (2, N'Child1Record1')<br />INSERT INTO tChild1 VALUES (3, N'Child1Record2')<br />INSERT INTO tChild1 VALUES (1, N'Child1Record3')<br />INSERT INTO tChild1 VALUES (2, N'Child1Record4')<br />SELECT * FROM tChild1<br />-- =========================================== </p><p>-- 建立從表 2<br />CREATE TABLE tChild2<br />(<br /> tChild2_Col1 int NOT NULL,<br /> tChild2_Col2 nvarchar(50) NOT NULL CONSTRAINT fkChild2Parent FOREIGN KEY REFERENCES tParent (tParent_Col2)<br />) </p><p>INSERT INTO tChild2 VALUES (11, N'ParentRecord2')<br />INSERT INTO tChild2 VALUES (22, N'ParentRecord1')<br />INSERT INTO tChild2 VALUES (33, N'ParentRecord3')<br />INSERT INTO tChild2 VALUES (44, N'ParentRecord3')<br />SELECT * FROM tChild2<br />-- =========================================== </p><p>-- 產生刪除從表外鍵約束的 T-SQL 陳述式<br />-- 1. MS SQL Server 2000<br />DECLARE @stmt nvarchar(4000)<br />SELECT @stmt = ISNULL(@stmt + CHAR(13) + CHAR(10), '')<br /> + 'ALTER TABLE ' + OBJECT_NAME(fkeyid)<br /> + ' DROP CONSTRAINT ' + OBJECT_NAME(constid)<br />FROM sysforeignkeys<br />WHERE rkeyid = OBJECT_ID(N'tParent', N'U') </p><p>-- -- 2. MS SQL Server 2005 / 2008<br />-- DECLARE @stmt nvarchar(max)<br />-- SELECT @stmt = ISNULL(@stmt + CHAR(13) + CHAR(10), '')<br />-- + 'ALTER TABLE ' + OBJECT_NAME(parent_object_id)<br />-- + ' DROP CONSTRAINT ' + OBJECT_NAME(constraint_object_id)<br />-- FROM sys.foreign_key_columns<br />-- WHERE referenced_object_id = OBJECT_ID(N'tParent', N'U') </p><p>-- PRINT @stmt<br />-- =========================================== </p><p>-- 執行產生的 T-SQL 陳述式<br />EXECUTE sp_executesql @stmt<br />-- =========================================== </p><p>-- 刪除主表<br />DROP TABLE tParent<br />-- =========================================== </p><p>-- 測試<br />INSERT INTO tChild1 VALUES (9999, N'Child1Record9999')<br />INSERT INTO tChild2 VALUES (9999, N'Child2Record9999')<br />SELECT * FROM tChild1<br />SELECT * FROM tChild2<br />-- ===========================================