October 25, 2001 at 11:36 am
How can you script a query to drop all tables in a Database that start with the letter "L"?
Rob DeMotsis
Sr. SQL Server DBA
October 25, 2001 at 2:24 pm
Probably a cursor is easiest, just open it with a select that gives you the table names that match. You could also use sp_msforeachtable. I'd proceed cautiously!
Andy
October 25, 2001 at 7:23 pm
You should never need to use a cursor and it is usually the worst way.
In this case you can just
select 'drop table ' + name from sysobjects where type = 'U' and name like 'L%'
(I think) run it - copy the output to another query window and run it.
If you want to do it in an sp then something like
declare @s-2 varchar(128), @maxs varchar(128), @cmd varchar(500)
select @s-2 = '', @maxs = max(name) from sysobjects where type = 'U' and name like 'L%'
while @s-2 < @maxs
begin
select @s-2 = min(name) from sysobjects where type = 'U' and name like 'L%' and name > @s-2
select @cmd = 'drop table ' + @s-2
exec (@cmd)
end
Cursors never.
DTS - only when needed and never to control.
October 25, 2001 at 8:10 pm
Why would a cursor be the worst way? If you solve the problem as true set based operation I'd agree, but no matter how you rephrase it, you're really doing row by row processing arent you?
Andy
October 29, 2001 at 3:49 pm
You will end up doing single row processing but as soon as you use the explicit cursor you are limitting yourself to processing a single row from then on. Usually I would rather use a temp table so that it gives the possibility of set based operations until the final loop.
In this case there is no need for a cursor as you already have a unique field to work on. If you are worried about multple scans of the system tables just put the names into a temp table.
My main complaint about cursors is that people use them too readily (they are never needed) and they stop you following the structure of the database.
My signature on another forum is 'Cursors are useful if you don't know sql'.
I never allow people working for me to code them.
Cursors never.
DTS - only when needed and never to control.
October 29, 2001 at 4:07 pm
I like your first solution, the one where you concatenate the 'drop table' with the table name - quick and dirty, but effective.
We'll have to agree to disagree about cursors, in part at least. I agree that people tend to over use, abuse, etc, cursors, especially if they are coming from a row processing background. But to say that you should never use them overlooks a powerful tool.
There are a couple key points to me. One, how often do you run the code? If it runs once a day and doesn't bring the machine down, does it matter that much how you code it? I do agree that if you can solve it using set based you should, if you can figure it out in a reasonable amount of time. If you cant, solve the problem!
The other is, how maintainable is the code. Cursors are clear and easy to read, many set based solutions - especially ones designed to "fake" cursors - are bears to read.
Keep in mind, I am NOT advocating cursors all the time, abandoning performance, etc. When I see someone use a cursor, I look to see why and how often. If they are clearly applying a cursor based solution because they just dont "think" set, I try to point them in the right direction.
I mean no disrepect - we appreciate each of our readers who takes the time to contribute to these discussions, even the ones who disagree with us!
Andy
October 29, 2001 at 4:40 pm
Personally I hate cursors for doing work, but they are extremely handy in "one time" situations. The nature of this type of problems doesn't really work well in any situation and either a loop or a cursor will work.
Personally I think a cursor is better for this because it is quick and dirty and works well. If this is a "trim all L tables" every 10 minutes type of problem, then I would probably lean towards the other solution.
I actually use all 3 of these techniques in places. You have to choose the tool to fit the situation.
Steve Jones
October 29, 2001 at 4:45 pm
>> I mean no disrepect.
Chances of me ever taking it are very slim - hope you're as thick skinned (/confident).
>> especially if they are coming from a row processing background.
That's exactly the sort of person I want to teach.
>> The other is, how maintainable is the code. Cursors are clear and easy to read, many set based solutions - especially ones designed to "fake" cursors - are bears to read.
would disagree with that
a cursor is
declare cursor
loop through rows
get data into variables
do the processing
end
You can duplicate that same structure with a temp table - agree it's not always to much benefit though - but you do always have all the data available so that if you come up with a change in the future you can slap in some set based processing.
It used to be an easy decision as cursors were very buggy and slow and changed implementation with releases and even service packs - unfortunately this argument doesn't really apply anymore.
You have an argument for using cursors for things like calling an SP with each row of a resultset (but this probably has a temp table anyway so no need for a cursor), ddl/dbcc statements as here - but is this a general design problem that the programmer is trying to get round? I'm more concerned about using them when they are wholly innapropriate (we've all seen it - I know I've never done it because I've only ever coded one) that's why I force people to think of another way.
Cursors never.
DTS - only when needed and never to control.
December 8, 2002 at 6:21 pm
quote:
How can you script a query to drop all tables in a Database that start with the letter "L"?
I think the INFORMATION_SCHEMA views would be a more stable way to select table names. The other contributions are great, just change the source of the list of names to something supported. You will have less trouble likely in the future.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply