Post reply

how to find name of a table which has maximum number of transactions for a large database

  • December 18, 2012 at 8:39 am

    #272211

    is there a way to find a table which has maximum no. of records among a table list of 100 tables. we need to find tables which has transactions, we want to avoid opening each tables separately to see data

  • December 18, 2012 at 8:41 am

    #1569618

    Transactions or records as they are two completely different things?

  • December 18, 2012 at 8:45 am

    #1569623

    no.of records

  • December 18, 2012 at 8:51 am

    #1569628

    First off run this

    select 'insert into #temp select count(*),'''+name+''' from '+object_schema_name(object_id)+'.'+name+';' from sys.objects where type = 'u'

    Then copy the output from the above and paste it into the comment line below

    create table #temp (records int, tablename sysname)

    --PUT OUTPUT OF THE TOP LINE IN HERE

    select * from #temp order by 1 desc

  • December 18, 2012 at 8:54 am

    #1569630

    another way to get your row counts; the indexes have the rowcounts built into the sys view:

    --for 2005/2008:

    Select OBJECT_NAME(object_id) as TableName,SUM(rows) as NumRows,index_id

    From sys.partitions p

    Inner Join sys.sysobjects o

    on p.object_id = o.id

    Where index_id in (0,1)

    And o.type = 'U'

    Group By object_id,index_id

    Order By NumRows Desc

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 18, 2012 at 8:58 am

    #1569638

    I was always told to not trust the sys view as it can differ from what was in the table, that was back in SQL 2000 days so unsure if they are now more or less in sync with each other.

  • December 18, 2012 at 10:48 am

    #1569684

    anthony.green (12/18/2012)

    I was always told to not trust the sys view as it can differ from what was in the table, that was back in SQL 2000 days so unsure if they are now more or less in sync with each other.

    the old rowcounts in sysindexes, yes, they were unreliable. you'd have to use DBCC UPDATEUSAGE before querying sysindexes to get accurate counts in SQL 2000, as i remember.

    starting with 2005 and above however, the example i posted is always correct; since there is always an index for every row , whether a heap or a clustered index, you can get the counts accurately.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 19, 2012 at 11:50 pm

    #1570406

    thanks for the reponse ..your suggested query worked for me.

    Best regards

Viewing 8 posts - 1 through 7 (of 7 total)

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