A query to get the user table which has maximum number of records 
Author Message
 A query to get the user table which has maximum number of records
Hi all,

I want to write a query to get the user table name which has maximum number
of records in a database.

For example, database TestDB has three user tables: test1 has 10 records,
test2 has 20 records, and test3 has 30 records. Then the query returns
?test3?.

Thank you for your help!

Frankie.



Tue, 28 Dec 2004 18:00:23 GMT
 A query to get the user table which has maximum number of records

Frankie,

If you can accept that the information might be incorrect, you can use the sysindexes table:
SELECT top 1 object_name(id)
FROM sysindexes
where indid in(0,1)
ORDER BY rowcnt

If you can't accept that the data might be off, you have to do a SELECT COUNT(*) against each
table, and save the data (perhaps in a temp table), and then select the max from there. The
looping can be done using a cursor.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> Hi all,

> I want to write a query to get the user table name which has maximum number
> of records in a database.

> For example, database TestDB has three user tables: test1 has 10 records,
> test2 has 20 records, and test3 has 30 records. Then the query returns
> ?test3?.

> Thank you for your help!

> Frankie.



Tue, 28 Dec 2004 18:16:27 GMT
 A query to get the user table which has maximum number of records
Ahh, yes I forgot to exclude the system tables. And, again, be aware that the info might be
off...

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> Hi Tibor,

> I change a little of your script, and now I get the answer.

> SELECT top 1 object_name(id)
> FROM sysindexes
> where indid in(0,1)
> and objectproperty(id,'IsMSShipped')=0
> ORDER BY rowcnt DESC

> Thanks,

> Frankie.



> > Frankie,

> > If you can accept that the information might be incorrect, you can use the
> sysindexes table:
> > SELECT top 1 object_name(id)
> > FROM sysindexes
> > where indid in(0,1)
> > ORDER BY rowcnt

> > If you can't accept that the data might be off, you have to do a SELECT
> COUNT(*) against each
> > table, and save the data (perhaps in a temp table), and then select the
> max from there. The
> > looping can be done using a cursor.

> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...



> > > Hi all,

> > > I want to write a query to get the user table name which has maximum
> number
> > > of records in a database.

> > > For example, database TestDB has three user tables: test1 has 10
> records,
> > > test2 has 20 records, and test3 has 30 records. Then the query returns
> > > ?test3?.

> > > Thank you for your help!

> > > Frankie.



Tue, 28 Dec 2004 19:35:54 GMT
 A query to get the user table which has maximum number of records
Hi Tibor,

I change a little of your script, and now I get the answer.

SELECT top 1 object_name(id)
FROM sysindexes
where indid in(0,1)
and objectproperty(id,'IsMSShipped')=0
ORDER BY rowcnt DESC

Thanks,

Frankie.



Quote:
> Frankie,

> If you can accept that the information might be incorrect, you can use the
sysindexes table:
> SELECT top 1 object_name(id)
> FROM sysindexes
> where indid in(0,1)
> ORDER BY rowcnt

> If you can't accept that the data might be off, you have to do a SELECT

COUNT(*) against each
Quote:
> table, and save the data (perhaps in a temp table), and then select the
max from there. The
> looping can be done using a cursor.

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...
Quote:




Quote:
> > Hi all,

> > I want to write a query to get the user table name which has maximum
number
> > of records in a database.

> > For example, database TestDB has three user tables: test1 has 10
records,
> > test2 has 20 records, and test3 has 30 records. Then the query returns
> > ?test3?.

> > Thank you for your help!

> > Frankie.



Tue, 28 Dec 2004 19:23:41 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. What is the maximum number of records in Pdradox 5 table

2. Maximum number of tables/joins in query

3. System Error 4414 (Exceeded maximum number of tables in a query)

4. Increasing the maximum number of tables allowed in a query

5. Maximum number of tables in a query (16) exceeded

6. Maximum number of tables in a query (16) exceeded

7. Maximum number of table names in a query

8. Getting the number of a record added to a table

9. Getting/Setting Record Number in VB4 Tables?

10. I am getting duplicate records, I mean everything is duplicate even Access record number

11. Count record number for each tables of one user


 
Powered by phpBB® Forum Software