Remove tables from ms SQL Server

-- Note: <br/> -- 1. to delete the table table_name, you must first determine whether the table is being referenced by other tables in the database. <br/> -- 2. if it is not referenced, you can directly drop table table_name; otherwise, you must first Delete the constraints of the referenced table and then drop table table_name. <br/> -- 3. in addition, you do not need to consider whether table_name is referencing other tables. </P> <p> -- create a master table <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> -- create a slave Table 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> -- create a slave Table 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> -- generate a T-SQL statement to delete the foreign key constraint from the table <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> -- execute the generated T-SQL statement <br/> execute sp_executesql @ stmt <br/> -- ======================================== ==========</P> <p> -- delete a master table <br/> drop table tparent <br/> -- ========== ========================================= </P> <p> -- test <br/> insert into tchild1 values (9999, n' child1record9999') <br/> insert into tchild2 values (9999, n' child2record999999 ') <br/> select * From tchild1 <br/> select * From tchild2 <br/> -- ============== ======================================