Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Query

Author  Topic 

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-08-14 : 16:11:31
I need a query to pull the tables in the database which contains the data more than one or count > 1.

Thanks for your help in advance...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 16:18:55
Huh?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-14 : 16:28:44
Try this:

SELECT t.name as Tablename, MAX(n.rows)as [Rowcount]
FROM sysobjects t inner join sysindexes n
on t.id =n.id
WHERE t.xtype = 'U' AND n.id = OBJECT_ID(t.name)
GROUP BY t.name
Having max(n.rows)>1
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-08-14 : 16:29:09
I am able to get the desired results.


select "TABLE NAME"= convert (varchar (50), so.name), ROWS=si.rows
from sysobjects so, sysindexes si
where so.type = 'U'
and so.id = si.id
and si.indid in (0,1)
order by so.name
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-08-14 : 17:05:50
sodeep Your query works !!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 17:06:02
Perhaps.
Please remember that sysindexes is only an estimated number of records.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-14 : 18:39:00
Agreed .OP is only looking for rowcount>1 thats why i suggest.
Go to Top of Page
   

- Advertisement -