SQL Server Performance

SQL Script for table

Discussion in 'SQL Server 2008 General DBA Questions' started by Katy, Jan 8, 2011.

  1. Katy New Member

    Hi ,
    Can somebody please let me know the script to list the size of all the tables in mega byte and the row count.
    Thank you
  2. Luis Martin Moderator

    Welcome to the forums.!
    Check:
    http://sql-server-performance.com/Community/forums/t/32379.aspx
  3. RamJaddu Member

    here is the script
    ------Script below
    Declare c cursor for select name from sysobjects where type='U' and name <>'dtproperties'
    Declare @name varchar(255)
    open c
    fetch next from c into @name
    Declare @TSpace table(name varchar(255),rows int,resevered varchar(20),data varchar(20),index_size varchar(20),unused varchar(20))
    while @@fetch_status = 0
    begin
    insert into @TSpace exec sp_spaceused @objname=@name
    fetch next from c into @name
    end
    close c
    deallocate c
    select
    Name
    ,rows
    ,round(Left(cast(replace(resevered,'KB','') as float)/ (1024),100),1) Reserved
    ,round(Left(cast(replace(Data,'KB','') as float)/ (1024),100),1) Data
    ,round(Left(cast(replace(index_size,'KB','') as float)/ (1024),100),1) Index_size
    ,round(Left(cast(replace(unused,'KB','') as float)/ (1024),100),1) Freespace
    from @TSpace order by 2 desc

Share This Page