declare @command1 varchar(500)
, @command2 varchar(500)
, @command3 varchar(500)
, @replacechar varchar(500)
, @postcommand varchar(500)
, @precommand varchar(500)
select @command1 = ‘if
”@” <> ”tempdb” insert into ##TableStorage exec [@]..sp_MSforeachtable ”Exec sp_SpaceUsed ””?””” ‘
, @command2 = ‘Update ##TableStorage set
[TotalSpace] = cast(replace([TotalSpace], ” KB”, ””)/1024 as bigint),
[DataSpace] = cast(replace([DataSpace], ” KB”, ””)/1024 as bigint),
[IndexSpace] = cast(replace([IndexSpace], ” KB”, ””)/1024 as bigint),
[UnusedSpace] = cast(replace([UnusedSpace], ” KB”, ””)/1024 as bigint)’
, @command3 = ‘insert into TableStorage select CONVERT(char(20), SERVERPROPERTY(”servername”)),
”@”,*,getdate() from ##TableStorage ‘
, @replacechar =
‘@’, @postcommand = ‘select * from TableStorage ‘
, @precommand = ‘if object_id(”Tempdb..##TableStorage”) is not null drop table ##TableStorage
CREATE TABLE [##TableStorage](
[TableName] [nvarchar](128) NOT NULL,
[RowCount] [nvarchar](128) NULL,
[TotalSpace] [nvarchar](128) NULL,
[DataSpace] [nvarchar](128) NULL,
[IndexSpace] [nvarchar](128) NULL,
[UnusedSpace] [nvarchar](128) NULL,
) ‘
exec sp_MSforeachdb @command1=@command1,
@command2=@command2,
@command3=@command3,
@replacechar=@replacechar,
@postcommand=@postcommand,
@precommand=@precommand
