Create Group of Views with Columns in Upper Case

Question

Post reply

Create Group of Views with Columns in Upper Case

Garfi61416

Old Hand

Points: 360
More actions
July 7, 2009 at 7:16 pm

#211893

I have a client who has database tables with columns in lower case. I need them in upper case, and I can't ask them to change all the data of their tables. Therefore, I would like to know how to create a set a view of each table with the column names in upper case, if possible, or an alternative to the solution. Also, since I don't know the names of the tables, I would like this to be done dynamically. I know it's a tall order, but I am hoping someone knows what to do.
I read the post http://www.sqlservercentral.com/Forums/Topic737526-146-1.aspx#bm738038, and I think it is something along the lines of that.
Please help.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

[KH] SSCommitted Points: 1908 More actions · Answer 1

you can get the table name / column name from below query

SELECTt.TABLE_NAME, c.COLUMN_NAME

FROMINFORMATION_SCHEMA.TABLES t

INNER JOIN INFORMATION_SCHEMA.COLUMNS cONt.TABLE_NAME= c.TABLE_NAME

WHEREt.TABLE_TYPE= 'BASE TABLE'

ORDER BY c.ORDINAL_POSITION

1. loop through INFORMATION_SCHEMA.TABLES to get all the table names

2. Get all the column names from INFORMATION_SCHEMA.COLUMNS and UPPER() the COLUMN_NAME

3. use Dynamic SQL form the Create View DDL to create the view.

Garfi61416 Old Hand Points: 360 More actions · Answer 2

Thanks for the reply, and I was already considering your approach, the problem I'm having is trying to figure out how to create the dynamic sql.

I can get a list of all column names by using something like

SELECT @sql = 'SELECT ... FROM INFORMATION_SCHEMA.COLUMNS...'

But once I do that, how do I execute that query to produce a dynamic set of views?

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Garfi61416 (7/7/2009)
I have a client who has database tables with columns in lower case. I need them in upper case, and I can't ask them to change all the data of their tables. Therefore, I would like to know how to create a set a view of each table with the column names in upper case, if possible, or an alternative to the solution. Also, since I don't know the names of the tables, I would like this to be done dynamically. I know it's a tall order, but I am hoping someone knows what to do.
I read the post http://www.sqlservercentral.com/Forums/Topic737526-146-1.aspx#bm738038, and I think it is something along the lines of that.
Please help.

I'm curious... why do you need for the column names themselves to be in upper case?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)