Dynamic table name selection

[ngandhi]

Hi,

my objective is to find the count of rows in each table.
I tried using the following code, but does not work. Any ideas to get to the end result?

DECLARE @name sysname
declare @objname nvarchar(776) -- The object we want size on.
declare @updateusage varchar(5)
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

declare name_cursor cursor for
select distinct name from sysobjects where type = 'U' order by name

open name_cursor
fetch next from name_cursor into @objname
while @@fetch_status = 0

begin
select count(*) from @objname
fetch next from name_cursor into @objname
end

close name_cursor
deallocate name_cursor

 

[smin]

you need to build a sql string and use sp_executesql.
For example;

declare @sqlstring nvarchar(100)
set @sqlstring = "select count(*) from " + @objname
execute sp_excutesql @sqlstring

smin

 

[RickCole]

Try This:

DECLARE @name sysname
declare @objname nvarchar(776) -- The object we want size on.
declare @updateusage varchar(5)
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

declare name_cursor cursor for
select distinct name from sysobjects where type = 'U' order by name

open name_cursor
fetch next from name_cursor into @objname
while @@fetch_status = 0

begin
exec ('select count(*) from ' + @objname )
--select count(*) from @objname
fetch next from name_cursor into @objname
end

close name_cursor
deallocate name_cursor


Rick.

 

[ngandhi]

thanks rick.
It works great!

 

[fluteplr]

Or Try this.

set quoted_identifier off
go

sp_msforeachtable
"declare @count int select @count=count(*)from ?
if @count > 0 begin print '?' print @count end"

 

[tlbroadbent]

Here is another method that is very quick.

Select RowCnt=Max(i.Rows), TblName=o.name
From sysindexes i Inner Join sysobjects o
On i.id=o.id
Where o.type='u'
Group By o.Name
Order by o.name
Go

Or you might try thisa modification of fluteplr's recommendation.

Set nocount on

Create table #tmpcnt (RowCnt int, TblName sysname)

Insert #tmpcnt
exec sp_msforeachtable
"Select RowCnt=count(*), TblName='?' From ?"

Select * From #tmpcnt
Order By tblname

Drop table #tmpcnt

set nocount off Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.