SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Dropping multiple tables in SQL Server 2000/2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

libran1783
Starting Member

3 Posts

Posted - 11/21/2008 :  05:12:32  Show Profile  Reply with Quote
Hi

I used the below to drop tables having common prefix like 'GPN'

DECLARE @id varchar(255) DECLARE @dropCommand varchar(255) DECLARE tableCursor CURSOR FOR SELECT name FROM sys.tables WHERE NAME LIKE 'GPN%' OPEN tableCursor FETCH next FROM tableCursor INTO @id WHILE @@fetch_status=0 BEGIN SET @dropcommand = N'drop table ' + @id EXECUTE(@dropcommand) FETCH next FROM tableCursor INTO @id END CLOSE tableCursor DEALLOCATE tableCursor

The point is this works in SQL2005 but not in SQL2000

In SQL 2000 we get errors like ' dropcommand' variable not declared.

Please let me know how i can solve this.

Purushothama

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/21/2008 :  05:19:04  Show Profile  Reply with Quote
you dont have sys.tables in sql 2000. use sysobjects instead
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/21/2008 :  05:19:59  Show Profile  Reply with Quote
or use
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
Go to Top of Page

libran1783
Starting Member

3 Posts

Posted - 11/21/2008 :  05:38:16  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

or use
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'



Thanks for the reply.
Tried :

USE tempdb
DECLARE @id varchar(255) DECLARE @dropCommand varchar(255) DECLARE tableCursor CURSOR FOR SELECT table_name FROM Information_Schema.TABLES WHERE table_name LIKE 'GPN%' OPEN tableCursor FETCH next FROM tableCursor INTO @id WHILE @@fetch_status=0 BEGIN SET @dropcommand = N'drop table ' + @id EXECUTE(@dropcommand) FETCH next FROM tableCursor INTO @id END CLOSE tableCursor DEALLOCATE tableCursor

But ended up wit the same erros:

Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@dropcommand'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@dropcommand'.

let me know your views
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 11/21/2008 :  07:35:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Just make use of the result
SELECT 'DROP TABLE '+table_name FROM Information_Schema.TABLES
WHERE table_name LIKE 'GPN%'


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/21/2008 :  08:50:17  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Just make use of the result
SELECT 'DROP TABLE '+table_name FROM Information_Schema.TABLES
WHERE table_name LIKE 'GPN%'


Madhivanan

Failing to plan is Planning to fail


remember to add AND table_type='BASE TABLE' if you need to drop only tables & exclude any views.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 11/21/2008 :  08:56:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

Just make use of the result
SELECT 'DROP TABLE '+table_name FROM Information_Schema.TABLES
WHERE table_name LIKE 'GPN%'


Madhivanan

Failing to plan is Planning to fail


remember to add AND table_type='BASE TABLE' if you need to drop only tables & exclude any views.


Yes it is

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

libran1783
Starting Member

3 Posts

Posted - 11/24/2008 :  01:48:00  Show Profile  Reply with Quote
thanks guys for alternate solution,
but i would like to use the thing wat I gave in the begining,Just wanted to know where I need to modify so that It works on SQL2K as well
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/24/2008 :  01:53:56  Show Profile  Reply with Quote
quote:
Originally posted by libran1783

thanks guys for alternate solution,
but i would like to use the thing wat I gave in the begining,Just wanted to know where I need to modify so that It works on SQL2K as well


In sql 2000 use systables instead of sys.tables.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000