How to change schema of all tables, views and stored procedures in MSSQL
Yes, it is possible.
To change the schema of a database object you need to run the following SQL script:
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName
Where ObjectName can be the name of a table, a view or a stored procedure. The problem seems to be getting the list of all database objects with a given shcema name. Thankfully, there is a system table named sys.Objects that stores all database objects. The following query will generate all needed SQL scripts to complete this task:
SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))
Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.
Running the above script will generate the SQL commands needed to transfer objects from one schema to another. Something like this:
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CONTENT_KBArticle;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_Analytics_Statistics_Delete;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_CMS_QueryProvider_Select;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.COM_ShoppingCartSKU;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CMS_WebPart;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Polls_PollAnswer;
Now you can run all these generated queries to complete the transfer operation.
Change Schema Name Of Table In SQL
Create Schema :
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'exe'))
BEGIN
EXEC ('CREATE SCHEMA [exe] AUTHORIZATION [dbo]')
END
ALTER Schema :
ALTER SCHEMA exe
TRANSFER dbo.Employees
How do I move a table into a schema in T-SQL
ALTER SCHEMA TargetSchema
TRANSFER SourceSchema.TableName;
If you want to move all tables into a new schema, you can use the undocumented (and to be deprecated at some point, but unlikely!) sp_MSforeachtable
stored procedure:
exec sp_MSforeachtable "ALTER SCHEMA TargetSchema TRANSFER ?"
Ref.: ALTER SCHEMA
SQL 2008: How do I change db schema to dbo
How do I change db schema to dbo
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;
See ALTER SCHEMA.
Generalized Syntax:
ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;
How to copy all tables, stored procedures to another schema in Synapse data warehouse?
There is no built-in method to do this, but depending on your skills there are a number of different options:
use SQL Server Management Studio (SSMS) built-in scripting options. Newer versions of SSMS (v18.x and onwards) are capable of producing DDL for Azure Synapse Analytics. Simply point to your object (table, stored proc, view etc) in Object Explorer, right-click it, and view the scripting options. eg for tables you will see 'Script Table as'
SQL Server Data Tools (SSDT) - SSDT now has support for Azure Synapse Analytics, dedicated SQL pools. So you can import your schema, do a find and replace in the .sql scripts in the project, and generate the script. You can also use the Data Compare and Schema Compare features.
command-line option
mssql-cli
. This offers powerful command-line scripting options but you'll need to download and install it: https://learn.microsoft.com/en-us/sql/tools/mssql-cli?view=sql-server-ver15Use
CTAS
to transfer schema and data. Create a simple CTAS template and run it for each of your tables:CREATE TABLE <new schema>.yourTable
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM <old schema>.yourTable;
OPTION ( LABEL = 'CTAS: copy yourTable to new schema' );
So a few options for you.
Stored procedures/DB schema in source control
We choose to script everything, and that includes all stored procedures and schema changes. No wysiwyg tools, and no fancy 'sync' programs are necessary.
Schema changes are easy, all you need to do is create and maintain a single file for that version, including all schema and data changes. This becomes your conversion script from version x to x+1. You can then run it against a production backup and integrate that into your 'daily build' to verify that it works without errors. Note it's important not to change or delete already written schema / data loading sql as you can end up breaking any sql written later.
-- change #1234
ALTER TABLE asdf ADD COLUMN MyNewID INT
GO
-- change #5678
ALTER TABLE asdf DROP COLUMN SomeOtherID
GO
For stored procedures, we elect for a single file per sproc, and it uses the drop/create form. All stored procedures are recreated at deployment. The downside is that if a change was done outside source control, the change is lost. At the same time, that's true for any code, but your DBA'a need to be aware of this. This really stops people outside the team mucking with your stored procedures, as their changes are lost in an upgrade.
Using Sql Server, the syntax looks like this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_MyProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [usp_MyProc]
GO
CREATE PROCEDURE [usp_MyProc]
(
@UserID INT
)
AS
SET NOCOUNT ON
-- stored procedure logic.
SET NOCOUNT OFF
GO
The only thing left to do is write a utility program that collates all the individual files and creates a new file with the entire set of updates (as a single script). Do this by first adding the schema changes then recursing the directory structure and including all the stored procedure files.
As an upside to scripting everything, you'll become much better at reading and writing SQL. You can also make this entire process more elaborate, but this is the basic format of how to source-control all sql without any special software.
addendum: Rick is correct that you will lose permissions on stored procedures with DROP/CREATE, so you may need to write another script will re-enable specific permissions. This permission script would be the last to run. Our experience found more issues with ALTER verses DROP/CREATE semantics. YMMV
In sql server 2005, how do I change the schema of a table without losing any data?
In SQL Server Management Studio:
- Right click the table and select modify (it's called "Design" now)
- On the properties panel choose the correct owning schema.
Related Topics
Sql-Server: Error - Exclusive Access Could Not Be Obtained Because the Database Is in Use
SQL Left Join First Match Only
Cross Apply VS Outer Apply Speed Difference
Sequentially Number Rows by Keyed Group in SQL
Postgresql Generate_Series of Months
Postgres Date Overlapping Constraint
How to Print a Triangle of Stars Using SQL
Check If Stored Procedure Is Running
Identity_Insert Is Set to Off - How to Turn It On
How to Change Schema of All Tables, Views and Stored Procedures in Mssql
How SQL Query Result Insert in Temp Table
How to Join on a Stored Procedure
Which Lock Hints Should I Use (T-Sql)
Select Multiple (Non-Aggregate Function) Columns with Group By