Created
October 12, 2012 06:14
-
-
Save mattiaswolff/3877589 to your computer and use it in GitHub Desktop.
SQL: Get SQL table size
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
DECLARE @TableName VARCHAR(100) --For storing values in the cursor | |
--Cursor to get the name of all user tables from the sysobjects listing | |
DECLARE tableCursor CURSOR | |
FOR | |
select [name] | |
from dbo.sysobjects | |
where OBJECTPROPERTY(id, N'IsUserTable') = 1 | |
FOR READ ONLY | |
--A procedure level temp table to store the results | |
CREATE TABLE #TempTable | |
( | |
tableName varchar(100), | |
numberofRows varchar(100), | |
reservedSize varchar(50), | |
dataSize varchar(50), | |
indexSize varchar(50), | |
unusedSize varchar(50) | |
) | |
--Open the cursor | |
OPEN tableCursor | |
--Get the first table name from the cursor | |
FETCH NEXT FROM tableCursor INTO @TableName | |
--Loop until the cursor was not able to fetch | |
WHILE (@@Fetch_Status >= 0) | |
BEGIN | |
--Dump the results of the sp_spaceused query to the temp table | |
INSERT #TempTable | |
EXEC sp_spaceused @TableName | |
--Get the next table name | |
FETCH NEXT FROM tableCursor INTO @TableName | |
END | |
--Get rid of the cursor | |
CLOSE tableCursor | |
DEALLOCATE tableCursor | |
--Select all records so we can use the reults | |
SELECT * | |
FROM #TempTable | |
--Final cleanup! | |
DROP TABLE #TempTable |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment