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