I doubt what I want to accomplish is possible in purely SQL, but I wanted to make sure I am not missing something.

I want to force the full table name to be part of the column name in the result set without having to individually alias each column. IE:

SELECT Table1.* FROM Table1;

This should output something like:

This is primarily used in a query that dynamically LEFT JOINS a 2nd table that has overlap in some of the column names. Meaning, depending on use case, it may be only querying the first table (no join) or both.

The parser for the result set also knows whether it needs to be concerned with just the 1st table or both tables. It would then be nice for it do something like:

table1Object.ID = reader["Table1.ID"];
table2Object.ID = reader["Table2.ID"];

I can just hard code every single column name along with aliases. IE, something like:

SELECT Table1.ID As Tab1ID, ...other columns... FROM Table1

That of course requires that if I add any new fields in the future I have to alter that query.

I could also query the table schemas from the DB and dynamically build the query in C# and give everything aliases, but I was hoping to avoid that as well.

It might have been MySql, but years ago I vaguely remember getting result sets where if there was a join and there was columns in each table that conflicted, it would return the column name as 'TableName.ColumnName'. Of course that doesn't really fix my aforementioned problem because it only did that for conflicts and not every column (and if you had no join you had no conflict).

CodePudding user response：

Given tables and sample data like this:

CREATE TABLE dbo.Parent(ID int, ShipDate date, floob int);
CREATE TABLE dbo.Child (ID int, ParentID int,  splunge int);

INSERT dbo.Parent(ID, ShipDate, floob) SELECT 1, GETDATE(), 5;
INSERT dbo.Child(ID, ParentID, splunge) SELECT 1, 1, 12;

You can create a procedure like this:

CREATE PROCEDURE dbo.BuildSomeSQL
  @parentTable   nvarchar(256),
  @parentColumn  nvarchar(128),
  @childTable    nvarchar(256),
  @childColumn   nvarchar(128)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'SELECT '   char(13)   char(10)   '  ';
  
  SELECT @sql  = STRING_AGG(CONCAT(N'[', 
    PARSENAME(@parentTable,1), N'.', name,  N'] = p.', name), 
    N','   char(13)   char(10)   '  ')
  FROM sys.columns 
  WHERE [object_id] = OBJECT_ID(@parentTable);

  SELECT @sql  = N','   char(13)   char(10)   '  ' 
                 STRING_AGG(CONCAT(N'[', 
    PARSENAME(@childTable,1), N'.', name, N'] = c.', name), 
    N','   char(13)   char(10)   '  ')
  FROM sys.columns 
  WHERE [object_id] = OBJECT_ID(@childTable);
    
  SET @sql  = char(13)   char(10)   N'FROM '   @parentTable   N' AS p
  INNER JOIN '   @childTable   N' AS c
  ON p.'   @parentColumn   N' = c.'   @childColumn   N';';
  
  PRINT @sql;  
  EXEC sys.sp_executesql @sql;
END

GO

Then you call it like this:

EXEC dbo.BuildSomeSQL
  @parentTable   = N'dbo.Parent',
  @parentColumn  = N'ID',
  @childTable    = N'dbo.Child', 
  @childColumn   = N'ParentID';

Print output:

SELECT 
  [Parent.ID] = p.ID,
  [Parent.ShipDate] = p.ShipDate,
  [Parent.floob] = p.floob,
  [Child.ID] = c.ID,
  [Child.ParentID] = c.ParentID,
  [Child.splunge] = c.splunge
FROM dbo.Parent AS p
  INNER JOIN dbo.Child AS c
  ON p.ID = c.ParentID;

Execution output:

• Example db<>fiddle