You are here:

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.

MS SQL Server

All Answers

Answers by Expert:

Ask Experts


David Vaughn


Performance tuning; T-SQL syntax;


Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Truman State University

©2017 All rights reserved.

[an error occurred while processing this directive]