Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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 non t.id =n.idWHERE t.xtype = 'U' AND n.id = OBJECT_ID(t.name) GROUP BY t.nameHaving 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
Agreed .OP is only looking for rowcount>1 thats why i suggest.