Home » Databases » Sybase » ASE » Ed Barlow Stored Procedures: sp__monunusedindex updated with formatting, including owner and all indexes for SAP Sybase ASE 15 and higher

Ed Barlow Stored Procedures: sp__monunusedindex updated with formatting, including owner and all indexes for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monunusedindex stored procedure for displaying the indexes that have not been used since server start in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve updated the stored procedure to include the table owner and all the indexes & index keys. I’ve also fixed the formatting of the data.

$ diff sp__monunusedindex.old sp__monunusedindex.15
21a22,27
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/20/2013|  Jason Froebe      |    | Show full index name and full index keys
> -- 1.0 |  2006    |  Edward Barlow     |    | show indexes that have not been used since server start
> -------+----------+--------------------+----+-----------------------------------------------------
23c29,34
< select Dbname=db_name(DBID),Object=object_name(ObjectID,DBID),IndexID,IndexName=i.name, ObjectID
---
>
> declare @max_ownername_size varchar(3)
> declare @max_keylist_size varchar(4)
> declare @exec_str varchar(2000)
>
> select Dbname=db_name(DBID), Object=object_name(ObjectID,DBID), IndexID, IndexName=i.name, ObjectID
27,39c38,45
< and   a.ObjectID=o.id  and DBID=db_id()
< and   o.id = i.id and a.IndexID=i.indid
< and   o.type='U'
< and       (LastOptSelectDate is null and OptSelectCount=0)
< -- and   RowsUpdated=0
< -- and   RowsDeleted=0
< -- and   PhysicalReads=0
< -- and   PhysicalWrites=0
< -- and   PagesRead=0
< and   object_name(ObjectID,DBID) not like 'sys%'
< and   object_name(ObjectID,DBID) not like 'rs_%'
< and    object_name(ObjectID,DBID) not like 'spt_%'
< and   IndexID!=0
---
>    and   a.ObjectID=o.id  and DBID=db_id()
>    and   o.id = i.id and a.IndexID=i.indid
>    and   o.type='U'
>    and       (LastOptSelectDate is null and OptSelectCount=0)
>    and   object_name(ObjectID,DBID) not like 'sys%'
>    and   object_name(ObjectID,DBID) not like 'rs_%'
>    and    object_name(ObjectID,DBID) not like 'spt_%'
>    and        IndexID!=0
42,45c48,51
< owner      char(30) not null,
<    uid        smallint not null,
<    name       char(30) not null,
<    index_name char(30) not null,
---
>    owner      sysname not null,
>    uid        int not null,
>    name       longsysname not null,
>    index_name longsysname not null,
53c59
< keylist    char(127) null,
---
>    keylist    varchar(2000) null,
58,73c64,79
< insert into   #indexlist
<    select owner      = user_name(o.uid),
<           o.uid,
<           name       = o.name,
<           index_name = i.name,
<           id = i.id,
<           indexid    = i.indid,
<           clust      = convert(char(1),null),
<           allow_dup  = convert(char(1),null),
<           ign_dup_key  = convert(char(1),null),
<           uniq       = convert(char(1),null),
<           suspect    = convert(char(1),null),
<           keylist    = convert(char(127),"N.A."),
<           status      = status, status2=i.status2
<    from   sysobjects o, sysindexes i, #tmp t
<    where  i.id   = o.id
---
> insert into   #indexlist
> select owner      = user_name(o.uid),
>    o.uid,
>    name       = o.name,
>    index_name = i.name,
>    id = i.id,
>    indexid    = i.indid,
>    clust      = convert(char(1),null),
>    allow_dup  = convert(char(1),null),
>    ign_dup_key  = convert(char(1),null),
>    uniq       = convert(char(1),null),
>    suspect    = convert(char(1),null),
>    keylist    = "N.A.",
>    status      = status, status2=i.status2
> from   sysobjects o, sysindexes i, #tmp t
> where  i.id   = o.id
75d80
< --and    o.type in ("U",@show_type)
106c111
< or     status2&8192= 8192
---
>    or status2 & 8192= 8192
111c116
< while ( @count < 17 )   /* 16 appears to be the max number of indexes */
---
> while ( @count < 250 )   /* max number of indexes = 250 */
113d117
<
120c124
<       set    keylist=rtrim(keylist)+","+index_col(name,indexid,@count,uid)
---
>       set    keylist = convert(varchar(2000), rtrim(keylist)+", "+index_col(name,indexid,@count,uid))
123c127,128
< if @@rowcount=0   break
---
>    if @@rowcount=0
>       break
132,138c137,143
< if @no_print is null
<         begin
<         print "   INDEX KEY:     c = clustered            u = unique"
<         print "                  a = allow dup row        s = suspect"
<         print "                  i = ignore dup key "
<         print ""
<               end
---
> if @no_print is null
> begin
>    print "   INDEX KEY:     c = clustered            u = unique"
>    print "                  a = allow dup row        s = suspect"
>    print "                  i = ignore dup key "
>    print ""
> end
140,146c145,173
< select "Name" = rtrim(name)+"."+index_name,
<        c   = isnull(clust,""),
<        u   = isnull(uniq,""),
<        i   = isnull(ign_dup_key,""),
<        a   = isnull(allow_dup,""),
<        s   = isnull(suspect,""),
<        "List of Index Keys"    = keylist
---
> if @dont_format is null
> begin
>    select @max_ownername_size = convert(varchar(3), isnull(max(char_length(owner)) + max(char_length(name)) + max(char_length(index_name)) + 2, 1) )
>       from #indexlist
>
>    select @max_keylist_size = convert(varchar(4), isnull(max(char_length(keylist)), 1) )
>       from #indexlist
>
>    select @exec_str =
>       'select "Name" = convert(varchar(' + @max_ownername_size + '), rtrim(owner) + "." + rtrim(name) + "." + index_name),
>          c   = isnull(clust,""),
>          u   = isnull(uniq,""),
>          i   = isnull(ign_dup_key,""),
>          a   = isnull(allow_dup,""),
>          s   = isnull(suspect,""),
>          "List of Index Keys"    = convert(varchar(' + @max_keylist_size + '), keylist)
>       from #indexlist
>       order by owner,name,indexid'
>    exec (@exec_str)
> end
> else
> begin
>    select "Name" = rtrim(owner) + "." + rtrim(name) + "." + index_name,
>       c   = isnull(clust,""),
>       u   = isnull(uniq,""),
>       i   = isnull(ign_dup_key,""),
>       a   = isnull(allow_dup,""),
>       s   = isnull(suspect,""),
>       "List of Index Keys"    = keylist
148a176
> end

full code of sp__monunusedindex:


use sybsystemprocs
go
/* Procedure copyright(c) 2006 by Edward M Barlow */

/******************************************************************************
**
** Name : sp__monunusedindex
**
** Created By : Ed Barlow
**
******************************************************************************/

IF EXISTS (SELECT * FROM sysobjects
WHERE name = “sp__monunusedindex”
AND type = “P”)
DROP PROC sp__monunusedindex

go

create proc sp__monunusedindex( @dont_format char(1) = null, @no_print char(1) = null)
as
————————————————————————————————–
— Vers| Date | Who | DA | Description
——-+———-+——————–+—-+—————————————————–
— 1.1 |11/20/2013| Jason Froebe | | Show full index name and full index keys
— 1.0 | 2006 | Edward Barlow | | show indexes that have not been used since server start
——-+———-+——————–+—-+—————————————————–
begin

declare @max_ownername_size varchar(3)
declare @max_keylist_size varchar(4)
declare @exec_str varchar(2000)

select Dbname=db_name(DBID), Object=object_name(ObjectID,DBID), IndexID, IndexName=i.name, ObjectID
into #tmp
from master..monOpenObjectActivity a, sysobjects o, sysindexes i
where RowsInserted=0
and a.ObjectID=o.id and DBID=db_id()
and o.id = i.id and a.IndexID=i.indid
and o.type=’U’
and (LastOptSelectDate is null and OptSelectCount=0)
and object_name(ObjectID,DBID) not like ‘sys%’
and object_name(ObjectID,DBID) not like ‘rs_%’
and object_name(ObjectID,DBID) not like ‘spt_%’
and IndexID!=0

create table #indexlist (
owner sysname not null,
uid int not null,
name longsysname not null,
index_name longsysname not null,
id int not null,
indexid smallint not null,
clust char(1) null,
allow_dup char(1) null,
ign_dup_key char(1) null,
uniq char(1) null,
suspect char(1) null,
keylist varchar(2000) null,
status smallint not null,
status2 smallint not null
)

insert into #indexlist
select owner = user_name(o.uid),
o.uid,
name = o.name,
index_name = i.name,
id = i.id,
indexid = i.indid,
clust = convert(char(1),null),
allow_dup = convert(char(1),null),
ign_dup_key = convert(char(1),null),
uniq = convert(char(1),null),
suspect = convert(char(1),null),
keylist = “N.A.”,
status = status, status2=i.status2
from sysobjects o, sysindexes i, #tmp t
where i.id = o.id
and i.id = ObjectID and i.indid=IndexID
and indid > 0

/* delete multiple rows */
delete #indexlist
from #indexlist a, #indexlist b
where a.indexid = 0
and b.indexid != 0
and a.name = b.name

update #indexlist
set clust=’Y’
where indexid = 1
or status&16=16
or status2&512 = 512

update #indexlist
set uniq = ‘Y’
where status & 2 = 2

update #indexlist
set ign_dup_key = ‘Y’
where status & 1 = 1

update #indexlist
set allow_dup = ‘Y’
where status & 64 = 64

update #indexlist
set suspect = ‘Y’
where status & 32768 = 32768
or status2 & 8192= 8192

declare @count int
select @count=1

while ( @count < 250 ) /* max number of indexes = 250 */ begin if @count=1 update #indexlist set keylist=index_col(name,indexid,1,uid) where index_col(name,indexid,@count,uid) is not null else update #indexlist set keylist = convert(varchar(2000), rtrim(keylist)+", "+index_col(name,indexid,@count,uid)) where index_col(name,indexid,@count,uid) is not null if @@rowcount=0 break select @count=@count+1 end update #indexlist set name=convert(char(30), rtrim(rtrim(substring(owner,1,15)) + "." +name)) where owner!="dbo" if @no_print is null begin print " INDEX KEY: c = clustered u = unique" print " a = allow dup row s = suspect" print " i = ignore dup key " print "" end if @dont_format is null begin select @max_ownername_size = convert(varchar(3), isnull(max(char_length(owner)) + max(char_length(name)) + max(char_length(index_name)) + 2, 1) ) from #indexlist select @max_keylist_size = convert(varchar(4), isnull(max(char_length(keylist)), 1) ) from #indexlist select @exec_str = 'select "Name" = convert(varchar(' + @max_ownername_size + '), rtrim(owner) + "." + rtrim(name) + "." + index_name), c = isnull(clust,""), u = isnull(uniq,""), i = isnull(ign_dup_key,""), a = isnull(allow_dup,""), s = isnull(suspect,""), "List of Index Keys" = convert(varchar(' + @max_keylist_size + '), keylist) from #indexlist order by owner,name,indexid' exec (@exec_str) end else begin select "Name" = rtrim(owner) + "." + rtrim(name) + "." + index_name, c = isnull(clust,""), u = isnull(uniq,""), i = isnull(ign_dup_key,""), a = isnull(allow_dup,""), s = isnull(suspect,""), "List of Index Keys" = keylist from #indexlist order by owner,name,indexid end end go[/sql] sp__monunusedindex

Share Button

Leave a Reply

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

*
*

Facebook login by WP-FB-AutoConnect