The joy of sp_spaceused

Question

Post reply

The joy of sp_spaceused

Sibandaz

Right there with Babe

Points: 733
More actions
June 21, 2012 at 8:41 pm

#262758

Comments posted to this topic are about the item The joy of sp_spaceused

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 1

you could use an undocumented system sp to avoid the cursor

CREATE TABLE #temp

(

[name] nvarchar(128),

[rows] char(11),

[reserved] varchar(18),

[data] varchar(18),

[index_size] varchar(18),

[unused] varchar(18)

)

INSERT INTO #temp

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?' "

SELECT

[name] AS TableName,

CAST([rows] AS int) AS NumOfRows,

CAST(REPLACE([reserved],' KB','') AS int) AS [TotalKB]

FROM

#temp

ORDER BY 3 DESC

SELECT SUM(CAST(REPLACE([reserved],' KB','') AS int)) AS TotalKB_data FROM #temp

DROP TABLE #temp

Sibandaz Right there with Babe Points: 733 More actions · Answer 2

You are right and this will make it more faster than the solution I provided with the cursor.

Thanks

thadeushuck Ten Centuries Points: 1367 More actions · Answer 3

SELECT A.name,(SELECT rows FROM dbo.sysindexes s WITH (NOLOCK)

WHERE s.indid < 2 AND s.id = A.ID )AS [Row count],SpaceUsedMB from

(SELECTSO.NAME,SO.ID,

CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low

FROM master.dbo.spt_values WITH (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) SpaceUsedMB

FROMdbo.sysindexes i WITH (NOLOCK)

INNER JOIN

dbo.sysobjects so WITH (NOLOCK)

ON

i.id = so.id

AND so.type IN ('U')

WHEREindid IN (0, 1, 255)

GROUP BY SO.NAME,SO.ID)

AS A

ORDER BY SpaceUsedMB DESC

this is what I use to figure out table sizes in a database.

stevedln SSC Enthusiast Points: 132 More actions · Answer 4

It may not be the best way and you may be able to wrap an sp_msforeachtable with the sp_msforeachdb procedure but here is one way to iterate over each database and apply the for each table and space used sprocs. This will allow you to see each table individually.

USE master

GO

DECLARE @DBList AS TABLE (recid int identity,

dbname varchar(150))

INSERT INTO @DBList(dbname)

SELECT name

FROM sys.databases db

WHERE name NOT IN ('master','msdb','tempdb','model')

DECLARE @count int = (SELECT COUNT(*) FROM @DBList)

DECLARE @i int = 1

DECLARE @dbname varchar(150)

DECLARE @sql nvarchar(max)

SET NOCOUNT ON;

WHILE (@i <= @count)

BEGIN

SELECT @dbname = dbname FROM @DBList WHERE recid = @i;

SET @sql = '';

PRINT ' '

PRINT '#########################################'

PRINT 'SPACE USED FOR TABLES in DB: ' + @dbname;

PRINT '#########################################'

PRINT ' '

SET @sql = N'USE ' + @dbname + ';' + ' EXEC sp_msforeachtable '' sp_spaceused "?"'' '

PRINT @sql

EXEC(@sql);

SET @i +=1;

END

Again; this is just one way.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 5

thadeushuck (6/22/2012)
this is what I use to figure out table sizes in a database.

your version gives different results from the original or my own version...

aco.mladjenovic Grasshopper Points: 17 More actions · Answer 6

SSMS has this functionality already build in. Right click database select Reports and Disk Usage by Top Tables. Saves some time anyway.:-)

Sibandaz Right there with Babe Points: 733 More actions · Answer 7

I know about the one from ssms but whether it saves time depends on what you are doing and where you are and am not sure if that from ssms you refer to can be automated and linked to a job or proc sendmail. I normally do a count on tables using something almost similar to the script i wrote, put it in a ssis package with other processes that checks the size of tables, deletes,insert orr update depending on the table size, then send email and all this is done during off office times.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

WILLIAM MITCHELL (6/22/2012)
you could use an undocumented system sp to avoid the cursor
CREATE TABLE #temp
(
[name] nvarchar(128),
[rows] char(11),
[reserved] varchar(18),
[data] varchar(18),
[index_size] varchar(18),
[unused] varchar(18)
)
INSERT INTO #temp
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?' "
SELECT
[name] AS TableName,
CAST([rows] AS int) AS NumOfRows,
CAST(REPLACE([reserved],' KB','') AS int) AS [TotalKB]
FROM
#temp
ORDER BY 3 DESC
SELECT SUM(CAST(REPLACE([reserved],' KB','') AS int)) AS TotalKB_data FROM #temp
DROP TABLE #temp

That doesn't actually avoid a cursor. It just hides it. If you look at the code for sp_MSforeachtable, it's a cursor on steroids.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

mdawini1972 (6/22/2012)
You are right and this will make it more faster than the solution I provided with the cursor.
Thanks

No. It won't. sp_MSforeachtable IS a cursor based stored procedure. Open it up and look at it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sibandaz Right there with Babe Points: 733 More actions · Answer 10

Thanks Jeff I will have a look at it. I initially assumed it wasnt using a cursor. I havent seen it before though.