Skip to content
TheChatArchiveThe Chat Archive
Hunyuanhunyuan-turbos-20250416· TencentHugging Face LMArena Human Preference 140K

how to change stored procedure schema in sql server

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-3-7-sonnet-20250219-thinking-32k’s answer.
  1. User

    how to change stored procedure schema in sql server

  2. Hunyuan Assistant

    To change the schema of a stored procedure in SQL Server, you cannot directly "alter" the schema like you do with tables (e.g., ALTER SCHEMA ... TRANSFER). Instead, you need to create a new stored procedure under the desired schema and then optionally drop the old one.


    ✅ Steps to Change Stored Procedure Schema

    1. Create the Stored Procedure Under the New Schema

    Use the CREATE PROCEDURE statement with the new schema prefix.

    -- Suppose you want to move procedure 'usp_GetEmployees' from dbo to hr schema
    
    CREATE PROCEDURE hr.usp_GetEmployees
    AS
    BEGIN
        -- Copy the original body here
        SELECT * FROM Employees;
    END;
    

    ⚠️ You must manually copy the entire logic of the existing procedure into the new one.


    2. Verify the New Procedure

    EXEC hr.usp_GetEmployees;
    

    3. (Optional) Drop the Old Procedure

    Once you've confirmed the new procedure works correctly:

    DROP PROCEDURE dbo.usp_GetEmployees;
    

    🔄 Alternative: Use sp_changeobjectowner (Deprecated)

    ⚠️ Deprecated in newer versions. Was used like this:

    EXEC sp_changeobjectowner 'usp_GetEmployees', 'hr';
    

    Instead, Microsoft recommends using the ALTER SCHEMA for objects like tables, but for stored procedures, you must recreate them under the new schema.


    🛠 Script to Generate CREATE PROCEDURE from Existing One

    If you want to script out the existing procedure to reuse it under a new schema:

    EXEC sp_helptext 'dbo.usp_GetEmployees';
    

    Or in SSMS:

    • Right-click the procedure → Script Stored Procedure as → CREATE To → New Query Editor Window

    Then modify the schema prefix from dbo. to hr. and execute.


    ✅ Summary

    ActionSQL Command Example
    Create under new schemaCREATE PROCEDURE hr.usp_GetEmployees AS ...
    Drop old procedureDROP PROCEDURE dbo.usp_GetEmployees;
    Transfer ownership (deprecated)EXEC sp_changeobjectowner (Not recommended)

    Let me know if you'd like a script that automates this process for multiple procedures!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.