drop scripts - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

drop scripts

Author

Topic

jeroena
Yak Posting Veteran

76 Posts

Posted - 2002-10-18 : 09:59:39

Hi

I created a script for dropping all the user defined stored procedures in my database.

DECLARE @csp_name VARCHAR(255)

DECLARE c_procs CURSOR
FOR
SELECT
name
FROM
sysobjects
WHERE
name LIKE 'csp_%'

OPEN c_procs

FETCH NEXT FROM c_procs
INTO @csp_name

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DROP PROCEDURE ' + @csp_name)
PRINT ('Procedure "' + @csp_name + '" verwijderd.')
FETCH NEXT FROM c_procs
INTO @csp_name
END

CLOSE c_procs
DEALLOCATE c_procs
GO

All my procedures start with "csp_". The question: Does anyone have such a dropscript for indexes? My indexes are names "<tablename>_ix001", so this works:

select * from sysindexes where name like '%_ix%'

Thnx!

Spike.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-18 : 10:48:09

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20452

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-18 : 11:01:39

Two additional things:

A. Procedures can be created as part of, and dropped as, a group. Since you are prefixing the procedures you want to group together, you might want to look at using procedure grouping if you intend to drop and recreate them on a regular basis. Books Online details this.

B. You could also:

DECLARE @sp varchar(8000)
SET @sp=''
SELECT @sp=@sp + name + ',' FROM sysobjects WHERE name LIKE 'csp[_]%'
EXECUTE ('DROP PROCEDURE ' + Left(@sp, Len(@sp)-1))

This code grabs all of the procedure names and builds a string of them, which is included in the DROP PROCEDURE call to drop all of them. The same technique can be used to drop all of the indexes as well:

DECLARE @idx varchar(8000)
SET @idx=''
SELECT @idx=@idx + name + ',' FROM sysindexes WHERE name LIKE '%[_]ix%'
EXECUTE ('DROP INDEX ' + Left(@idx, Len(@idx)-1))

Also, you need to enclose a literal underscore in square brackets like this [_] in order for the LIKE operator to treat it as a literal, and not a pattern match character.

Edited by - robvolk on 10/18/2002 11:18:38

jeroena
Yak Posting Veteran

76 Posts

Posted - 2002-10-21 : 05:04:35

Hmm... seems more effecient...

doesn't seem to work here (SQL Server 2000) though.

Incorrect syntax near the keyword 'Left'.

And the DROP INDEX statement needs a tablename as well.

I should be able to get it to work though. Thnx.

Jeroen.

jeroena
Yak Posting Veteran

76 Posts

Posted - 2002-10-21 : 05:09:12

This seems to work:

DECLARE
@idx_name VARCHAR(255)
, @tab_name VARCHAR(255)

DECLARE c_indexes CURSOR
FOR
SELECT sob.name AS TableName,
six.name AS IndexName
FROM sysobjects sob,
sysindexes six
WHERE six.name LIKE sob.name + '%'
and
six.name LIKE '%_ix%'

OPEN c_indexes

FETCH NEXT FROM c_indexes
INTO @tab_name, @idx_name

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DROP INDEX ' + @tab_name + '.' + @idx_name)
PRINT ('Index "' + @idx_name + '" verwijderd.')
FETCH NEXT FROM c_indexes
INTO @tab_name, @idx_name
END

CLOSE c_indexes
DEALLOCATE c_indexes
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources