SQL Server: How can I select everything from a table with a prefix?

Question

I have the following code in a very long stored procedure, where P equals the Products table:

SELECT
P.*,
etc1,
etc2

Which would give me "ProductID" and so on.

I would like to select it with a prefix such as:

SELECT
P.* AS P_*,
etc1,
etc2

Which would give me "P_ProductID" and so on.

Is this possible to do?

Answer 1

Not unless you use dynamic SQL. It is very uncommon to require such a thing though, are you sure you need it?

Working example

create table Products (ProductID int, Price money, Description varchar(10));
insert Products select 1, 12.3, 'apples'
insert Products select 2, 2.4, 'bananas'
create table OrderDetails (OrderID int, ProductID int, Qty int)
insert into OrderDetails select 11,1, 2
insert into OrderDetails select 11,2, 4

declare @sql nvarchar(max)
select @sql = coalesce(@sql+',','') +
    'P.' + QuoteName(Column_name) + ' as ' + QuoteName('P_' + Column_name)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Products'
order by ORDINAL_POSITION
set @sql = '
select ' + @sql + ', O.OrderID, O.Qty
from Products P
inner join OrderDetails O on P.ProductID = O.ProductID
'
--print @sql   :: uncomment if you need to see it
exec (@sql)

Output:

P_ProductID P_Price               P_Description OrderID     Qty
----------- --------------------- ------------- ----------- -----------
1           12.30                 apples        11          2
2           2.40                  bananas       11          4

Answer 2

You can do it if you list all columns separately.

Column alias can't be done with wildcard.

If p.* is a very long list and the rest are only a few columns it may be better to rename 'the rest' and leave p.* as it is.