Q:

SQL query to dynamically COUNT(FIELD) for all fields of table X

This should be such an easy thing, but it has me totally stumped.
You can easily return the count of each field of a table manually, with oneliners such as:
select count(FIELD1) from TABLE1   --42,706
select count(FIELD5) from TABLE1   --42,686
select count(FIELD9) from TABLE1   --2,918

This is slow and painful if you want to review several dozen tables the same way, and requires you to know the names of the fields in advance.
How handy would it be to have a script you can connect to any database, simply feed it a table name, and it will automatically return the counts for each field of that table?
Seems you can get half the work done with:
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TABLE1'

Something is flawed even with my barebones approach (explicitly hitting one field instead of them all):
declare @TABLENAME varchar(30), @FIELDNAME varchar(30)
set @TABLENAME = 'TABLE1'

set @FIELDNAME = (select top 1 COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TABLENAME
and COLUMN_NAME = 'FIELD9')

select @FIELDNAME, count(@FIELDNAME) from TABLE1

The result is 42,706.  Recall from my example above that FIELD9 only contains 2,918 values.
Even if that wasn't a problem, the more dynamic query would replace the last line with:
select @FIELDNAME, count(@FIELDNAME) from @TABLENAME

But SQL Server returns:
Must declare the table variable "@TABLENAME".

So I can avoid that by restructuring the query with a temp table:
declare @FIELDNAME varchar(30)

set @FIELDNAME = (select top 1 COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TABLE1'
and COLUMN_NAME = 'FIELD9')

if OBJECT_ID('TEMPDB..#TEMP1') is not null
    drop table #TEMP1

select *
into #TEMP1
from TABLE1  --still not exactly dynamic!

select @FIELDNAME, count(@FIELDNAME) from #TEMP1

But that still brings us back to the original problem of returning 42,706 instead of 2,918.
I am running SQL Server 2008 R2, if it makes any difference.

A:

Your query:
SELECT @FIELDNAME, COUNT(@FIELDNAME) FROM TABLE1

does not count FIELD9, @FIELDNAME is treated as a constant. It's like doing a COUNT(*).
You should use dynamic sql:
DECLARE @sql VARCHAR(MAX)

SET @sql = 'SELECT ''' + @fieldName + ''', COUNT([' + @fieldName + ']) FROM [' + @tableName + ']'

EXEC(@sql)

To get all columns and return it in a single result set without using a Temporary Table and CURSOR:
DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql +
'SELECT ''' + COLUMN_NAME + ''' AS ColName, COUNT([' + COLUMN_NAME + ']) FROM [' + @tableName + ']' + CHAR(10) +
'UNION ALL' + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName

SELECT @sql = LEFT(@sql, LEN(@sql) - 10)
EXEC(@sql)

