December 13, 2011 at 1:02 pm
Hi friends,
I'm trying to rename column names that are in lower case to upper case in some tables.. I have the below sql to give the command to rename the column names, but not sure how to bring up only the columns that are in lower case and not for all the columns..
SELECT 'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', ''' + UPPER(COLUMN_NAME) + ''',''COLUMN''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME like 'tablename'
Any suggestions please.. Thanks
December 13, 2011 at 1:17 pm
what about Proper Cased column names? not just all lower case;
this will find anything not upepr cased, buy using collation:
SELECT 'EXEC sp_rename '''
+ TABLE_SCHEMA + '.'
+ TABLE_NAME + '.'
+ COLUMN_NAME + ''', '''
+ UPPER(COLUMN_NAME) + ''',''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME <> UPPER(TABLE_NAME)
COLLATE Latin1_General_BIN
if it's ONLY lowercased, then i think the WHERE statement is this:
WHERE TABLE_NAME = LOWER(TABLE_NAME)
COLLATE Latin1_General_BIN
[/code]
Lowell
December 13, 2011 at 1:38 pm
Thanks a lot!!
One other doubt, do I need to drop & recreate all DF* and check constraints for those columns before I can rename them?
Thanks
December 13, 2011 at 1:39 pm
Thanks a lot!!
One other doubt, do I need to drop & recreate all DF* and check constraints for those columns before I can rename them?
Thanks
December 13, 2011 at 1:47 pm
only nvarchar/nchar/char/varchar columns with default or check constraints will fail(and this need to be dropped and recreated)...so if you had a check constraint for 'Y' or 'N', or a Default value on a n*var*char column, then the constraints will block the renaming.
if it was an integer/datetime/pretty much anything else column with a default value,
no problem.
Lowell
December 13, 2011 at 1:52 pm
Oh no.. Is there a way to find the non character columns that are in lower case? I should may be run the sql for non character fields first and then deal with the character fields by dropping and recreating constraints....
Thanks
December 13, 2011 at 2:07 pm
newbieuser (12/13/2011)
Oh no.. Is there a way to find the non character columns that are in lower case? I should may be run the sql for non character fields first and then deal with the character fields by dropping and recreating constraints....Thanks
the same view you were using has the datatypes of the columns as well...but not whether they have constraints.
so you could use something like this:
WHERE ...
-- columns by constraints
AND LOWER(DATA_TYPE) IN ('char','nchar','varchar','nvarchar')
--or the other way? ok columns
AND LOWER(DATA_TYPE) NOT IN ('char','nchar','varchar','nvarchar')
i would be lazy and run everything i could, then script out the constraints for the items that failed, instead of making one whopper SLq that checks defautl constraints, check constraints, indexes, etc.
Lowell
December 13, 2011 at 2:20 pm
When I try without limiting to character fields it works and I seem to get all the fields in lower case.
SELECT
'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', ''' + UPPER(COLUMN_NAME) + ''',''COLUMN''' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name'
and column_name = LOWER(column_name)
COLLATE Latin1_General_BIN
But, when I run the below sql, I get all the column names but I need only the character fields that are lower case.....
SELECT
'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', ''' + UPPER(COLUMN_NAME) + ''',''COLUMN''' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name'
and column_name = LOWER(column_name)
and LOWER(DATA_TYPE) IN ('char','nchar','varchar','nvarchar')
THanks so much for your help
December 13, 2011 at 3:04 pm
newbieuser (12/13/2011)
I'm trying to rename column names that are in lower case to upper case in some tables.
I have to ask, "WHY?!?!" By default, SQL is case insensitive. It doesn't care if the names are UPPERCASE, lowercase, camelCase, PascalCase, or RaNDomCAse. From a SQL viewpoint, you gain nothing by doing this.
From a legibility standpoint, you're actually losing by doing this. Numerous studies have shown that all uppercase is much harder to read than lowercase or mixed case. The reason for this is that people use the shape of words to help with reading comprehension. Lowercase letters have ascenders (e.g., "b" or "h") and descenders (e.g., "p" or "g") whereas uppercase letters tend not to, so their shapes are more similar and therefore harder to read. That's part of the reason that people complain when someone posts to a forum using all caps.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 15, 2011 at 8:11 am
We are facing some issues on the application with the lower case fields in the database.. So we need to change them all to upper case..
I would really appreciate if you someone could help me with this.. The below SQL returns rows for all columns for table1 but I need only the character fields that are in lower case..
SELECT
'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', ''' + UPPER(COLUMN_NAME) + ''',''COLUMN''' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1'
and column_name = LOWER(column_name)
and LOWER(DATA_TYPE) IN ('char','nchar','varchar','nvarchar')
Thanks so much
December 15, 2011 at 8:21 am
Use the COLLATE clause, like Lowell showed you.
John
December 15, 2011 at 8:26 am
When I try with collate I get incorrect syntax..
SELECT
'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', ''' + UPPER(COLUMN_NAME) + ''',''COLUMN''' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1'
and column_name = LOWER(column_name)
and LOWER(DATA_TYPE) IN ('char','nchar','varchar','nvarchar')
collate Latin1_General_BIN
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'collate'.
help please..thanks
December 15, 2011 at 8:30 am
It needs to come immediately after the predicate to which you want it to apply. Try swapping the order of the AND lines.
John
December 15, 2011 at 8:31 am
newbieuser (12/15/2011)
We are facing some issues on the application with the lower case fields in the database.. So we need to change them all to upper case..I would really appreciate if you someone could help me with this.. The below SQL returns rows for all columns for table1 but I need only the character fields that are in lower case..
SELECT
'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', ''' + UPPER(COLUMN_NAME) + ''',''COLUMN''' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1'
and column_name = LOWER(column_name)
and LOWER(DATA_TYPE) IN ('char','nchar','varchar','nvarchar')
Thanks so much
you left out the requored colaltion, but
the WHERE statement looks correct to me:
1. it's checking one specific table
2. finding only the columns that are lower cased
3. findinf only the columns that are char-types.
maybe the table is only composed of char types, but i do not see how it could be returning ALL columns.
Lowell
December 15, 2011 at 8:38 am
Lowell (12/15/2011)
you left out the requored colaltion, butthe WHERE statement looks correct to me:
1. it's checking one specific table
2. finding only the columns that are lower cased
3. findinf only the columns that are char-types.
maybe the table is only composed of char types, but i do not see how it could be returning ALL columns.
Lowell, if the database has a case-insensitive collation, then column_name = LOWER(column_name) is always going to equate to true. For example, "serverid" looks the same as "ServerID" - that's why you need the COLLATE clause. But you're right - if it's also finding non-character columns, I don't know why that is.
John
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply