Using variables in From Clause 1

[Sunny4Ever]

Hi all,

I was trying to write a query which will return the name of each user table in a database and the count of rows in each table.

--*********************************************************
DECLARE @MyTableName varchar(30)
DECLARE MyCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @MyTableName

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT COUNT(*) FROM @MyTableName
END

CLOSE MyCursor
DEALLOCATE MyCursor

--*********************************************************

But SQL 2000 returns the following error:

"Must declare the variable '@MyTableName'."

So it looks like you cannot use a variable in a FROM clause!! Any one have any information on this or ideas on how to get the end result?

 

[micha123]

You can't use table name as variable. You must do that in dynamic SQL:

DECLARE @MyTableName varchar(30)
DECLARE MyCursor CURSOR FOR 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @MyTableName

WHILE @@FETCH_STATUS = 0
BEGIN
  exec ('SELECT COUNT(*) FROM ' + @MyTableName )
END

CLOSE MyCursor
DEALLOCATE MyCursor

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[JamesLean]

This might be useful for you:

CREATE TABLE #tables (
	name sysname,
	rows int
)

INSERT #tables
EXEC sp_msforeachtable 'SELECT ''?'', COUNT(*) FROM ?'

SELECT * FROM #tables
ORDER BY name

--James

 

[jbenson001]

Great post James.. will come in handy