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

  • 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?

     

     

  • 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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Hi Phil,

    Thanks for getting in touch,

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

    ssms

  • 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.

     

  • 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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Issue this command first:

    CREATE SCHEMA config;

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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?

  • Don't worry.

     

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

  • 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

  • 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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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