CODEWITHSUNDEEP
c#sql-server
Mathias Lykkegaard Lorenzen
Mathias Lykkegaard Lorenzen

Reputation: 15886

Prefix every column name with a specific string?

I'm trying to manually map some rows to instances of their appropriate classes. I know that I need to use every column of every table, and map all of those columns from one table into a given class.

However, I was wondering if there would be an easier way to do it. Right now, I have a class called School and a class called User. Each of these classes has a Name property, and other properties (but the ´Name` one is the important one, since it is a mutual name for both classes).

Right now, I am doing the following to map them down.

SELECT u.SomeOtherColumn, u.Name AS userName, s.SomeOtherColumn, s.Name AS schoolName FROM User AS u INNER JOIN School AS s ON something

I would love to do the following, but I can't, since Name is a mutual name between the classes.

SELECT u.*, s.* FROM User AS u INNER JOIN School AS s ON something

This however generates an error since they both have the column Name. Can I prefix them somehow? Like this for instance?

u.user_*, s.school_*

So that every column of each of those tables have a prefix? For instance user_Name and school_Name?

Upvotes: 3

Views: 478

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56735

Years ago I wrote a bunch of functions and procedures to help me with developing automatic code-generation routines for SQL Servers and applications using dynamic SQL. Here is the one that I think would be most helpful to your situation:

Create FUNCTION [dbo].[ColumnString2]
(
    @TableName  As SYSNAME,       --table or view whose column names you want
    @Template   As NVarchar(MAX), --replaces '{c}' with the name for every column,
    @Between    As NVarchar(MAX)  --puts this string between every column string
)   
    RETURNS NVarchar(MAX) AS
BEGIN
    DECLARE @str As NVarchar(MAX);

    SELECT  TOP 999
        @str = COALESCE(
                @str + @Between + REPLACE(@Template,N'{c}',COLUMN_NAME), 
                REPLACE(@Template,N'{c}',COLUMN_NAME)
                        )
    FROM    INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA= COALESCE(PARSENAME(@TableName, 2), N'dbo')
      And   TABLE_NAME  = PARSENAME(@TableName, 1)
    ORDER BY ORDINAL_POSITION

    RETURN @str;
END

This allows you to format all of the column names of a table or view any way that you want. Simply pass it a table name, and a Template string with '{c}' everywhere that you want the column name inserted for each column. It will do this for every column in @TableName, and add the @Between string in between them.

Here is an example of how to vertically format all of the column names for a table, renaming them with a prefix in a way that is suitable for inclusion into a SELECT query:

SELECT dbo.[ColumnString2](N'yourTable', N'
    {c} As prefix_{c}', N',')

This function was intended for use with dynamic SQL, but you can use it too by executing it in Management Studio with your output set to Text (instead of Grid). Then cut and paste the output into your desired query, view or code text. (Be sure to change your SSMS Query options for Text Results to raise the "maximum number of characters displayed" from 256 to the max (8000). If that still gets cut off for you, then you can change this procedure to a function that outputs each column as a separate row, instead of as one single large string.)

Upvotes: 1

Related Questions