HOWTO: Find out the Sizes and Rowcounts of All the User Tables in a Database in SQL (UPDATED! - Now includes the RedGate SQLPrompt Snippet Text)

Here is an update on a blog post that I wrote a decade ago for finding the size and rowcounts for all user tables in a database:


--For storing values in the cursor
DECLARE @TableName VARCHAR(100)
DECLARE @SchemaNameAndTableName VARCHAR(100)

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT
        so.name AS TableName,
        s.name + '.' + so.name AS FullTableName
    FROM
        sys.objects so INNER JOIN
        sys.schemas AS s ON so.schema_id = s.schema_id
    WHERE
        so.type = 'U'
        AND s.name + '.' + so.name LIKE '%%'
    ORDER BY
        FullTableName

--A procedure level temp table to store the results
DECLARE @TempTable AS Table
        (
         TableName VARCHAR(100),
         NumberOfRows BIGINT,
         ReservedSizeKB VARCHAR(50),
         DataSizeKB VARCHAR(50),
         IndexSizeKB VARCHAR(50),
         UnusedSizeKB VARCHAR(50)
        )

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName, @SchemaNameAndTableName

--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 @SchemaNameAndTableName
    UPDATE @TempTable SET TableName = @SchemaNameAndTableName WHERE TableName = @TableName
    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName, @SchemaNameAndTableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Strip out the " KB" out of the size strings so that they can be ordered.
UPDATE
    @TempTable
SET  
    ReservedSizeKB = LEFT(ReservedSizeKB, LEN(ReservedSizeKB) - 3),
    DataSizeKB = LEFT(DataSizeKB, LEN(DataSizeKB) - 3),
    IndexSizeKB = LEFT(IndexSizeKB, LEN(IndexSizeKB) - 3),
    UnusedSizeKB = LEFT(UnusedSizeKB, LEN(UnusedSizeKB) - 3)

--Select all records so we can use the results.
--Optionally ORDER BY ReservedSizeKB
SELECT 
    TableName,
    NumberOfRows,
    ROUND((CAST(ReservedSizeKB AS FLOAT)/1024.0/1024.0),2) AS ReservedSizeGB,
    ROUND((CAST(DataSizeKB AS FLOAT)    /1024.0/1024.0),2) AS DataSizeGB,
    ROUND((CAST(IndexSizeKB AS FLOAT)   /1024.0/1024.0),2) AS IndexSizeGB,
    ROUND((CAST(UnusedSizeKB AS FLOAT)  /1024.0/1024.0),2) AS UnusedSizeGB
FROM
    @TempTable
WHERE
    1=1
    AND 1=1
ORDER BY
    --ReservedSizeGB DESC
    TableName
    --NumberOfRows DESC

Here is the RedGate SQLPrompt Snippet text:

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets>
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>TableSizes</Title>
      <Shortcut>TableSizes</Shortcut>
      <Description>AJ - Gets the sizes and rowcounts of all the tables in the database.</Description>
      <Author />
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations />
      <Code Language="sql"><![CDATA[--For storing values in the cursor
DECLARE @TableName VARCHAR(100)
DECLARE @SchemaNameAndTableName VARCHAR(100)

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT
        so.name AS TableName,
        s.name + '.' + so.name AS FullTableName
    FROM
        sys.objects so INNER JOIN
        sys.schemas AS s ON so.schema_id = s.schema_id
    WHERE
        so.type = 'U'
        AND s.name + '.' + so.name LIKE '%$CURSOR$%'
    ORDER BY
        FullTableName

--A procedure level temp table to store the results
DECLARE @TempTable AS Table
        (
         TableName VARCHAR(100),
         NumberOfRows BIGINT,
         ReservedSizeKB VARCHAR(50),
         DataSizeKB VARCHAR(50),
         IndexSizeKB VARCHAR(50),
         UnusedSizeKB VARCHAR(50)
        )

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName, @SchemaNameAndTableName

--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 @SchemaNameAndTableName
    UPDATE @TempTable SET TableName = @SchemaNameAndTableName WHERE TableName = @TableName
    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName, @SchemaNameAndTableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Strip out the " KB" out of the size strings so that they can be ordered.
UPDATE
    @TempTable
SET  
    ReservedSizeKB = LEFT(ReservedSizeKB, LEN(ReservedSizeKB) - 3),
    DataSizeKB = LEFT(DataSizeKB, LEN(DataSizeKB) - 3),
    IndexSizeKB = LEFT(IndexSizeKB, LEN(IndexSizeKB) - 3),
    UnusedSizeKB = LEFT(UnusedSizeKB, LEN(UnusedSizeKB) - 3)

--Select all records so we can use the results.
--Optionally ORDER BY ReservedSizeKB
SELECT 
    TableName,
    NumberOfRows,
    ROUND((CAST(ReservedSizeKB AS FLOAT)/1024.0/1024.0),2) AS ReservedSizeGB,
    ROUND((CAST(DataSizeKB AS FLOAT)    /1024.0/1024.0),2) AS DataSizeGB,
    ROUND((CAST(IndexSizeKB AS FLOAT)   /1024.0/1024.0),2) AS IndexSizeGB,
    ROUND((CAST(UnusedSizeKB AS FLOAT)  /1024.0/1024.0),2) AS UnusedSizeGB
FROM
    @TempTable
WHERE
    1=1
    AND 1=1
ORDER BY
    --ReservedSizeGB DESC
    TableName
    --NumberOfRows DESC
]]</Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>