Sometimes We try to run database
script of database from one server to another server, without removing schema
suffix, then we need to change the Schema of the database according to new
existing server.
Suppose Suffix of your database
is “test” (ex. Test.tablename), and on new server schema name is “dbo” (ex. Dbo.tablename).
Then in this case after executing script, you have to change the schema name
from test à dbo.
Change Schema name
of One object ( ex. Table):
ALTER SCHEMA
NewSchemaName TRANSFER OldSchemaName.ObjectName
Change Schema of all tables, Procedure & Views:
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name
FROM sys.Objects o
INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
WHERE s.Name = 'oldschema'
And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
Output After executing above Query:
ALTER SCHEMA dbo TRANSFER test.tblInfo
ALTER SCHEMA dbo TRANSFER test.tblStudent
ALTER SCHEMA dbo TRANSFER test.InsertStudent
ALTER SCHEMA dbo TRANSFER test.InsertInfo
ALTER SCHEMA dbo TRANSFER test.tblAddress
ALTER SCHEMA dbo TRANSFER test.tblEducation
ALTER SCHEMA dbo TRANSFER test.GetStudents
..
..
..
We will get list of queries as
given above. You have to copy list of all these query and execute it. Schema name
will be changed in all objects.
