Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to find name of a table which has maximum number of transactions for a large database Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:17 AM
Points: 8, Visits: 22
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
Post #1397846
Posted Tuesday, December 18, 2012 8:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 5,143, Visits: 4,938
Transactions or records as they are two completely different things?



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397848
Posted Tuesday, December 18, 2012 8:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:17 AM
Points: 8, Visits: 22
no.of records
Post #1397853
Posted Tuesday, December 18, 2012 8:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 5,143, Visits: 4,938
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397858
Posted Tuesday, December 18, 2012 8:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1397861
Posted Tuesday, December 18, 2012 8:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 5,143, Visits: 4,938
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.





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397869
Posted Tuesday, December 18, 2012 10:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1397929
Posted Wednesday, December 19, 2012 11:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:17 AM
Points: 8, Visits: 22
thanks for the reponse ..your suggested query worked for me.

Best regards
Post #1398795
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse