Row count and space used for all tables in database

  • skra

    SSCommitted

    Points: 1614

    Comments posted to this topic are about the item Row count and space used for all tables in database

  • gtossa

    SSC Veteran

    Points: 200

    Very powerful script. Do you have the SQL Server 2000 version of this script?

    Thank you,

    GT

  • skra

    SSCommitted

    Points: 1614

    There has been added a version of this procedure for SQL Server 2000.

    It is waiting for approval now.

  • M Chabot

    SSCrazy

    Points: 2511

    Maybe add

    order by cat_name,sch_name,tab_name

    for the select statement from the temp table.

  • khilanvinchhi

    SSC Enthusiast

    Points: 125

    Hi Rafal,

    How about this code??

    use [YourDatabase];

    select

    a1.object_id

    , a2.name

    , a1.used_page_count * 8 as total_used_pages

    , cast(a1.used_page_count * 8 /1000 as varchar(10)) + 'MB' as SIZE_total_used_pages

    , a1.reserved_page_count * 8 as total_reserved_pages

    , a1.row_count

    from sys.dm_db_partition_stats a1

    inner join sys.all_objects a2 on ( a1.object_id = a2.object_id )

    left outer join sys.indexes a3 on ( (a1.object_id = a3.object_id) and (a1.index_id = a3.index_id) )

    where (select count(distinct partition_number)

    from sys.dm_db_partition_stats a4

    where (a4.object_id = a1.object_id)) >= 1 and a2.type <>'S'

    --and (a1.used_page_count * 8 /1000) > 1 --Uncomment this line to list tables that occupy >1MB space

    order by a1.used_page_count desc, a2.name asc, a1.index_id, a1.partition_number

    I have not compared my code as against yours.. just thought of putting it up here.. so did I. 😀

    Will be glad if you replied on its worth!!! 🙂

    Thanks,

    Khilan

  • A_A_R_T

    SSC Eights!

    Points: 890

    Hi there,

    Thanks for this script...I was looking for a script like this. I tried both the SQL2005 and the SQL2000 script. No problem with the first script but the second wouldn't run.

    Msg 170, Level 15, State 1, Procedure proc_records_per_database, Line 43

    Line 43: Incorrect syntax near '?'.

    I found a little typo in the script when creating the temp table:

    create table #temp_table_list

    (

    rec_id int identity(1, 1) not null,

    cat_name sysname not null,

    sch_name sysname not null,

    tab_name sysname not null,

    row_count bigint not null default 0,

    reserved_pages bigint not null default 0,

    reserved bigint not null default 0,

    pages bigint not null default 0,

    data bigint not null default 0,

    used_pages bigint not null default 0,????

    used bigint not null default 0,

    index_size bigint not null default 0,

    not_used bigint not null default 0,

    primary key(rec_id)

    )

    There are some unrecognized spaces (perhaps a tab) after "used_pages bigint not null default 0,". When you remove those the script works just fine 🙂

  • sunshine-587009

    SSCrazy Eights

    Points: 8812

    Was the one for sql server 2000 approved? I cannot find it.

    ¤ §unshine ¤

  • JamieX

    Hall of Fame

    Points: 3733

    both scripts (2000 & 2005) run perfectly. This is a GOOD ONE.

    Thanks!!

  • smartin-756027

    SSC Enthusiast

    Points: 145

    I have backed into a DBA role from a development background an I wonder why there isn't more discussion of [ Microsoft specific I think] SMO object model that affords you the ability to get row count and size of table plus more C# version of this.

    Down side of this is it is outside the database [Using a database API though] and there is extremely little documentation more hacking around with it to find what is required unless someone knows of a good source[ The most that I have found is a book with three chapters on it that I have on order]

    Certainly having a TSQL script to pull up in a database is probably the norm for DBA's but there could be occasion to write an application or service that may do this on demand or check against a predefined thresh hold and report to you when records exceed a certain predefined number though once again this could be done with a DTS package too

    using System.Data.SqlClient;

    using Microsoft.SqlServer.Management;

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Server;

    using Microsoft.SqlServer.Management.Smo;

    con.ConnectionString = @ConnectStringSA;

    con.Open();

    ServerConnection conn = new ServerConnection(con);

    Server svr = new Server(conn);

    foreach (Table oTable in svr.Databases[@DBName].Tables)

    {

    string sRowCount = oTable.RowCount.ToString();

    string sDataSpace = oTable.DataSpaceUsed.ToString();

    \\Yada Yada ya

    }

    Has anyone else used SMO extensively?

  • Venkatesan Prabu

    SSCommitted

    Points: 1892

    I have written a short article regarding this.

    Please check my blog and provide me your valuable feedback.

    http://venkattechnicalblog.blogspot.com/2008/09/list-out-all-tables-and-row-count-in.html

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • sandeep.cs3

    Old Hand

    Points: 307

    A very crisp and clean way to retrieve all the tables along with its row count

    Row count of all tables in a database

  • YSLGuru

    SSC-Insane

    Points: 21304

    Couple of items to note all of which assume a SQL 2005 platform as you say this code is for:

    1) The call to DBCC UPDATEUSAGE in your ‘proc_records_per_database’ SP is unnecessary per BOL which states “Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.” So you may need to run it the first time you access DB’s upgraded from 2000 to 2005 but after that initial run this is just excess overhead.

    2) Per BOL regarding sysindexes “This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead.” The older SQL 2000 sysindexes is replaced with sys.indexes . Unfortunately the newer sys.indexes does not include rowcount however you can get it thru the SQL 2005 view sys.partitions but if you link it to sys.indexes you’ll need to eliminate the duplication that you get joining the 3 items (sys.tables, sys.indexes & sys.partitions)

    3) Even though you managed to avoid the literal use of a cursor you have 4 instances of RBAR (row by agonizing row) where you’re looping through some set/results.

    4) It may be just me but it seems like there’s a lot going on here just to get the table name, rowcounts and space used. It does do it for all databases if one wishes however if one’s only looking for this info on a single DB which I think would be the more common scenario you can do this thru the join of sys.tables + sis.indexes + dbo.spt_values (located in the MASTER DB).

    This is just my opinion.

    Kindest Regards,

    Just say No to Facebook!
  • YSLGuru

    SSC-Insane

    Points: 21304

    sandeep.cs3 (11/29/2010)


    A very crisp and clean way to retrieve all the tables along with its row count

    Row count of all tables in a database

    I looked at your rowcount sample and its short and sweet which is all the better in T-SQL in a SQL 2000 only environment.

    If though you are working with SQL 2005 or higher, know that per BOL the use of older SQL 2000 table ‘sysindexes’ ( which in 2005 is a view included for backward computability) is not recommended as it will be going away and has been replaced with view sys.indexes . Unfortunately the newer sys.indexes does not include rowcount or an equivalent and so you must join another view (sys.partitions works) or some other object to get your equivalent rowcount value. Note too that depending on whether you have portioned tables or not the method you employ may or may not return the correct rowcount in a partitioned table.

    Kindest Regards,

    Just say No to Facebook!

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply