dcsimg
Results 1 to 6 of 6

Thread: system stored procedures

  1. #1
    Join Date
    Mar 2003
    Posts
    9

    system stored procedures

    I have written a custom stored procedure which calls a system stored procedure such as sp_helpindex. Is there anyway to manipulate the results that are displayed from the system stored procedure. For example, I would like to substring out the first 30 characters of the first column.

    To give you an idea of my stored procedure...you pass in tablename as the parameter and the following is executed from within my stored procedure:
    @str1 = 'exec sp_helpindex [' + @tablename + ']'
    exec (@str1)

    My stored procedure does many other things, but I would like to clean up the display of results generated from the sp_helpindex.

    Any suggestions would be appreciated.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    you can see the code of the sp_helpindex by typing

    use master
    go
    sp_helptext sp_helpindex


    Here is the code I updated to display only 30 characters of first column



    create proc sp_helpindex2
    @objname nvarchar(776) -- the table to check for indexes
    as
    -- PRELIM
    set nocount on

    declare @objid int, -- the object id of the table
    @indid smallint, -- the index id of an index
    @groupid smallint, -- the filegroup id of an index
    @indname sysname,
    @groupname sysname,
    @status int,
    @keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
    @dbname sysname

    -- Check to see that the object names are local to the current database.
    select @dbname = parsename(@objname,3)

    if @dbname is not null and @dbname <> db_name()
    begin
    raiserror(15250,-1,-1)
    return (1)
    end

    -- Check to see the the table exists and initialize @objid.
    select @objid = object_id(@objname)
    if @objid is NULL
    begin
    select @dbname=db_name()
    raiserror(15009,-1,-1,@objname,@dbname)
    return (1)
    end

    -- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
    declare ms_crs_ind cursor local static for
    select indid, groupid, name, status from sysindexes
    where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid
    open ms_crs_ind
    fetch ms_crs_ind into @indid, @groupid, @indname, @status

    -- IF NO INDEX, QUIT
    if @@fetch_status < 0
    begin
    deallocate ms_crs_ind
    raiserror(15472,-1,-1) --'Object does not have any indexes.'
    return (0)
    end

    -- create temp table
    create table #spindtab
    (
    index_name sysname collate database_default NOT NULL,
    stats int,
    groupname sysname collate database_default NOT NULL,
    index_keys nvarchar(2126) collate database_default NOT NULL -- see @keys above for length descr
    )

    -- Now check out each index, figure out its type and keys and
    -- save the info in a temporary table that we'll print out at the end.
    while @@fetch_status >= 0
    begin
    -- First we'll figure out what the keys are.
    declare @i int, @thiskey nvarchar(131) -- 128+3

    select @keys = index_col(@objname, @indid, 1), @i = 2
    if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
    select @keys = @keys + '(-)'

    select @thiskey = index_col(@objname, @indid, @i)
    if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
    select @thiskey = @thiskey + '(-)'

    while (@thiskey is not null )
    begin
    select @keys = @keys + ', ' + @thiskey, @i = @i + 1
    select @thiskey = index_col(@objname, @indid, @i)
    if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
    select @thiskey = @thiskey + '(-)'
    end

    select @groupname = groupname from sysfilegroups where groupid = @groupid

    -- INSERT ROW FOR INDEX
    insert into #spindtab values (@indname, @status, @groupname, @keys)

    -- Next index
    fetch ms_crs_ind into @indid, @groupid, @indname, @status
    end
    deallocate ms_crs_ind

    -- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
    declare @empty varchar(1) select @empty = ''
    declare @des1 varchar(35), -- 35 matches spt_values
    @des2 varchar(35),
    @des4 varchar(35),
    @des32 varchar(35),
    @des64 varchar(35),
    @des2048 varchar(35),
    @des4096 varchar(35),
    @des8388608 varchar(35),
    @des16777216 varchar(35)
    select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
    select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
    select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
    select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
    select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
    select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
    select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
    select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
    select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216

    -- DISPLAY THE RESULTS
    select
    --Update the first column with 30 character
    -- 'index_name' = index_name,
    'index_name' = left(index_name,30),
    'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
    case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
    + case when (stats & 1)<>0 then ', '+@des1 else @empty end
    + case when (stats & 2)<>0 then ', '+@des2 else @empty end
    + case when (stats & 4)<>0 then ', '+@des4 else @empty end
    + case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
    + case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
    + case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
    + case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
    + case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
    + ' located on ' + groupname),
    'index_keys' = index_keys
    from #spindtab
    order by index_name


    return (0) -- sp_helpindex

  3. #3
    Join Date
    Mar 2003
    Posts
    9
    So I guess there is no way to change the results without implicitly changing the code of the stored procedure. I was hoping that like with a table (select substring(column,1,30) as "COLUMN 1"), I could modify the stored procedure somehow in a select statment.

    Thanks for you suggestion....I guess that is really my only option.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    You can save the result of sp_helpindex in a temp table and manipulate the result.

    insert into #temptable exec sp_helpindex 'mytable'

    But you have to figure out the structure of the temp table you need, because the temp table needs to be pre-created.

  5. #5
    Join Date
    Mar 2003
    Posts
    9
    Thanks so much! It worked perfectly!!!

  6. #6
    Join Date
    Mar 2003
    Posts
    9
    Thanks so much! It worked perfectly!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •