Dropping multiple tables in SQL Server 2000/2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dropping multiple tables in SQL Server 2000/2005

Author

Topic

libran1783
Starting Member

3 Posts

Posted - 2008-11-21 : 05:12:32

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

52326 Posts

Posted - 2008-11-21 : 05:19:04

you dont have sys.tables in sql 2000. use sysobjects instead

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 05:19:59

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

libran1783
Starting Member

3 Posts

Posted - 2008-11-21 : 05:38:16

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-21 : 07:35:54

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 08:50:17

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-21 : 08:56:21

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

libran1783
Starting Member

3 Posts

Posted - 2008-11-24 : 01:48:00

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 01:53:56

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources