View - select all fields of two tables when both tables have an identical column name

Question

I want to create a view that selects all columns of 2 tables. I have to select all columns (I can't write the columns names, because columns can be added).

My problem is that both tables have columns with the same name. And I can't change the column names.

CREATE OR ALTER VIEW [dbo].[vwSomething]
AS
    SELECT         
        dbo.A.*,
        dbo.B.*
    FROM            
        dbo.A
    INNER JOIN      
        dbo.B ON dbo.A.AID = dbo.B.AID 
GO

Who is adding columns to the base tables and can't themselves modify the view? Maybe setup an agent job that periodically refreshes the view? — Charlieface, Mar 01 '21 at 09:20
If you create a view with `SELECT *`, columns added to the underlying tables later will **not appear in the view** automatically. See https://stackoverflow.com/q/22716598/95 for more information. — Marek Grzenkowicz, Mar 01 '21 at 09:23
I know, I have a proc which runs `EXECUTE sp_refreshview...` — Sarah Jung, Mar 01 '21 at 09:29
What is your question? How to require that all column names are unique across all tables in a database? A [DDL trigger](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15) will let you _discourage_ the use of conflicting column names. — HABO, Mar 01 '21 at 17:01
@SMor No lazy excuses! - It's a system for multiple customers, and each customer can add which columns he wants to the tables. — Sarah Jung, Mar 02 '21 at 07:09

jarlh · Answer 1 · 2021-03-01T10:49:07.887

You have to name the view columns:

CREATE OR ALTER VIEW [dbo].[vwSomething] (c1, c2, c3, ...)
AS
SELECT         dbo.A.*,
               dbo.B.*
FROM            dbo.A
INNER JOIN      dbo.B ON dbo.A.AID = dbo.B.AID 
GO

Or

CREATE OR ALTER VIEW [dbo].[vwSomething]
AS
SELECT         dbo.A.col1 c1, dbo.A.col2 c2, ...
               dbo.B.cola ca, dbo.B.colb cb, ... 
FROM            dbo.A
INNER JOIN      dbo.B ON dbo.A.AID = dbo.B.AID 
GO

A view is expected to always return the same columns, even if someone later adds a column to one of its base tables.

score 2 · Answer 2 · edited May 29 '21 at 12:13

You need to list the columns. If the duplicates column names contain the same data, you can use this shortcut to get the list:

select string_agg(column_name, ', ') within group (order by table_name, ordinal_position)
from (select c.*,
             row_numbrer() over (partition by column_name order by table_name) as seqnum
      from information_schema.columns c
      where table_name in ('A', 'B') and table_schema = 'dbo'
     ) c
where seqnum = 1;

You can then copy the list into your query.

score 0 · Answer 3 · answered Mar 01 '21 at 09:33

0

Ok! then do not use Select * and Enumerate your columns and assign an alias to them

SELECT a.Column1 as Alias1,b.Column1 as Alias2
FROM            dbo.A a
INNER JOIN      dbo.B b ON dbo.A.AID = dbo.B.AID

answered Mar 01 '21 at 09:33

Martin

31
3

score 0 · Answer 4 · answered Mar 01 '21 at 10:55

Column Names in a view should always be unique. you can assign an alias name to your columns to differentiate it from Table A and Table B. If you are concerned about the Addition of new Columns in the future, then You can go for Dynamic SQL as below.

DROP TABLE IF EXISTS #TempColumDetails
CREATE TABLE #TempColumDetails ([Id]INT IDENTITY(1,1), [ColumnName] VARCHAR(500),[ColumnAlias] VARCHAR(500),[TableAlias] VARCHAR(50))

INSERT INTO #TempColumDetails
SELECT
    COLUMN_NAME AS [ColumnName], 
    COLUMN_NAME+'_'+'T1' [ColumnAlias],
    'T1' AS [TableAlias]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'A'
ORDER BY ORDINAL_POSITION

INSERT INTO #TempColumDetails
SELECT
    COLUMN_NAME AS [ColumnName], 
    COLUMN_NAME+'_'+'T2' [ColumnAlias],
    'T2' AS [TableAlias]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'B'
ORDER BY ORDINAL_POSITION


DECLARE @MaxId INT,@Count INT
SELECT @MaxId = MAX(Id) FROM #TempColumDetails

DECLARE @TSQL NVARCHAR(MAX) 
SET @TSQL = 'CREATE OR ALTER VIEW [dbo].[vwSomething]
AS
SELECT ';
SET @Count = 1;
WHILE(@Count<=@MaxId)
BEGIN
    DECLARE @ColumnName VARCHAR(500),@ColumnAlias VARCHAR(500),@TableAlias VARCHAR(50)
    SELECT @ColumnName = ColumnName, @ColumnAlias = ColumnAlias, @TableAlias = TableAlias FROM #TempColumDetails WHERE Id = @Count  
    IF(@Count = @MaxId) 
    BEGIN 
        SELECT @TSQL = @TSQL+@TableAlias+'.'+@ColumnName+' AS '+@ColumnAlias+' '
    END
    ELSE
    BEGIN
        SELECT @TSQL = @TSQL+@TableAlias+'.'+@ColumnName+' AS '+@ColumnAlias+', '       
    END
    SET @Count = @Count + 1;
END

SELECT @TSQL = @TSQL + 'FROM  dbo.A T1
JOIN      dbo.B T2 ON T1.AID = T2.AID ';

EXEC(@TSQL)

If you need a cursor, then just use a cursor. Looping over an incrementing ID against a MAX value is just a more error-prone and slower version of a cursor. And stop applying that pattern without a good reason. — SMor, Mar 01 '21 at 12:31
Thanks for your comments, I thought Cursors affect performance than a while loop. and I used Temp Table here because ORDER BY Clause is not allowed in CTEs or Derived Tables unless Top some record(e.g. 50) is mentioned. — Annamalai D, Mar 01 '21 at 12:59

View - select all fields of two tables when both tables have an identical column name

4 Answers4