Avatar of stephenlecomptejr
stephenlecomptejr
Flag 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!

 



SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Scott Pletcher

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.
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?
Scott Pletcher

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]'
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
stephenlecomptejr

ASKER
Thanks for your responses, Scott.  I appreciate them.
I get an error though running the above:


Scott Pletcher

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')
stephenlecomptejr

ASKER
Wow, no matter the steps above that run without errors, I still get the uppercase BANKING schema... Ugh!


⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
stephenlecomptejr

ASKER
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

Scott Pletcher

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]'
stephenlecomptejr

ASKER
No sir.  That didn't work still!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Scott Pletcher

For the GUI, you'll need to refresh the db.  Right-click on "UniversalDB" and select "Refresh".
stephenlecomptejr

ASKER
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
Scott Pletcher

THIS SOLUTION 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
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.