Is it possible to avoid specifying a column list in a SQL Server CTE?

Question

Is it possible to avoid specifying a column list in a SQL Server CTE?

I'd like to create a CTE from a table that has many columns so that the structure is identical. There probably is a way to accomplish this without relisting every column name.

I've tried (unsuccessfully):

with pay_cte as
(select * from payments)
select * from pay_cte

I'm encouraged in my quest by this statement in the msdn documentation: The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

https://msdn.microsoft.com/en-us/library/ms175972.aspx

Answer 1

Yes, assuming you mean that you don't have to name every column in the with cte(Col1, Col2) as section.

You can easily try this yourself with a very simple test query along the lines of:

with cte as
(
    select *
    from sys.tables
)
select *
from cte