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

Question

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

Answer 1

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?