Scenario Proposal

While working on different database projects I usually find it very necessary to perform a data sizing exercise on all tables focusing mostly on the number of rows in order to determine a growth factor of each table. This kind of information is useful in performing long term planning of growth and scalability of the data that we are managing.

Out of an exercise like this one we can also determine future needs for data archiving.

Solution

The most trivial way of achieving this task would be perform an “Select Count(1) from” each of the tables of our database but this could create a lot of overhead especially for large databases and large tables.

The optimal way to achieve this is by using the sp_spaceused system stored procedure.

Sample code below to outline the usage:

— variable declaration  — www.dotnetsql.info

— declare @n to store the total number of tables

declare @n int

— declare @i to use it as an index

declare @i int

— declare @tn to retrieve the tablename on the @i position

declare @tn varchar(1000)

set         @i = 1

— table to store all the tablenames in scope

declare @t table(

id int identity(1,1),

name varchar(1000)

)

— table to store the return results

declare @tempt table

(  

tableName varchar(100),

numberofRows varchar(100),

reservedSize varchar(50),

dataSize varchar(50),

indexSize varchar(50),

unusedSize varchar(50)

)

— code  —- www.dotnetsql.info

— insert all the tables in the database in a temporary table

insert into @t

                        (name)

select                  s.name+'.'+t.name

from  sys.tables  t

join  sys.schemas s     on s.schema_id = t.schema_id

— get the rowcount of the temporary table

set @n = @@ROWCOUNT+1

— loop through the table to retrieve table names

while (@i<@n)           begin

— get the current table name

      select @tn = name

      from  @t

      where id = @i

— insert the result of sp_spaceused into the results table

      insert into @tempt

      EXEC sp_spaceused @tn

— increment the current row

      set @i=@i+1

end

— return the rowcount and data usage

select *

from @tempt

Thanks for reading this article,

Next steps :

1. Add this script to your database toolkit

2. Share this with your colleagues because Sharing is Learning

3. Comment below if you need any assistance

Powered by CodeReview – Let's make it Better!