Link to home
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

How do I change the name of a schema in SQL Server from capital letters to camel case?

All, please note the following image:  I'm trying to change this BANKING schema to Banking instead and do not know the proper SQL syntax to do so... can someone please provide it to where I can run the script in SQL Server?


I'm concerned if I have to delete it first and if so, do I need to delete this table also?

Thank you for any help.  It is much appreciated!

User generated image 



Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

This will be a royal pain.

You can't rename a schema.  You'll have to create a new schema and move all objects owned by the old schema to the new schema.  But, unless your instance is case sensitive, and they aren't be default, you won't be able to create a new schema name "Banking" while "BANKING" still exists, because those names are the same on a case insensitive instance (as they are by default).

So, you'll have to do this whole thing *twice*.  Create a new schema, say "Banking#'.  Move all objects from BANKING to BANKING#.  DROP the BANKING schema.  Create a new schema named 'Banking'.  Move all objects from Banking# to Banking.  DROP the Banking# schema.
Avatar of stephenlecomptejr

ASKER

Just fyi - I have no tables with data in this scenario!  I'm willing to delete the table - then schema if that helps!
Can you please provide the SQL Scripts that would make this work also?
If you're willing to drop all existing tables first, then this script should gen the code you need to do it:

SELECT N'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name +']'
FROM sys.tables
WHERE SCHEMA_NAME(schema_id) IN (SELECT schema_id FROM sys.schemas WHERE name = 'BANKING')
UNION ALL
SELECT 'GO'
UNION ALL
SELECT 'DROP SCHEMA [BANKING]'
UNION ALL
SELECT 'CREATE SCHEMA [Banking]'
Thanks for your responses, Scott.  I appreciate them.
I get an error though running the above:

User generated image
D'OH, my bad:

SELECT N'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name +']'
FROM sys.tables
WHERE schema_id IN (SELECT schema_id FROM sys.schemas WHERE name = 'BANKING')
Wow, no matter the steps above that run without errors, I still get the uppercase BANKING schema... Ugh!

User generated image
This is my creation script of the table:

USE [UniversalDB]
GO

/****** Object:  Table [Banking].[BadSSN]    Script Date: 12/6/2021 5:59:10 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Banking].[BadSSN](
   [ContactID] [nvarchar](20) NULL,
   [PayrollID] [nvarchar](20) NULL,
   [VerifiedSSN] [nchar](9) NULL,
   [Name] [nvarchar](60) NULL,
   [SSNStatus] [char](1) NULL,
   [UserName] [nvarchar](60) NULL,
   [Notes] [nvarchar](3000) NULL,
   [LastAccessedDate] [datetime] NULL,
   [FollowUpDate] [datetime] NULL,
   [DateInserted] [datetime] NOT NULL,
   [BADSSNID] [int] IDENTITY(1,1) NOT NULL,
   [Amount] [money] NULL,
PRIMARY KEY CLUSTERED 
(
   [BADSSNID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [Banking].[BadSSN] ADD  DEFAULT (getdate()) FOR [DateInserted]
GO


Open in new window

Let's add a GO between the DROP and CREATE schema (although shouldn't really need it):

SELECT N'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name +']'
FROM sys.tables
WHERE SCHEMA_NAME(schema_id) IN (SELECT schema_id FROM sys.schemas WHERE name = 'BANKING')
UNION ALL
SELECT 'GO'
UNION ALL
SELECT 'DROP SCHEMA [BANKING]'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT 'CREATE SCHEMA [Banking]'
User generated imageNo sir.  That didn't work still!
For the GUI, you'll need to refresh the db.  Right-click on "UniversalDB" and select "Refresh".
By the time I post it on here - of course, I refreshed it.  JIC, I closed out and opened up a new session after seeing that!  

Maybe I can use that to my advantage by having it uppercase since it's a dev environment and there won't be accidents of dropping tables.   But no matter what I try above, it's still holding on to the original UPPERCASE - that's why I took the time to post a pic.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial