We help IT Professionals succeed at work.

T-sql to change schema of all tables

I have a table in which there are fields like
Schema      
Name of table  
Create_date      
Modify_date      
Action       
Owner

I would like to change schema of all the tables into one particular schema..i need a t-sql to change as there are many tables
Comment
Watch Question

AneeshDatabase Consultant
Top Expert 2009

Commented:
if you just need to change the schema in that particular table use this

UPDATE tableName set [Schema] = 'newSchemaName'

Author

Commented:
HI, first I need to crate a copy of all tables and then delete. Instead of updating can you tell me to copy tables to different schema in a loop.
AneeshDatabase Consultant
Top Expert 2009

Commented:
If all you want to do is to change the Schema or ownership of the objects, deleting and recreating all objects sounds to me an unnecessary job. All you need to do is to use ALTER SCHEMA for each object, for instance the following code changes the schema of Employee table from dbo to Payroll:
ALTER SCHMEA Payroll  -- Target Schema
TRANSFER
dbo.Enployee --Object to be transferred

Of course you can repeat it for all objects in the original schema using a simple loop, but personally I'd like to have a chance to look at what's being transferred and make changes/exclusions if necessary. With that in mind the following produces the script for transferring all objects which, you can view and modify if necessary before actually running the produced code:

DECLARE @origSchema NVARCHAR(100)
DECLARE @targetSchema NVARCHAR(100)
SET @OrigSchema = 'dbo' -- Replace it with the name of your original schema
SET @TargetSchema = 'payroll'  --replace it with the name of your new schema
SELECT  'ALTER SCHEMA ' + @targetSchema + ' TRANSFER ' + @origSchema + '.'
        + [name]
FROM    Sys.Objects
WHERE   schema_id = SCHEMA_ID(@OrigSchema)


Hope that hels!
Additional IMPORTANT NOTE: Please ensure that you have scripted all the permission of original object before transferring to new schema because permissions granted to original object e.g. dbo.testTable will be removed during transfer.