How to SELECT * but without "Column names must be unique in each view"

I need to encapsulate a set of tables JOINs that we freqently make use of on a vendor's database server. We reuse the same JOIN logic in many places in extracts etc. and it seemed a VIEW would allow the JOINs to be defined and maintained in one place.

CREATE VIEW MasterView
AS
SELECT *
FROM entity_1 e1
INNER JOIN entity_2 e2 ON e2.parent_id = entity_1.id
INNER JOIN entity_3 e3 ON e3.parent_id = entity开发者_如何学运维_2.id
/* other joins including business logic */
etc.

The trouble is that the vendor makes regular changes to the DB (column additions, name changes) and I want that to be reflected in the "MasterView" automatically.

SELECT * would allow this, but the underlying tables all have ID columns so I get the "Column names in each view must be unique" error.

I specifically want to avoid listing the column names from the tables because a) it requires frequent maintenance b) there are several hundred columns per table.

Is there any way to achieve the dynamism of SELECT * but effectively exclude certain columns (i.e. the ID ones)

Thanks

---

I specifically want to avoid listing the column names from the tables because a) it requires frequent maintenance b) there are several hundred columns per table.

In this case, you can't avoid it. You must specify column names and for those columns with duplicate names use an alias. Code generation can help with these many columns.

SELECT * is bad practice regardless - if someone adds a 2GB binary column to one of these tables and populates it, do you really want it to be returned?

---

One simple method to generate the columns you want is

select column_name+',' from information_schema.columns
where table_name='tt'
and column_name not in('ID')

---

As well as Oded's answer (100% agree with)...

If someone changes the underlying tables, you need view maintenance anyway (with sp_refreshview). The column changes will not appear in the view automatically. See "select * from table" vs "select colA, colB, etc. from table" interesting behaviour in SQL Server 2005

So your "reflected in the "MasterView" automatically requirement can't be satisfied anyway

If you want to ensure the view is up to date, use WITH SCHEMABINDING which will prevent changes to the underlying tables (until removed or dropped). Then make column changes, then re-apply the view

---

I had the same issue, see example below:

ALTER VIEW Summary AS SELECT * FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.Id = t2.Id

and I encountered that error you mentioned, the easiest solution is using the alias before * like this:

SELECT t1.* FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.Id = t2.Id

You shouldn't see that error anymore.

---

I had gone with this in the end, building off of Madhivanan's suggestion. It's similar to what t-clausen.dk later suggested (thanks for your efforts) though I find the xml path style more elegant than cursors / rank partitions.

The following recreates the MasterView definition when run. All columns in the underlying tables are prepended with the table name, so I can include two similarly named columns in the view by default. This alone solves my original problem, but I also included the "WHERE column_name NOT IN" clause to specifically exclude certain columns that will never be used in the MasterView.

create procedure Utility_RefreshMasterView 
as
begin

    declare @entity_columns varchar(max)
    declare @drop_view_sql varchar(max)
    declare @alter_view_definition_sql varchar(max)

    /* create comma separated string of columns from underlying tables aliased to avoid name collisions */
    select @entity_columns = stuff((
        select ','+table_name+'.['+column_name+'] AS ['+table_name+'_'+column_name+']' 
        from information_schema.columns
        where table_name IN ('entity_1', 'entity_2')
        and column_name not in ('column to exclude 1', 'column to exclude 2')
        for xml path('')), 1, 1, '')


    set @drop_view_sql = 'if exists (select * from sys.views where object_id = object_id(N''[dbo].[MasterView]'')) drop view MasterView'

    set @alter_view_definition_sql = 
    'create view MasterView as select ' + @entity_columns + '
    from entity_1
    inner join entity_2 on entity_2 .id = entity_1.id
    /* other joins follow */'

    exec (@drop_view_sql)
    exec (@alter_view_definition_sql)

end

---

If you have a Select * and then you are using the JOIN, the result might include columns with the same name and that cannot be possible in a view.If you run the query by itself, works fine but not when creating the View.

For example:

**Table A**
ID, CatalogName, CatalogDescription
**Table B**
ID, CatalogName, CatalogDescription
**After the JOIN query**
ID, CatalogName, CatalogDescription, ID, CatalogName, CatalogDescription
That's not possible in a View.

Specify a unique name for each column in the view. Using just * is not a very good practice.