Dropping Tables In SQL Server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Dropping Tables In SQL Server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-11 : 10:55:43

Francis writes "Hi there,

I am using SQL Server 2000 (though I guess this applies to all versions). I need to drop and recreate tables quite often for testing and I use generated ddl scripts to do this.

In Oracle, there is a CASCADE CONSTRAINT keyword to allow the dropping of tables regardless of constraints (foreign key or otherwise).
e.g.
DROP TABLE Entity1 CASCADE CONSTRAINT;

Not sure about DB2, I think you do not need this keyword.

Is there an equivalent keyword that I can add in SQL Server when dropping tables to allow me to do this or do I have generate code to drop all of the constraints first before dropping the tables. At the moment I am getting the usual SQL Server error that I am not allowed to drop the table due to the constraints present.

Regards."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-11 : 11:26:48

If you're doing this kind of testing it's really not a good idea to mix test tables with production tables. You should make copies of all of the related tables, declare the foreign keys on the copies, and rewrite the code to drop the child tables first, then drop the parent/topmost table.

You can find the constraint names in the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view, and use it to generate code to drop the constraints, if you can't go the full-table-dropping route.

fgavin
Starting Member

3 Posts

Posted - 2002-03-11 : 12:23:22

Hi there,
Thanks for the reply. There is no integration between the testing and production tables. These test scripts that I am (attempting) to write are used to test various aspects of a much larger project, and all of the test stuff is kept separate from the production area.

I guess what I am really looking for is an example (or a pointer to a place where I could find an example of this) of how to access this INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view, and write a ddl script that will drop all of table constraints before dropping the table. The full process flow for me would be:
1. Find all the constraints for the given table.
2. Drop these constraints
3. Drop the table.
4. Recreate the table
5. Recreate the constraints.

The last three are already scripted, it is the first 2 that I am having the problems with.

Thanks,

Francis.

quote:

If you're doing this kind of testing it's really not a good idea to mix test tables with production tables. You should make copies of all of the related tables, declare the foreign keys on the copies, and rewrite the code to drop the child tables first, then drop the parent/topmost table.

You can find the constraint names in the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view, and use it to generate code to drop the constraints, if you can't go the full-table-dropping route.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-11 : 12:33:30

Check out these dynamic SQL articles:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

These should give you a good start, also try the other dynamic SQL articles:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=dynamic

You can use it to query the INFORMATION_SCHEMA view and construct the DROP CONSTRAINT statement dynamically, then execute it.

Question: if you are recreating the constraints, it suggests that you know them in advance...why not script the entire process? Are you dynamically adding constraints?

fgavin
Starting Member

3 Posts

Posted - 2002-03-11 : 13:10:13

I generate the DDL scripts all at once.

Then use a make file to run all of the various ddl scripts (i.e. Tables, Foreign Keys, Primary Keys) against the database of choice.

Previously, the simple DROP Table command was effective enough for Oracle and DB2 without having to worry about constraints. So I used to run the Tables file followed by the others in a specific order.

Now I need to worry about these constraints, so I guess i need an example of ddl script that I could alter to do this for me.

i.e. Tables SQL file looks like this:
DROP TABLE TABLE1;
CREATE TABLE TABLE1(
idNumber CHAR(8) NOT NULL,
booleanType CHAR(1) NOT NULL);
GO

So I guess I need something to put before this DROP TABLE command to take care of the constraints.

Thanks.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-11 : 18:09:50

I don't often suggest people go use Enterprise Manager, but it does have a nice Generate SQL Scripts option (Right-click database, All Tasks... Generage SQL Scripts). There you can choose options to include DROP commands, dependent objects, etc., etc. Would this work for you?

------------------------
GENERAL-ly speaking...

fgavin
Starting Member

3 Posts

Posted - 2002-03-12 : 05:47:48

Hi there,

I had a look at that SQL Script generator in the Enterprise Manager. As far as I can ascertain, the script generated only checks to see if the table exists before attempting to drop it.

I guess all I need is an example of a DDL script that uses some stored procedure (if there is a standard one) to get all of the constraints for any given table and then drops them one by one before dropping the table. An example of the actual syntax of this DDL script would be great (i.e. how to use a stored procedure to get this information etc.)

1. Get the constraints for any given table
2. Drop the constraints

Is there a stored procedure that already exists that we could pass in the table name and get a list of constraints back. How do I use a stored procedure in this way (i.e. I have no experience in writing DDL script to do this or what form/syntax it would take in a standard DDL script file).

Thanks,

Francis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources