469,329 Members | 1,451 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,329 developers. It's quick & easy.

Drop tables with unknown names and unknown quantity

Amy
This is what I want to do:

1. Delete all tables in database with table names that ends with a
number.
2. Leave all other tables in tact.
3. Table names are unknown.
4. Numbers attached to table names are unknown.
5. Unknown number of tables in database.

For example:
(Tables in database)
Account
Account1
Account2
Binder
Binder1
Binder2
Binder3
.......

I want to delete all the tables in the database with the exception
of Account and Binder.

I know that there are no wildcards in the "Drop Table tablename"
syntax. Does anyone have any suggestions on how to write this sql
statement?

Note: I am executing this statement in MS Access with the
"DoCmd.RunSQL sql_statement" command.

Thanks for any help!
Jul 20 '05 #1
2 8472
[posted and mailed, please reply in news]

Amy (am*********@hotmail.com) writes:
1. Delete all tables in database with table names that ends with a
number.
2. Leave all other tables in tact.
3. Table names are unknown.
4. Numbers attached to table names are unknown.
5. Unknown number of tables in database.


The simplest way is to say:

SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9]'

and then cut and paste and run the result. You would do this from
Query Analyzer.

If you would like to do it programmatically, because you are doing
it routinely, you could set up a cursor over sysobjects, and then
use dynamic SQL to drop the tables:

DECLARE @tbl sysname
DECLARE drop_tbl_cur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE name like '%[0-9]'
OPEN CURSOR drop_tbl_cur
WHILE 1 = 1
BEGIN
FETCH drop_tbl_cur INTO @tbl
IF @@fetch_status <> 0
BREAK
EXEC ('DROP TABLE ' + @tbl)
END
DEALLOCATE drop_tbl_cur

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
MC
I would also add ' AND xtype = 'U' ' in the where statement so that it
includes only user tables. This way it would include any object in the
statement and you would get errors when trying to execute.
it would look something like this:
SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9] and
xtype = 'U'

MC

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
[posted and mailed, please reply in news]

Amy (am*********@hotmail.com) writes:
1. Delete all tables in database with table names that ends with a
number.
2. Leave all other tables in tact.
3. Table names are unknown.
4. Numbers attached to table names are unknown.
5. Unknown number of tables in database.


The simplest way is to say:

SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9]'

and then cut and paste and run the result. You would do this from
Query Analyzer.

If you would like to do it programmatically, because you are doing
it routinely, you could set up a cursor over sysobjects, and then
use dynamic SQL to drop the tables:

DECLARE @tbl sysname
DECLARE drop_tbl_cur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE name like '%[0-9]'
OPEN CURSOR drop_tbl_cur
WHILE 1 = 1
BEGIN
FETCH drop_tbl_cur INTO @tbl
IF @@fetch_status <> 0
BREAK
EXEC ('DROP TABLE ' + @tbl)
END
DEALLOCATE drop_tbl_cur

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Darshak Shah | last post: by
2 posts views Thread by James | last post: by
2 posts views Thread by Mark Doerbandt | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.