Skip to content
TheChatArchiveThe Chat Archive
gpt-4-1106-preview· OpenAIHugging Face Arena Human Preference 55K

Give me a MSSQL query to list the table sizes of a database

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with gpt-4-0613’s answer.
  1. User

    Give me a MSSQL query to list the table sizes of a database

  2. Assistant

    To list the table sizes in an MSSQL database, you can use a query that utilizes the sys.tables system catalog view, along with the sys.indexes, sys.partitions, and sys.allocation_units system views to calculate the size for each table.

    Here's an example of a query that will list the table sizes in an MSSQL database:

    SELECT 
        t.NAME AS TableName,
        s.Name AS SchemaName,
        p.rows AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKB, 
        SUM(a.used_pages) * 8 AS UsedSpaceKB, 
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE 
        t.NAME NOT LIKE 'dt%' 
        AND i.OBJECT_ID > 255 
        AND i.index_id <= 1
    GROUP BY 
        t.Name, s.Name, p.Rows
    ORDER BY 
        TotalSpaceKB DESC, t.Name
    

    Here's a breakdown of what this query does:

    • t.NAME provides the name of the table.
    • s.Name provides the schema name where the table resides.
    • p.rows provides the count of rows in the table.
    • SUM(a.total_pages) * 8 AS TotalSpaceKB calculates the total space allocated for the table in kilobytes.
    • SUM(a.used_pages) * 8 AS UsedSpaceKB calculates the space actually used by the table in kilobytes.
    • (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB calculates the unused space for the table in kilobytes.

    The * 8 factor converts pages to kilobytes since one page in SQL Server is typically 8 KB.

    The GROUP BY clause is used to aggregate the data per table, and the ORDER BY clause is sorting the results by the total space used in descending order, so the largest tables will be listed first. The WHERE clause filters out system

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.