Skip to content

Instantly share code, notes, and snippets.

@jdaigle
Created April 25, 2013 13:02
Show Gist options
  • Save jdaigle/5459517 to your computer and use it in GitHub Desktop.
Save jdaigle/5459517 to your computer and use it in GitHub Desktop.
query, spaced using in database for each table
SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT
[name],
replace(convert(varchar,cast([rows] as money),1),'.00','') AS [rows],
replace(convert(varchar,cast(replace(reserved,' KB','') as money),1),'.00','') AS reserved,
replace(convert(varchar,cast(replace(data,' KB','') as money),1),'.00','') AS data,
replace(convert(varchar,cast(replace(index_size,' KB','') as money),1),'.00','') AS index_size,
replace(convert(varchar,cast(replace(unused,' KB','') as money),1),'.00','') AS unused
FROM #t
order by [name]
-- # of rows.
SELECT replace(convert(varchar,cast(SUM(CAST([rows] AS int)) as money),1),'.00','') AS [rows]
FROM #t
DROP TABLE #t
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment