July 20, 2011 at 2:43 am
Hi All,
I am trying to execute the below script to get all database size in a single temp table.
----------------------------------------------------------------------
--USE YOUR_DATABASE_NAME
CREATE TABLE #tSpace
(
DatabaseName VARCHAR(100),
DatabaseSize VARCHAR(100),
AllocatedSpace VARCHAR(100),
SpareColumn VARCHAR(100) null
)
EXEC sp_MSforeachdb 'INSERT INTO #tSpace(DatabaseName, DatabaseSize, AllocatedSpace) EXEC sp_spaceused'
UPDATE
#tSpace
SET
DatabaseName = REPLACE(DatabaseName, ' MB', ''),
DatabaseSize = REPLACE(DatabaseName, ' MB', ''),
AllocatedSpace = REPLACE(DatabaseName, ' MB', '')
SELECT
DatabaseName,
DatabaseSize,
AllocatedSpace
FROM
#tSpace
ORDER BY CAST(DatabaseSize AS BIGINT) DESC
DROP TABLE #tSpace
----------------------------------------------------------------------
When we use sp_spaceused to get the size of database it will give 2 recordsets. 1st one with 3 columns and 2nd one with 4 columns. So, the above script is throwing the below error.
Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 128
Insert Error: Column name or number of supplied values does not match table definition.
Appreciate any help.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
July 20, 2011 at 4:18 am
What is the purpose of this script?
I think you can achieve it with sys.database_files
July 20, 2011 at 6:26 am
Yes Suresh,
This is enough for me. I have replaced sp_spaceused with SELECT * FROM sys.database_files.
Thanks a lot.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
July 20, 2011 at 8:56 am
I had some fun making a version of this that grabs all the tables in all the dbs... just in case it helps someone in the future.
This would be amazingly easy to change to keep an history and maybe run weekly has a job...
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
--WHERE Row_count > 0
ORDER BY
DbName
, MB_Reserved DESC
, Row_count DESC
COMMIT
July 20, 2011 at 9:35 pm
Hi Ninja,
You code will give all table sizes from all databases. But I was looking for only database sizes. When we use sp_spaceused without any parameter it will give db size details. But with 2 recordsets. So, my code is not working.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
July 20, 2011 at 10:03 pm
If you need only database size, you can query sys.master_files directly. No need to use sp_msforeachdb.
Hint:
select (size*128.0)/1024 'Size (MB)', * from sys.master_files
July 21, 2011 at 12:03 am
I did it suresh, It is working for me as per your 1st reply. My problem already solved by your solution.
But when I see Ninja's reply I just want to clear him what was my issue.
The issue was, if we are inserting some data to a table from a stored procedure. How do we insert to a table if the stored procedure returns more than 1 recordset, and especially if those recordsets are having different number of columns?
_____________________________________________
One ounce of practice is more important than tonnes of dreams
July 21, 2011 at 5:10 am
C.K.Shaiju (7/21/2011)
I did it suresh, It is working for me as per your 1st reply. My problem already solved by your solution.But when I see Ninja's reply I just want to clear him what was my issue.
The issue was, if we are inserting some data to a table from a stored procedure. How do we insert to a table if the stored procedure returns more than 1 recordset, and especially if those recordsets are having different number of columns?
I knew I gave you something different from the original question (which had already been answered as far as I know).
My Code gives the row count and table size for all the tables in all the dbs. I just thought you might like it since it's similar data.
July 21, 2011 at 5:23 am
Ninja's_RGR'us (7/21/2011)
I knew I gave you something different from the original question (which had already been answered as far as I know).My Code gives the row count and table size for all the tables in all the dbs. I just thought you might like it since it's similar data.
Oh.. Ok Ninja. I thought we are diverting from the discussion. That's why I replied for your reply. By the way, I have added your code to my collection :). Thank you.
Hi Suresh,
I tried the below code.
select (size*128.0)/1024 'Size (MB)', * from sys.master_files
But it is giving 32 MB for almost all databases which is having less than 10 MB size. For e.g. It is showing 32 MB for 2.50 MB database. Do I need to do anything to get the exact value?
_____________________________________________
One ounce of practice is more important than tonnes of dreams
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply