SQL 2008

SQL Table Space Script : This sql query uses "sp_mstablespace" stored procedure to get table space for all the user table in the server.


Code :

SELECT a.[name] as 'Table',
(select convert(int, rowcnt)
  FROM dbo.sysindexes
  WHERE indid < 2  and id=a.id) as rows,
 (((SELECT sum(dpages)
  FROM dbo.sysindexes
  WHERE indid < 2 and id = a.id)
 +
 (SELECT isnull(sum(used), 0)
  FROM dbo.sysindexes
  WHERE indid = 255 and id = a.id))*8) as DataSpaceUsed,
(((SELECT sum(used)
  FROM dbo.sysindexes
  WHERE indid in (0, 1, 255) and id = a.id) - ((SELECT sum(dpages)
  FROM dbo.sysindexes
  WHERE indid < 2 and id = a.id)
 +
 (SELECT isnull(sum(used), 0)
  FROM dbo.sysindexes
  WHERE indid = 255 and id = a.id))
)*8) as IndexSpaceUsed

FROM  sysobjects a
WHERE a.[xtype] = 'u'
AND  a.[name] <> 'dtproperties'
ORDER BY a.[name]