Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

User created one database, for example TestDb.

Within this database the user created 30 tables, with the table name in lower case letters (Eg:Employee).

Is it possible to change the tables names to upper case (eg:EMPLOYEE) in SQL Server 2008 R2?

Thanks.

share|improve this question
    
Is the server collation case sensitive ? If not then, what is the problem you are facing so that you need to rename all the tables to UPPER case ? –  Kin Mar 25 at 12:21

3 Answers 3

To rename table names in UPPER Case:

SELECT 'EXEC sp_rename '
+ '''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ''', '
+ '''' + UPPER(TABLE_NAME) + ''''
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

To rename Columns names in UPPER Case:

SELECT 'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + 
COLUMN_NAME + ''', ''' +
UPPER(COLUMN_NAME) + ''',''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'),
'isMSShipped') = 0

Result generated by above commands can run on query window to rename table name or column name in upper case respectively.

share|improve this answer
Select 'exec sp_rename '
+ '''' + QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME) + ''', '
+ '''' + Upper(TABLE_NAME) + ''''
From INFORMATION_SCHEMA.TABLES

Run This Script and you will get answer.Copy columns and run it. Now Table name will Changed.

share|improve this answer

This doesn't rely on the unreliable INFORMATION_SCHEMA views, and also handles poorly named objects (such as my'table) - but assumes you don't have otherwise poorly named objects (such as my[table]):

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
EXEC sp_rename N''' 
  + s.name 
  + '.' + REPLACE(t.name, '''', '''''') + ''',N''' 
  + UPPER(REPLACE(t.name, '''', '''''')) + ''',N''OBJECT'';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.