SQL query to list out all tables of a database with rows count, space used etc.

USE [DatabaseName]
SET NOCOUNT ON
CREATE TABLE #ResultTable (
  ObjectName NVARCHAR(120),
  RowsCount CHAR(11),
  Reserved VARCHAR(18),
  DataSize VARCHAR(18),
  IndexSize VARCHAR(18),
  Unused VARCHAR(18)
)

DECLARE AllTables CURSOR FOR
  SELECT Name
  FROM sysobjects WHERE TYPE='U'
  ORDER BY Name ASC

OPEN AllTables
DECLARE @TableName VARCHAR(128)

FETCH NEXT FROM AllTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #ResultTable EXEC sp_spaceused @TableName
  FETCH NEXT FROM AllTables INTO @TableName
END

CLOSE AllTables
DEALLOCATE AllTables 

SELECT * FROM #ResultTable
DROP TABLE #ResultTable

 

Discussion
42 / 7 =
** To prevent abusing comments from publishing, posted comments will be reviewed and then published!
 Mritunjay Kumar
Works at Mindfire Solutions

I mostly work with C#, ASP.NET, MVC, WCF, Web API, Entity FrameWork, MS Sql.

More under this category...
SQL query to search text in all stored procedures of a database
All under this category...