SQLCMD.EXE 生成丑陋的报告。 怎么格式化呢？

Question

我批量运行 SQL 查询，就像

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

它显示 2 个表并生成一些丑陋的报告，其中包含某种不需要的“P”字母...请参见下文，

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           
----------------------------------------------------------------

我还尝试使用下一个查询从此过程中生成 1 个表，

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')

但得到了类似的丑陋报告：

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

(1 rows affected)

是否可以更改报告的布局格式？ 为了让它更漂亮？

Answer 1

我怀疑您能否很好地格式化 SQLCMD 和 sp_spaceused - 为什么不使用其他一些方法来获取您需要的信息？

在 SQL Server 2005 及更高版本中，使用这段 T-SQL 来显示表及其使用的大小：

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id) 

对我来说效果很好！ :-)

马克