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

[jfroebe@dba-dev1 procs]$ 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:

[21] DBADEV1.sybase_dba.1> exec sp__revindex rep_queues_archive
[21] DBADEV1.sybase_dba.2> 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>

« »