SQLCMD.EXE generates ugly report. How to format it?
- by Juri Bogdanov
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?
