1

Just curious if there is a way to create a new schema that is based on an existing database.

Assuming the existing database has dbo schema only.

I will be taking an entire database and moving it to an existing database as a new schema within that database.

0
0

For copy all tables into new schema in current database :

SELECT 'ALTER SCHEMA New Schema TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];' as 'queries'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'Old Schema' -- 'dbo' if that is schema name of tables
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 inside result view table in bottom of page.

Something like this :

ALTER SCHEMA Inf TRANSFER [dbo].[t_Link];
ALTER SCHEMA Inf TRANSFER [dbo].[t_News];

Now click on column header ( queries ) and right click, copy all rows, then paste in query windows and Execute.

Now all of objects are in your new schema.

0

As we create schema within database not database within schema, we can’t move database from one to another schema.

Instead we can move all objects of a database from one to another schema on same or different database with and data.

There are many ways you can do this but here I'm sharing to simple ways to do this, if you need to move all objects with data:

First method is.

  1. You can use DTS or SSIS to move all the tables
  2. And generate script of all programmatically object (views, function, procedures & types)
  3. Update schema name according to target database and execute the script Note: Moving all the table using DTS or SSIS wouldn’t include indexes of the tables. So you have to generate script of the indexes and create them manually.

Second:

  1. Generate script of all objects including tables.
  2. Change schema name in the script according to target database and execute the script
  3. And copy data of all the tables using DTS or SSIS

If you need to move all objects without data then follow 1st and 2nd step of “Second” method.

You can use powershell scripting make all the steps easier.

Thanks!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.