MS SQL Server/Cursor or ?? to do table record counts
Working with SQL Server 2008 R2. Need to do record counts on a select list of tables (Approx. 100 tables). What I think I want to do is get the list of tables from the all_objects table. Then process thru this list with a select statement. Putting the results into a table. I have been trying to do this with a Cursor but can't seem to get the Select statement to accept the variable as the table name. Am I on the right track or is there a better way to do this?
Despite all the hate cursors get, they are (IMO) perfectly fine for small iterations such as this. Yes, there are non-cursor ways to do it, but often people jump through some fairly amazing hoops just to avoid them, and for no good reason.
I think the problem you're facing is not the cursor but getting the table name (stored in a variable, I assume) into the SELECT COUNT(*) statement.
Hopefully the following will get you on track. I've left the cursor-related stuff out for de-cluttering.
create table #temp (name varchar(100), cnt int) -- assuming you want to store the table name and count
declare @sql nvarchar(1000)
declare @t varchar(100) -- this is the var to populate with the table name inside the cursor loop
--following is inside cursor loop, which populates @t with the FETCH statement
set @sql = 'select ''' + @t + ''', count(*) From [' + @t + ']'
insert into #temp exec (@sql)
#temp should now have the data you want.
If I've missed the mark and this does not address your issue, let me know.