Getting Database Tables Information

Sometimes you will need to know the database table physical size in bytes, especially when you find your databse expanding and growing in a very strange way, and other times you will need to know the row counts of each and every table in your db, so I wrote this stored procedure and it will be executed within the context of the current databse it will give you row counts, data physical size, index physical size, etc...
I used this system stored procedure sp_spaceused, I created a cursor to loop in the databse tables, then it shows the results for each and every table.
Here is the code of the stored procedure.

CREATE procedure GetDatabaseTablesInfo
AS
DECLARE tnames_cursor CURSOR
FOR
select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1
OPEN tnames_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
begin
SELECT @tablename = RTRIM(@tablename)
exec sp_spaceused @tablename
FETCH NEXT FROM tnames_cursor INTO @tablename
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

Note that you can view table sizes from TaskPad inside enterprise manager but this data cannot be xported to an external report, and this script is useful since you can put it inside a job and run it , then sends out an email notifiying you with database tables growth which will increase manageability, Enjoy it !

Links to this post:

Create a Link