Dynamic table name from varchar field

Hi,
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>