January 4, 2012 at 8:25 am
Hi all,
I'd like to load table space info for two databases and make a comparison (for example in row counts) but want to get this work within one stored procedure.
EXEC sp_msforeachtable 'insert into #tbl EXEC dbo.sp_spaceused [?]' would do for the current DB. Any way I could use something like (just to demonstrate the logic what I want)
EXEC (sp_msforeachtable 'insert into #tbl1 EXEC ('USE ' +@DB1+ ' EXEC sp_spaceused [?]')')
EXEC (sp_msforeachtable 'insert into #tbl2 EXEC ('USE ' +@DB2+ ' EXEC sp_spaceused [?]')')
then join the 2 tables within one proc? Thanks.
__________________________
Allzu viel ist ungesund...
January 4, 2012 at 8:32 am
Ugly, but close enough to what you need.
There are better table spaces scripts but I don't have the links right now.
SET STATISTICS IO, TIME OFF
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS ON
GO
USE [master]
GO
IF OBJECT_ID('dbo.spaceused', 'U') IS NULL
BEGIN
CREATE TABLE dbo.spaceused (
DbName sysname DEFAULT(''),
tblName sysname,
Row_count INT ,
Reserved VARCHAR(50),
data VARCHAR(50) ,
index_size VARCHAR(50),
unused VARCHAR(50),
PRIMARY KEY CLUSTERED (DbName, tblName)
);
END
ELSE
BEGIN
--DROP TABLE dbo.spaceused
TRUNCATE TABLE dbo.spaceused
END
COMMIT
GO
DECLARE @Cmd VARCHAR(8000)
SET @Cmd = 'USE [?];
IF ''?'' NOT IN (''tempdb''
--, ''master'', ''model'', ''msdb''
)
BEGIN
--PRINT ''?''
DECLARE @InnerCmd VARCHAR(8000)
SET @InnerCmd = ''
EXEC sp_spaceused '''''' + CHAR(63) + ''''''''
INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)
EXEC sp_MSforeachtable @InnerCmd
UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''
END
'
--PRINT @Cmd
EXEC sp_MSforeachdb @Cmd
DELETE FROM dbo.spaceused WHERE Row_count = 0
SELECT
DbName
, tblName
, Row_count
, CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved
, CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data
, CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size
, CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused
FROM
dbo.spaceused
ORDER BY
DbName
, MB_Reserved DESC
, Row_count DESC
COMMIT
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply