Query - SQL Server Forums

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"

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

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

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-08-14 : 17:05:50

sodeep Your query works !!

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"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-14 : 18:39:00