Home » Databases » Sybase » ASE » Modifying sp__revindex so it doesn’t print the “go”

Modifying sp__revindex so it doesn’t print the “go”

I’m in the process of creating a suite of perl modules that use Ed Barlow‘s sp__rev* procedures. I’ve noticed that the @dont_format parameter isn’t being used in sp__revindex so I made use of it to drop the “go” from being included in the result set.

The reason why I don’t want the “go” is because I want just the ddl of the index and not a SQL script. The default behavior is to show the “go”.

$ diff revindex.10 ../j/revindex.10
32c32
< declare @nl_go     char(3)    /* RV added */
---
> declare @nl     char    /* RV added */
34,37c34
< if @dont_format is NULL
< begin
<       select @nl_go = char(10) + "go"   /* RV added */
< end
---
> select @nl = char(10)   /* RV added */
47c44
< endingstmt = convert(varchar(255),@nl_go),
---
>       endingstmt = convert(varchar(255),@nl + "go"),
88,93d84
< and status & 2048 != 2048
<
< update #indexlist
< set    createstmt = rtrim(createstmt)+' PRIMARY KEY'
< where  status & 2 = 2
<        and status & 2048 = 2048
166c157
<                                                                                               @nl_go,
---
>                                                                                               @nl +'go' ,
182c173
< ' DROP CONSTRAINT ' + rtrim(index_name) + @nl_go,
---
>                   ' DROP CONSTRAINT ' + rtrim(index_name) + @nl +'go' ,

Output:

exec sp__revindex rep_queues_archive
exec sp__revindex rep_queues_archive, "1";

        -- DDL Code



        ------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
-------

        IF EXISTS ( SELECT * FROM sysindexes WHERE
                        id=OBJECT_ID("rep_queues_archive") AND name= "CDX" ) DROP INDEX rep_queues_archive.CDX
go

        CREATE UNIQUE CLUSTERED INDEX CDX ON dbo.rep_queues_archive (sample_date,serverName,queue_id,direction)  WITH IGNORE_DUP
_KEY
go


(2 rows affected, return status = 0)

(1 row affected)

        -- DDL Code



        ------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
-------

        IF EXISTS ( SELECT * FROM sysindexes WHERE
                        id=OBJECT_ID("rep_queues_archive") AND name= "CDX" ) DROP INDEX rep_queues_archive.CDX


        CREATE UNIQUE CLUSTERED INDEX CDX ON dbo.rep_queues_archive (sample_date,serverName,queue_id,direction)  WITH IGNORE_DUP
_KEY


(2 rows affected, return status = 0)
[22] DBADEV1.sybase_dba.1>
Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect