Get counts of views - 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

General SQL Server Forums

Script Library

Get counts of views

Author

Topic

Samadhi69
Starting Member

20 Posts

Posted - 2009-02-04 : 15:05:28

I want to get the number of records in a particular set of views on a database.* I do not have access to the tables these views are based on so some code I have found for getting counts of tables is not an option. I have a table with the view names I want to get counts for and a column I would like to update with those counts. Here is my first attempt:

declare @table varchar(50) --table name
declare @count int(10) --table count

declare cc_cursor cursor for
select table_name
from table_counts

open cc_cursor
fetch next from cc_cursor into @table

while @@fetch_status = 0
begin
	set @count = (select count(*) from @table)
	update table_counts
	set numrecords = @count
	fetch next from cc_cursor into @table
end

close cc_cursor
deallocate cc_cursor

I get the error "Must declare the variable '@table'. If anyone knows a way around this, or a better way to do this, I would appreciate it.

*If you really want to know why, I'll answer but trust me it will be a time saver.

If con is the opposite of pro, what's the opposite of progress?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-04 : 15:31:52

Not sure why you were updating all rows in table_counts with the count returned. I made a small change there too.

declare @table varchar(50) --table name

declare @count int --table count

declare cc_cursor cursor for
select table_name
from table_counts

open cc_cursor
fetch next from cc_cursor into @table

while @@fetch_status = 0
begin
	exec ('
	declare @count int --table count
	select @count=count(*) from '+@table+' ;

	update table_counts
	set numrecords = @count
	where table_name='''+@table+'''
')

	fetch next from cc_cursor into @table
end

close cc_cursor
deallocate cc_cursor

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-04 : 15:35:58

rather use this,

declare @table varchar(50) --table name
declare cc_cursor cursor for
select table_name
from table_counts

open cc_cursor
fetch next from cc_cursor into @table

while @@fetch_status = 0
begin
	exec  ('
	update table_counts
	set numrecords = (select count(*) from '+@table+')
	where table_name='''+@table+'''
')

	fetch next from cc_cursor into @table
end

close cc_cursor
deallocate cc_cursor

Samadhi69
Starting Member

20 Posts

Posted - 2009-02-04 : 15:50:50

Thanks that worked perfectly. I'll have to read up on the exec command, I haven't used it much.

quote:
Not sure why you were updating all rows in table_counts with the count returned.

Because there are hundreds of views. Essentially, if the count is below a certain level I'm not going to worry about making it more efficient. If however, as is the with maybe 10% of them, the view has over a million records I want to only grab new data. This will take custom code for each table (determining which column, if any, is updated with a change date) so I want to do as few as possible. With all of the counts in a table, it's easy to check.

If con is the opposite of pro, what's the opposite of progress?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources