CODEWITHSUNDEEP
sql
Luis
Luis

Reputation: 3277

SELECT AS to all of the table

I have a table with many columns and I want to add to each column the same prefix when doing select, I mean instead of long code:

SELECT table.id    AS prefix_id
      ,table.value AS prefix_value
      ,table.content AS prefix_content
      , ...

There is a simple way to do it?

Upvotes: 2

Views: 213

Answers (2)

codingbiz
codingbiz

Reputation: 26386

I don't know if this would help you, write a script that does that. You can customize it to pass the table name, prefix etc

declare 
@tableName varchar(50) = 'school',
@sql varchar(max) = 'SELECT '

Select @sql = @sql+'c.'+c.name+',' from sys.columns c
join sys.tables t on c.object_id = t.object_id
where t.name = @tableName

--MORE PRECISELY
Select @sql = @sql+'c.'+c.name+' as prefix_'+c.name+', ' from sys.columns c
join sys.tables t on c.object_id = t.object_id
where t.name = @tableName

Set @sql = @sql + ' FROM '+@tableName+' c'

print @sql

Output, where c. is the prefix. You can modify the code to taste

SELECT c.SchoolID as prefix_SchoolID, 

    c.SchoolName as prefix_SchoolName, 
    c.Address as prefix_Address, 
    c.City as prefix_City, 
    c.State as prefix_State, 
    c.Country as prefix_Country,
    c.PhoneNumber as prefix_PhoneNumber, 
    c.Password as prefix_Password,
    c.CreatedBy as prefix_CreatedBy, 
    c.CreatedOn as prefix_CreatedOn, 
    c.ModifiedBy as prefix_ModifiedBy, 
    c.ModifiedOn as prefix_ModifiedOn, 

FROM school c

Notice the comma after modifiedOn. You may need to remove that from the @sql variable or manaually

Upvotes: 0

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34591

It is impossible to add a prefix to all columns, unless you do it manually using AS (like in your example).

Upvotes: 2

Related Questions