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
Go to Top of Page

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'
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -