SQL to drop all tables that begin with a certain string

[dgillz]

Is this possible? I need to drop all tables that begin with the string "gbkmutout". There are about 700 of them.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[ptheriault]

Take a look at this post. You will just need to add
a where clause for your tables.

thread183-1365242

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[dgillz]

Which set of code, the first or second set posted on the thread in question?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[ptheriault]

The first set of code should work but you should read the complete post to see if they made any changes you would need.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[dgillz]

I don't really understand where to put the WHERE clause. In the code there is already this:

IF LEFT (@TableName, 1) != 'V'

Should't I just change this to my string? No where clause needed?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[ptheriault]

Your where clause would go here.

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    DECLARE @TableName varchar(800);
    DECLARE @strSQL    varchar(8000);
    DECLARE c1 CURSOR FOR
        SELECT TABLE_NAME
          FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_TYPE = 'BASE TABLE'
          AND TABLE_NAME like 'gbkmutout%'

...rest of code

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[dgillz]

I am getting an error on every table:

Cannot drop the table 'GbkmutOutStandingView_62426AM', because it does not exist in the system catalog.

What does this mean?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[ptheriault]

It means that there is not a table named GbkmutOutStandingView_62426AM.

Could someone have already dropped it?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[dgillz]

Where is it getting that table name? I didn't hard code it, it is coming from the like statement.

So how could it not be in the database?

Also if I open up SQL enterprise manager, I can see the table.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry