473,372 Members | 1,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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 8653
[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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Darshak Shah | last post by:
Hi, * Question background : My DB is in ArchiveLog mode. I have TS in ReadOnly mode. As i know, - Oracle allows to DROP table resides in ReadOnly TS (& other objects also) even though that...
1
by: Radu | last post by:
Hi. Thru a sproc, I drop & re-create some temp tables. When I call that sproc from the client, though, I cannot drop the tables. I need to allow the user, say "Alex", to drop/create tables...
2
by: James | last post by:
Can anyone please shed some light on the following... I have a framework that uses dynamically created tables, named using an incremental "attribute set ID", as follows: attrdata_1 attrdata_2...
2
by: tom horner | last post by:
Any ideas on why drop and create alias statements would take a long time? We recently went through an upgrade of our production database, which included alter statements to table structures,...
11
by: RdR | last post by:
Hi, I am using Q Replication, I need to set to logging to capture changes on a table but the table has more than 18 characters for the name, I looked at the docs, it mentioned that table names...
2
by: Mark Doerbandt | last post by:
Hi, I need to validate a XML document where some of the element names are unnknown. <xs:any processContents="lax"/> or "skip" allows me to do so. But: I want to validate those elements -...
2
by: brstowe | last post by:
Firstly I consider myself quite an experienced SQL Server user, and am now using SQL Server 2005 Express for the main backend of my software. My problem is thus: The boss needs to run reports;...
1
by: ajay kumar chada | last post by:
Can anybody help me in finding error in the following SP please, (actullay i need to drop the tables which had been created six months ago CREATE PROCEDURE TGNSX00.TAB_DROP_PROC(OUT OUT_SQLCODE...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.