Dynamic table name in a query

[VBAHole22]

Is it possible to write a T-SQL query that goes through another table and pulls out a field that is the name of a table and another field that is the name of a field and then query for that?
I'm not explaining so well, sorry. I have a table that has id, tablename, field.
I want, for every record in this table, to create a query that will run to the table and get the value. But it's all dynamic because the table name will be changing as will the field names. Can this be done? How?

 

[stevexff]

Yes. You can build up the query string incrementally using a couple of queries, then call sp_executesql to process it. Here's the example from the help that is part of the SQL Server installation

USE Northwind
DECLARE @SQLString NVARCHAR(500)

/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @SQLString = N'SELECT FirstName, LastName, Title' + CHAR(13)

/* Set FROM clause with carriage return, line feed. */
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)

/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''

EXEC sp_executesql @SQLString
GO

The addition of the linefeeds is probably overkill - SQL will be quite happy with spaces