how to change stored procedure schema in sql server
- User
how to change stored procedure schema in sql server
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 PROCEDUREstatement 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 SCHEMAfor 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.tohr.and execute.
✅ Summary
Action SQL Command Example Create under new schema CREATE PROCEDURE hr.usp_GetEmployees AS ...Drop old procedure DROP 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!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.