>>I am attempting to display a count of the rows in a table called ContactUpdates in the AP Database. However, I am using a variable named @TableNameVar to contain the name of the table which I obtain by using a SQL statement sort of like a derived table, as in this: SET @TableNameVar = 'SELECT MIN(name) from sys.tables' , but it isn't quite working yet. This is for an exercise in my T-SQL class.
>>
>>

>USE AP
>
>DECLARE @DynamicSQL VARCHAR(max)
>DECLARE @TableNameVar sysname
>SELECT @TableNameVar = MIN(name) from sys.tables -- here we don't need dynamic SQL, we need to get the name of the table
>SET @DynamicSQL = 
>	'SELECT COUNT(*) AS [Countof' + (@TableNameVar) + '] FROM ' + quotename(@TableNameVar)
>--print (@DynamicSQL) -- we may want to use PRINT to debug the SQL
>EXEC (@DynamicSQL)
>>

>
>Cecil,
>
>See several fixes inline.

BTW, here is a slightly modified script with the table name as well:

DECLARE @DynamicSQL VARCHAR(max)
DECLARE @TableNameVar sysname
SELECT @TableNameVar = MIN(name) from sys.tables -- here we don't need dynamic SQL, we need to get the name of the table
SET @DynamicSQL = 
	'SELECT ' + quotename(@TableNameVar,'''') + ' as TableName, COUNT(*) AS [Countof' + (@TableNameVar) + '] FROM ' + quotename(@TableNameVar)
--print (@DynamicSQL) -- we may want to use PRINT to debug the SQL
EXEC (@DynamicSQL)

If you want to get count of records for all tables in a database, it's easy as well - check my recent blog How to get information about all databases without a loop