sql query result used in aother query

[pearlofperls]

How can I combine query 1 and query 2 into one big query?


query 1:

SELECT name, crdate from sysobjects where type='U' and name like '%19303%' ORDER BY crdate desc

result 1:

name crdate
11608#M0DAA2D58_TMP 2010-01-06 18:15:23.606
11608#M27042D58_TMP 2010-01-06 18:15:23.606
11608#M3EAD2D58_TMP 2010-01-06 18:15:23.606
11608#M00002D58_TMP 2010-01-06 18:15:03.606
11608#M53DC2D58_TMP 2010-01-06 18:15:03.606

query 2 which uses the result set of query 1:

SELECT
(select count (*) from 11608#M0DAA2D58_TMP) as Table1count,
(select count (*) from 11608#M27042D58_TMP) as Table2count,
(select count (*) from 11608#M3EAD2D58_TMP) as Table3count,
(select count (*) from 11608#M00002D58_TMP) as Table4count,
(select count (*) from 11608#M53DC2D58_TMP) as Table5count


result 2:


1000 900 400 135 5


thanx!!

 

[PDreyer]

If your statistics is up to date

select o.name, o.crdate, 'rowcnt'=sum(rowcnt(i.doampg))
from sysobjects o
join sysindexes i
  on i.id = o.id
where o.type='U'
group by o.name, o.crdate