I am working with a routine that manages several databases. Each DB has an activity log. I want to loop through the activity logs and get the latest activity date (formatted yyyy-MM-dd hh:mm ) for each DB.

If I hard code the database/table name into this statement, it works just fine.

SET @LatestLogDT = (SELECT FORMAT(MAX([ActivityDT]), N'yyyy-MM-dd hh:mm') FROM MyDB.dbo.MyTable);

However, if I put this in a loop and try to insert the db/table name as a dynamic variable, it fails, saying I must declare the variable. I've whittled the code down as thin as possible to demonstrate the issue. In this example, I declare @DBTableName as the db/table name, and I'm manually assigning here to show the problem without having to include the looping code, which is just a distraction.

DECLARE @DBTableName VARCHAR(50) = '[MyDB].[dbo].[MyTable]';
DECLARE @LatestLogDT VARCHAR(25);

SET @LatestLogDT = (SELECT FORMAT(MAX([ActivityDT]), N'yyyy-MM-dd hh:mm') FROM @DBTableName);
SELECT @LatestLogDT

However, when I run this code, I get this error message:

Must declare the table variable "@DBNAME".

Next, I tried creating an SQL string and executing that string, but I get the same error.

I feel like I've seen this done before, but clearly I'm missing something. Probably sometime quite simple. Hoping one of you geniuses can put me on the right track.