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. |
|
|
|