Problems with 'sp_helpdb' stored procedure

[Eyal Goren]

Hi,
One of our customers claims that the sp_helpdb truncates database name
larger than 24 characters, the release is 7.00.1094, did any one
encounter such a problem ???

[Greg D. Moore \(Strider\)]

"Eyal Goren" <ey********@bmc.com> wrote in message
news:88**************************@posting.google.c om...

Hi,
One of our customers claims that the sp_helpdb truncates database name
larger than 24 characters, the release is 7.00.1094, did any one
encounter such a problem ???

Probably easy enough to determine.

sp_helptext sp_helpdb

And they can read the text for themselves.

I don't see anything limiting to 24 characters at a quick pass. The user
defined datatype of sysname should be limited to 128 characters.

Perhaps someone changed it?

[Erland Sommarskog]

Eyal Goren (ey********@bmc.com) writes:

One of our customers claims that the sp_helpdb truncates database name
larger than 24 characters, the release is 7.00.1094, did any one
encounter such a problem ???

Yes, I was able to repeat this problem on an SQL7 server. It turns
out that in the final SELECT, there is an explicit substring():

select name = substring(d.name, 1, 24),
db_size = str(sum(convert(dec(15),v.size))*
(select low from master.dbo.spt_values
where type = 'E' and number = 1)
/ 1048576,10,2)+ ' MB',
owner = substring(suser_sname(d.sid), 1, 24),
dbid = d.dbid,
created = convert(char(11), d.crdate),
status = s.dbdesc
from master.dbo.sysdatabases d, #spfiledesc v, #spdbdesc s
where d.dbid = s.dbid
and s.dbid = v.dbid
group by d.name,d.sid,d.dbid,d.crdate,s.dbdesc
order by d.name

If you are really venturesome you could change the procedure yourself,
but note that this is definitely not supported.

This problem does not appear in SQL2000.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp