4

How can rename Sql Server schema name from Upper case to lower case.

In my SQL Server naming convention, we must use lower case name for Schema instead of upper case.

thanks in advance

mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
  • There's no supported way to do it, so far as I'm aware. If you're in a case-insensitive database, I think you'd have to move all contained objects to another schema, drop it, create it with the desired casing, and then move all of the objects back. In a case-sensitive DB, you'd just create a new schema with the correct name and move the objects directly across. – Damien_The_Unbeliever May 06 '14 at 06:50

1 Answers1

5

You could probably move all objects from 'MySchema' to a temporary schema say 'tmp_schema' using

ALTER SCHEMA NewSchema TRANSFER OldSchema.Object;

Then drop 'MySchema', recreate as 'myschema' and then move all the objects back with the same method. This post has a script to do it that you might find useful.

Rename SQL Server Schema

Community
  • 1
  • 1
Hoots
  • 1,876
  • 14
  • 23
  • Good answer, thank you. But why not just move them from the old schema name to the new schema name, one time? – Reversed Engineer May 03 '19 at 08:26
  • SQL server will see schema1 the same as SCHEMA1 so won't let you create it. That's why you need to create a temporary scheme to put things in so you remove the lowercase version before creating an upper case version to then move objects back to from the temporary scheme. – Hoots May 07 '19 at 13:28
  • Ah thank you - I messed the part about converting from upper case to lower case, and thought that NewSchema and OldSchema were different – Reversed Engineer May 08 '19 at 11:53