Steps you will do.
Open the Enterprise Manager and in your database create a new store procedure with any name that you wish. Copy and paste the following code in the body of store procedure save the store procedure and open the query analyzer. Call the store procedure and see the results. You will see the name of each table in your data base, number of rows in the table and size of the table in the data base, etc. The store procedure is as below.
CREATE PROCEDURE CheckSpace
AS
BEGIN
DECLARE @tablename sysname
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC ('sp_spaceused ' + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
END
GO
