You are here:

MS SQL Server/Cursor or ?? to do table record counts

Advertisement


Question
David,

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?

Thanks,

Richard

Answer
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

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

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

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.