How do I resize the results from a stored procedure?

[SQLBill]

DB: SQL Server 2000

The results from sp_spaceused has extra spaces in the database_name column. For example:

database_name
-----------------------------------------------------------------
Northwind

I want to resize the column.
...............................
If I was doing a regular query, I could do:

select substring(database_name, 1, 15) as 'database_name', database_size, unallocated space, reserved, Data, index_size, unused
from <table>

and the 'substring' part would resize the database_name column to the size I want.
..................................
However, I'm using a stored procedure....and I can't get the substring to work.

-Bill
13 days until my MS SQL Programming class starts!

 

[JayKusch]

can you post the full syntax of your sp, stored_procedure

Thanks

 

[SQLBill]

It's not my stored procedure...it's a 'built-in' one.
sp_spaceused is part of MS SQL Server (it's even listed in the BOL). If it was one I created, I could have included the substring command. My problem is that, since sp_spaceused is built-in, I'm not sure how to do the substring without having to re-write the stored procedure.

-Bill

 

[Andel]

You can resize the maximum characters per column.
From the Query Analizer, select Tools, then Options, click on the Result tab, then change the &quot;Maximum characters per column&quot; to whatever number you wish.
Andel
andel@barroga.net

http://andyb.8m.com

 

[DBCustard]

You can view the source of all the system stored procedures, they are just like any other procedure except they reside in the master database, so to look at sp_spaceused:

master..sp_helptext sp_spaceused

You'll probably be suprised by the complexity of these procedures, but I think you want to do something like:

declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

select @bytesperpage = low
from master.dbo.spt_values
where number = 1 and
type = 'E'

select @pagesperMB = 1048576 / @bytesperpage

select database_name = left(db_name(), 15),
database_size = ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' = ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))) / @pagesperMB,15,2)+ ' MB')