SQL Server Performance

Finding tables having Max rows

Discussion in 'Contribute Your SQL Server Scripts' started by gopi19, Nov 12, 2003.

  1. gopi19 New Member

    This small script might be handy to find out tables having max rows,
    nothing great though.

    Run statements below from SQL query analyser.

    set nocount on
    select 'create table #big_tables
    Table_namevarchar(75) null,
    Row_countint null
    )' +char(13) +'set nocount on '+char(13) +'declare @Table_name as varchar(75)' +char(13)+ 'declare @count as integer'

    select 'set @table_name = '+''''+ table_name +''''+char (13)+
    'begin '+ char(13)+ 'select @count = count(*) from ' + table_name +char(13)+
    'if @count > 100000 '
    + char(13)+ char(13) + 'insert into #big_tables (table_name, Row_count) select @table_name, @count ' +char(13)+ 'end'+char(13) from information_schema.tables where table_type ='base table'
    print 'Select * from #big_tables order by row_count desc ' +char(13) + 'go ' +char(13)+ 'drop table #big_tables'+char(13)

    Copy the result (script) from the result pane in to the SQL Query window and run again.
    Now You get the list of tables having rowcount more than 100000 are now listed in ascending order.
    This number can be actually changed from the SQL statement part.
    Further EXEC sp_spaceused can be used to find more about the interested tables.

    All the objects are under dbo ownership within the Schema.
    SQL Query analyser is set to Text mode.

    Thanks to Forum Admin and others for accommodating my small script.
  2. ChrisFretwell New Member

    Nice little script, but wouldnt this do the same thing

    select so.name, si.rows from sysobjects so join sysindexes si on so.id = si.id
    where si.indid = 0 and rows > 1000000

    I admit I started with sql in version 4.2 when there was no enterprise manager so I still tend to use system tables for some things. Querying system tables isnt for everyone and as M$ points out, they can change with the next release, so dont create critical path processes against them. But for something liks this, it should work well.


  3. gopi19 New Member

    Thanks Chris,
    That is simply wonderful to have such a small statement.

    On a test database here, I created 19 tables having rowcount > 10000.

    Using Your SQL statement, I got only one table listed
    where I expected 19 tables to appear in the result pane.

    When I ran my query, I got all the 19 tables listed on a test Server.
    All the objects were created and owned by dbo during my test.

    I was wondering about any SQL code or other problems.
    Do I have to alter this code You have given?
    I liked the single line statement, but is not working for me.

    I can send You the screen shots of my testing if Your email id can be provided.

  4. ChrisFretwell New Member

    The code I'm using depends on the statistics being up to date. It uses the same tables that would be used if you did sp_spaceused


    sp_spaceused tablename

    where tablename is one of the tables that returned from your script that didnt in the 1 line select. If you get different results, then you should probably consider updating your statistics. SQL uses the values in the statistic tables to determine the 'optimized' execution plan for queries etc.

    Try and let me know. Although screen shots are probably not necessary, my email is chris.fretwell@teligencecom.com


  5. gopi19 New Member

    Hi Chris,
    I updated the statistics using
    sp_updatestats on the user database as per Your suggestion.
    (I even ran the statement on master database. We are using SQL 2000 sp3 here).

    But that does not help to get the right results from Your query.

    I have sent You the results of the statements (screen shots).
    Hope you don't mistake me for testing the SQL codes (Yours and Mine).
    Nice to know you.
    Thanks to You and to the SQL community.
  6. ChrisFretwell New Member

    No problem at all. Like I said, you're script is good and will be the most accurate count since it checks the table. I might add the optimizer hint nolock (for sql 7 at least) on the count(*) since on some very large tables, this may take a few seconds.

    You may need to update your indexes then update statistics to get the numbers to jive perfectly. Actually, the combination of your script with the system table select might be great for identifying tables that need their indexes rebuilt and/or their statistics updated. You can compare the value returned in the #big_tables (actual count) to the number the optimizer will use (from sysindexes) and report any with a certain % difference as candidates for index and/or stat updates. Hmmmm. Interesting idea.

  7. gopi19 New Member

    Thanks Chris,
    Good to know You all and thanks for the participation.
    I am sure the SQL statements do not matter as there is no best coding.
    I am impressed with Your frankness and helping attitude. Nice to know the community.
    Thanks again

Share This Page