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

Ten Centuries

Points: 1026
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: 246959 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

carlton 84646 Ten Centuries Points: 1026 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 Ten Centuries Points: 1026 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: 246959 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!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101092 More actions · Answer 5

Issue this command first:

CREATE SCHEMA config;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

carlton 84646 Ten Centuries Points: 1026 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 Ten Centuries Points: 1026 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: 99129 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: 246959 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 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin