SQL: count and show all rows and columns of all tables under a scheme

Question

somewhere on the net (source missing, may be here) I got this SQL-statement:

SELECT t.NAME AS TableName, SUM(a.total_pages) * 8 AS TotalSpaceKB, p.Rows 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 t.NAME LIKE '%MY_TABLES%' AND s.Name LIKE 'MY_ACHEMA'
  AND t.is_ms_shipped = 0
  AND i.OBJECT_ID > 255
GROUP BY t.Name, p.Rows
ORDER BY p.Rows DESC

which gives me a beautiful view with 3 columns. something like this:

           TableName  TotalSpaceKB     Rows
               TABLE       3231656    76000
               TABLE       2305632    29136
               TABLE       2213128    14160
               TABLE       1954200     3020
etc...

now I'd like to expand it to inclide the number of columns of each table. How can this be done?

Answer 1

I removed the grouping and used subqueries:

SELECT
    t.NAME AS TableName,
    (SELECT SUM(a.total_pages) * 8 
        FROM sys.indexes i
        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 
        WHERE t.OBJECT_ID = i.object_id AND i.OBJECT_ID > 255
    ) AS TotalSpaceKB,
    (SELECT SUM(p.Rows)
        FROM sys.indexes i
        INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
        WHERE t.OBJECT_ID = i.object_id AND i.OBJECT_ID > 255
    ) AS Rows,
    (SELECT COUNT(*) FROM sys.columns c WHERE t.OBJECT_ID = c.object_id) AS Columns
FROM sys.tables t 
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id 
WHERE t.NAME NOT LIKE 'dt%' AND t.NAME LIKE '%MY_TABLES%' AND s.Name LIKE 'MY_ACHEMA'
  AND t.is_ms_shipped = 0
ORDER BY Rows DESC