top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How we can Check Table Size in our database?

0 votes
201 views
How we can Check Table Size in our database?
posted Mar 18, 2016 by Jayshree

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
 
Best answer

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 
answer Mar 18, 2016 by Shivaranjini
...