Passing variable table name

Question

Hello,

I'm trying to pass variable table name and find the count of each table. I'm using following statement, please guide..

SET @sql1='SELECT COUNT(1) FROM '+@name
EXEC sp_executesql @sql1, @count OUTPUT
INSERT INTO #temp (Table_Name,Record_Count)
VALUES (@name,@count)

Thanks

Answer 1

DECLARE @Count INT;
SET @sql1 ='SELECT @Count=COUNT(1) FROM'+@name 
EXEC sp_executesql 
	@query=@sql1,
	@params=N'@Count INT OUTPUT',
	@Count=@Count OUTPUT
--PRINT @Count
--Use the @Count from there
INSERT INTO #temp (Table_Name,Record_Count)
VALUES (@name,@count) 

---

Narsimha

Answer 2

Blog on counting rows different ways:

http://www.sqlusa.com/bestpractices2005/alltablesrowcount/

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 3

Two small modifications:

SET @sql1 ='SELECT @Count=COUNT(1) FROM'+@name

Should be

  SET @sql1 = 'SELECT @Count=COUNT(1) FROM ' + quotename(@name)

That is, there must be a space after FROM, and use quotename, in case the table name includes funny characters.

---

Erland Sommarskog, SQL Server MVP, [e-mail]