Converting a string to an object reference

Question

Sometimes I end up in a position where I have a string version of my object reference like so:

'server.database.schema.table'

And I need to use it as an actual object reference:

'server.database.schema.table'+'.'+[column]

How do I turn that string into:

[server].[database].[schema].[table]

so I can actually use it?

I'm sure this has been asked before, and it's probably newbish, but I just can't find the right keywords. I'm not using it in an EXEC EXECUTE.

Per the comments, here's an example dataset:

CREATE TABLE #example
(
 Server NVARCHAR(255) NOT NULL
 ,[Database] NVARCHAR(255) NOT NULL
 ,[Schema] NVARCHAR(255) NOT NULL
 ,[Table] NVARCHAR(255) NOT NULL
 ,Field1 INT
 ,Field2 DATE
 ,Field3 VARCHAR(50)
)
INSERT INTO #example
(Server,[Database],[Schema],[Table],Field1,Field2,Field3)
VALUES ('LinkedServer','DB','dbo','TableA',15,CONVERT(DATE,'1-1-18'),'Text')

So, if I wanted to select the reference of the first row, I'd imagine I'd do something like:

DECLARE @SERVER ??, @DB ??, @SCHEMA ??, @TABLE ??
SELECT TOP 1 @SERVER = Server FROM #example
SELECT TOP 1 @DB = [Database] FROM #example
SELECT TOP 1 @SCHEMA = [Schema] FROM #example
SELECT TOP 1 @TABLE = [Table] FROM #example
SELECT * FROM
 @SERVER.@DB.@SCHEMA.@TABLE
WHERE 
 Field1 = (SELECT TOP 1 Field1 FROM #example)
 AND Field2 = (SELECT TOP 1 Field2 FROM #example)
 AND Field3 = (SELECT TOP 1 Field3 FROM #example)

• Erik Reasonable Rates Darling: Can you give an example of how you'd use the code you're trying to run?
• paparazzo: TSQL has objects?
• undrline - Reinstate Monica: @paparazzo I'm used to Hyperion Business Intelligence; idk the appropriate term.
• undrline - Reinstate Monica: @sp_BlitzErik I was trying to keep it general because it comes up quite often. My current project I literally have a table with columns server, database, schema, table, and then a list of various fields I need to anti-join if they're all a match. For those first columns, the values are stored as nvarchar(255).
• Erik Reasonable Rates Darling: Okay, cool, go ahead and update your question with an example of what you're trying to do with that very literal table full of very literal values.

Answer 1

What you're looking to do is called dynamic SQL.

Using your example, I think you want to do something like this:

DECLARE @SERVER sysname, 
 @DB sysname, @SCHEMA sysname, 
 @TABLE sysname,
 @FIELD1 INT,
 @FIELD2 DATE,
 @FIELD3 VARCHAR(50);
SELECT TOP 1 @SERVER = Server,
 @DB = [Database], 
 @SCHEMA = [Schema],
 @TABLE = [Table],
 @FIELD1 = Field1,
 @FIELD2 = Field2,
 @FIELD3 = Field3
FROM #example
--ORDER BY Server
--You need to order by a unique column or set of columns to make this determinstic
DECLARE @StringToExecute NVARCHAR(MAX) = N''
SET @StringToExecute += N'
SELECT * 
FROM ' 
 + QUOTENAME(@SERVER)
 + N'.'
 + QUOTENAME(@DB)
 + N'.'
 + QUOTENAME(@SCHEMA)
 + N'.'
 + QUOTENAME(@TABLE) 
 + N'
WHERE 
 Field1 = @sp_FIELD1
 AND Field2 = @sp_FIELD2
 AND Field3 = @sp_FIELD3
';
PRINT @StringToExecute;
EXEC sys.sp_executesql @StringToExecute, 
 N'@sp_FIELD1 INT, @sp_FIELD2 DATE, @sp_FIELD3 VARCHAR(50)',
 @sp_FIELD1 = @FIELD1, @sp_FIELD2 = @FIELD2, @sp_FIELD3 = @FIELD3;

Take note of a couple things:

• I'm using QUOTENAME to escape objects that I can't parameterize -- the server, database, schema, and table names.

• I'm using sp_executesql to parameterize what I can, to prevent SQL injection.

I'll link to it again, because it's a really important thing to read: The Curse and Blessings of Dynamic SQL

• undrline - Reinstate Monica: But that turns the whole thing into a string (lol, you even called it stringtoexecute), and uses EXEC sp_executesql ... there's no other way to piece them together and convert their names into the reference? you're declaring them as sysname, but that clearly isn't enough.
• Erik Reasonable Rates Darling: @undrline No, this is how you have to do things. Can you be more specific about sysname not being enough? The datatype length is the max length any one of those object names can be.
• undrline - Reinstate Monica: I just meant I had played around with trying to convert/cast and declare things as sysname before I asked the question. I tried to upvote, but it wouldn't let me because of rep; I thought there was an exception for your own question. QUOTENAME was something I had been meaning to look up but hadn't gotten around to yet +1 for that even though it doesn't look I get a rep exemption for it being my question.
• Erik Reasonable Rates Darling: @undrline you should be able to mark the answer as accepted, if you feel it's adequate.