How to update object name from Upper case to Lower

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to update object name from Upper case to Lower

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2015-04-06 : 14:40:53

Anyone know of a script that can be used on a database to change all object names that have uppercase letters in the name to lowercase?

example:
Current name: dbo.LZ_CLAIM_LINE
want to update to: dbo.lz_claim_line

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-06 : 14:59:18

I don't have a script, but here's the pseudocode:

Loop through INFORAMTION_SCHEMA.COLUMNS, .TABLES and any other views that you'll need to query for the objects with this "issue". Add a WHERE clause to these to check if they are upper case. You can switch the collation to make it easy. I'm sure there's an easier way though such as ASCII.

Run sp_rename to rename the object.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-06 : 14:59:53

DECLARE C CURSOR
READ_ONLY
FOR SELECT ScheMA_name(schema_id) SchemaName,Name from sys.tables
WHERE Name COLLATE Latin1_General_CS_AI = Upper(Name)
OR Name COLLATE Latin1_General_CS_AI != Upper(Name) and Name COLLATE Latin1_General_CS_AI != Lower(Name)

DECLARE @schemaName varchar(100),@name varchar(100), @rename varchar(100)
OPEN C

FETCH NEXT FROM C INTO @schemaName, @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @rename = LOWER(@name)
SET @name = @schemaName + '.'+ @name
exec sp_rename @name, @rename

END
FETCH NEXT FROM C INTO @schemaName, @name
END

CLOSE C
DEALLOCATE C
GO

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-06 : 15:39:26

It depends. Is the db containing the objects case sensitive? If it's not, you can't rename with just different case because SQL will (correctly) see it as the same name, and you can't "rename" something to the same name.

Kristen
Test

22859 Posts

Posted - 2015-04-07 : 08:10:23

quote:
Originally posted by ScottPletcher

It depends. Is the db containing the objects case sensitive? If it's not, you can't rename with just different case because SQL will (correctly) see it as the same name, and you can't "rename" something to the same name.

Hmmm ... interesting, as SSMS will let you change capitalisation on, say, a Column Name. never really thought about it before

so I scripted it and, as you might expect, it uses a temporary intermediate name and then renames back again.


EXECUTE sp_rename N'dbo.TEMP_Table_1.T1_ColB', N'Tmp_T1_Colb', 'COLUMN' 
GO
EXECUTE sp_rename N'dbo.TEMP_Table_1.Tmp_T1_Colb', N'T1_Colb', 'COLUMN' 
GO

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-07 : 08:46:27

This works fine. I changed it from just tables to all objects. If your sensitive is CS, then you would obviously through an error. you would have to come up with a plan how to handle those cases and either manually address them or add logic to this script.

DECLARE C CURSOR
READ_ONLY
FOR SELECT ScheMA_name(schema_id) SchemaName,Name from sys.objects
WHERE Is_MS_Shipped = 0
AND (Name COLLATE Latin1_General_CS_AI = Upper(Name)
OR Name COLLATE Latin1_General_CS_AI != Upper(Name) and Name COLLATE Latin1_General_CS_AI != Lower(Name)
)

DECLARE @schemaName varchar(100),@name varchar(100), @rename varchar(100)
OPEN C

FETCH NEXT FROM C INTO @schemaName, @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @rename = LOWER(@name)
SET @name = @schemaName + '.'+ @name
exec sp_rename @name, @rename

END
FETCH NEXT FROM C INTO @schemaName, @name
END

CLOSE C
DEALLOCATE C
GO

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2015-04-07 : 08:51:05

Oh wow guys, Thanks for so many different options I can try and play with. You're all awesome in my book. I'll let you know how it goes.

Thanks again

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2015-04-07 : 10:28:40

I ended up using the the following below. MichaelJSQL I used your script with a few added parms. :)

Thanks everyone


DECLARE C CURSOR
READ_ONLY
FOR SELECT ScheMA_name(schema_id) SchemaName,Name from sys.objects 
WHERE Is_MS_Shipped = 0
and type_desc in('USER_TABLE','SQL_STORED_PROCEDURE')
AND (Name COLLATE Latin1_General_CS_AI = Upper(Name)
OR Name COLLATE Latin1_General_CS_AI != Upper(Name) and Name COLLATE Latin1_General_CS_AI != Lower(Name)
--and Name = 'Hospbase_Summary'
)

DECLARE @schemaName varchar(100),@name varchar(100), @rename varchar(100)
OPEN C

FETCH NEXT FROM C INTO @schemaName, @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @rename = LOWER(@name)
SET @name = @schemaName + '.'+ @name
exec sp_rename @name, @rename


END
FETCH NEXT FROM C INTO @schemaName, @name
END

CLOSE C
DEALLOCATE C
GO

Kristen
Test

22859 Posts

Posted - 2015-04-08 : 03:41:01

Personally I would generate a script and execute that, rather than just use a Cursor. I think it is always worth a visual check before letting this sort of change run wild directly on the database!

This will generate a script for all the objects needing renaming (i.e. name is not all lowercase), sorted by Object Type, with a comment heading for each type:


SELECT	CASE WHEN T_RowNumber = 1 THEN '-- ' + type_desc + CHAR(13) + CHAR(10) ELSE '' END
		+ 'exec sp_rename ''' + SchemaName + '.'+ ObjectName + ''', ''' + LOWER(ObjectName) + ''''
		COLLATE database_default
FROM
(
SELECT
	[T_RowNumber] = ROW_NUMBER()
		OVER
		(
			PARTITION BY type_desc 
			ORDER BY type_desc, ScheMA_name(schema_id), Name
		),
	ScheMA_name(schema_id) AS SchemaName,
	Name AS ObjectName,
	type_desc
FROM	sys.objects 
WHERE	Is_MS_Shipped = 0
	AND Name <> Lower(Name) COLLATE Latin1_General_BIN2
) AS X
--
ORDER BY type_desc, SchemaName, ObjectName

Output:


-- DEFAULT_CONSTRAINT
exec sp_rename 'dbo.DF__dtpropert__versi__6BAEFA67', 'df__dtpropert__versi__6baefa67'
...
-- PRIMARY_KEY_CONSTRAINT
exec sp_rename 'dbo.pk_dtProperties', 'pk_dtproperties'
...

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-08 : 10:08:42

quote:
Originally posted by Kristen

quote:
Originally posted by ScottPletcher

It depends. Is the db containing the objects case sensitive? If it's not, you can't rename with just different case because SQL will (correctly) see it as the same name, and you can't "rename" something to the same name.

Hmmm ... interesting, as SSMS will let you change capitalisation on, say, a Column Name. never really thought about it before so I scripted it and, as you might expect, it uses a temporary intermediate name and then renames back again.

EXECUTE sp_rename N'dbo.TEMP_Table_1.T1_ColB', N'Tmp_T1_Colb', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.TEMP_Table_1.Tmp_T1_Colb', N'T1_Colb', 'COLUMN'
GO

You didn't change only the capitalization there, which is what I was referring to, such as "ABc" to "Abc". Of course you can change upper/lower case any way you want if it's a completely different name: "ABc" to "tmp_abc" or "tmp_ABC" or whatever.

Kristen
Test

22859 Posts

Posted - 2015-04-08 : 10:18:37

I did only change the capitalisation, that's the "workaround" script that SSMS generated to achieve it.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-08 : 12:41:30

My bad, and correction from me. You can change just the case of a name. I remember when that was not allowed, but clearly it is now. Sorry for my confusion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources