Link to home
Start Free TrialLog in
Avatar of motioneye
motioneyeFlag for Singapore

asked on

sql query questions

Guys,
I have a query below to list fr me a table with the row count and its size, btw would someone help me how do I sort this query output with the highest table  on top ?

SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM
        (
        SELECT  QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
                CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)]
        FROM    sysindexes i (NOLOCK)
                        INNER JOIN
                sysobjects o (NOLOCK)
                        ON
                i.id = o.id AND o.type ='U'
                --((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND
                --((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
        WHERE   indid IN (0, 1, 255)
        GROUP BY        QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
            --order by [Row Count] desc
     

        ) as a
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of motioneye

ASKER

Thanks Pawan :)