SQLCMD.EXE generates ugly report. How to format it?

Posted by Juri Bogdanov on Stack Overflow See other posts from Stack Overflow or by Juri Bogdanov
Published on 2009-07-14T14:10:15Z Indexed on 2010/03/19 14:01 UTC
Read the original article Hit count: 584

Filed under:
|
|
|

I did batch to run SQL query like

use [AxDWH_Central_Reporting]
GO
EXEC sp_spaceused @updateusage = N'TRUE'
GO

It displays 2 tables and generates some ugly report with some kind of 'P' unneeded letters... See below

Changed database context to 'AxDWH_Central_Reporting'.

database_name                                                                                                                   Pdatabase_size     Punallocated space 
--------------------------------------------------------------------------------------------------------------------------------P------------------P------------------
AxDWH_Central_Reporting                                                                                                         P10485.69 MB       P7436.85 MB        
reserved          Pdata              Pindex_size        Punused            
------------------P------------------P------------------P------------------
3121176 KB        P3111728 KB        P7744 KB           P1704 KB           
----------------------------------------------------------------

I also tryed to generate 1 table from this procedure with next query

declare
    @dbname sysname,
    @dbsize bigint,
    @logsize bigint,
    @reservedpages  bigint

select
    @reservedpages = sum(a.total_pages)
from
    sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

select
    @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
    @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))

from 
    dbo.sysfiles

select 
    'database name' = db_name(),
    'database size' = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2) + ' MB'),
    'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
    	(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2) + ' MB')

But got similar ugly report:

database name                                                                                                                   Pdatabase size     Punallocated space 
--------------------------------------------------------------------------------------------------------------------------------P------------------P------------------
master                                                                                                                          P5.75 MB           P1.52 MB           

(1 rows affected)

Is it possible to change the layout formatting for report? To make it more beautifull?

© Stack Overflow or respective owner

Related posts about sqlcmd

Related posts about tsql