How to change schema of all tables, views and stored procedures in MSSQL

Question

How to change schema of all tables, views and stored procedures in MSSQL

carlton 84646

SSC Eights!

Points: 902
More actions
April 14, 2021 at 4:54 pm

#3871137
Hello Community,
I trying to use the following sql code to change the Schema for all my tables from dbo to Config
```
SELECT 'ALTER SCHEMA Config TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'dbo'
AND (DbObjects.Type IN ('U', 'P', 'V'))
```
But the schema remains the same.
Can someone let me know where I'm going wrong?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

Your code is merely returning results. If you want to run the code, you need to paste those results into SSMS and hit F5 ... very carefully and having backed up your DB, I'd suggest.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

carlton 84646 SSC Eights! Points: 902 More actions · Answer 2

Hi Phil,

Thanks for getting in touch,

I pasted the code into SSMS but the schema remains the same

ssms

carlton 84646 SSC Eights! Points: 902 More actions · Answer 3

Sorry, I'm being an idiot.

I'm getting the following error

Msg 15151, Level 16, State 1, Line 1

Cannot alter the schema 'Config', because it does not exist or you do not have permission.

Msg 15151, Level 16, State 1, Line 2

Cannot alter the schema 'Config', because it does not exist or you do not have permission.

Phil Parkin SSC Guru Points: 245813 More actions · Answer 4

carlton 84646 wrote:

Sorry, I'm being an idiot.
I'm getting the following error
Msg 15151, Level 16, State 1, Line 1 Cannot alter the schema 'Config', because it does not exist or you do not have permission. Msg 15151, Level 16, State 1, Line 2 Cannot alter the schema 'Config', because it does not exist or you do not have permission.

OK, that's a pretty clear error message!

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ScottPletcher SSC Guru Points: 100262 More actions · Answer 5

Issue this command first:

CREATE SCHEMA config;

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

carlton 84646 SSC Eights! Points: 902 More actions · Answer 6

Hi Phil,

I just tried changing the schema with the username that was used to actually create the database but I'm still getting the error

Cannot alter the schema 'Config', because it does not exist or you do not have permission.

Can you let me know how to set the permissions to alter the schema please?

carlton 84646 SSC Eights! Points: 902 More actions · Answer 7

Don't worry.

I realised the problem was that I didn't have a schema called called Config in the first place - doh!

Thom A SSC Guru Points: 99009 More actions · Answer 8

Side note, syntax like '[' + {Object Name} + ']' isn't safe from injection. If you had an object with the ] character in it's name (yes, there are people who are silly enough to do that) you would get a syntax error, and of course if it was a user parameter then someone malicious would easily be able to escape the string. You should be using QUOTENAME to properly quote the dynamic object's name instead.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 245813 More actions · Answer 9

carlton 84646 wrote:

Don't worry.
I realised the problem was that I didn't have a schema called called Config in the first place - doh!

I'm going to assume that wine or beer were involved 🙂

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.