Created
September 2, 2011 06:05
-
-
Save shbaz/1188006 to your computer and use it in GitHub Desktop.
Size of tables in a SQL Server database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Count rows in all tables, display tables with rows | |
-- | |
CREATE TABLE #T ( | |
name NVARCHAR(255), | |
[rows] INT, | |
reserved NVARCHAR(255), | |
data NVARCHAR(255), | |
index_size NVARCHAR(255), | |
unused NVARCHAR(255) | |
); | |
DECLARE tnames_cursor CURSOR | |
FOR | |
SELECT '[' + s.name + '].[' + t.name + ']' | |
FROM sys.tables AS t | |
JOIN sys.schemas AS s ON s.schema_id = t.schema_id | |
ORDER BY s.name + '.' + t.name; | |
OPEN tnames_cursor; | |
DECLARE @tablename sysname; | |
FETCH NEXT FROM tnames_cursor INTO @tablename; | |
WHILE (@@FETCH_STATUS <> -1) | |
BEGIN | |
IF (@@FETCH_STATUS <> -2) | |
BEGIN | |
SELECT @tablename = RTRIM(@tablename) | |
INSERT #T ( name , rows , reserved , data , index_size , unused ) | |
EXEC sp_spaceused @objname = @tablename | |
END; | |
FETCH NEXT FROM tnames_cursor INTO @tablename; | |
END; | |
CLOSE tnames_cursor; | |
DEALLOCATE tnames_cursor; | |
SELECT * FROM #T WHERE rows <> 0 ORDER BY CAST(REPLACE(reserved, ' KB', '') AS BIGINT); | |
DROP TABLE #T; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment