motioneye
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),(((C ONVERT(num eric(15,2) ,SUM(i.res erved)) * (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
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)
CONVERT(numeric(15,2),(((C
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND o.type ='U'
--((@include_system_tables
--((@include_system_tables
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)
--order by [Row Count] desc
) as a
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER