#Change database Schema to your choice.
#Schema change
#Quick Trick#2
Today I got you another trick to generate schema change commands very very quickly and smartly.
We had a request to restore a database from Dev environment and change the schema on the destination server with some XYZ schema.
Now to generate the schema alteration command for all the tables functions etc. it would take proportional time with the size of database.
Remember Quick trick1 once we used to generate the command to change recovery model of all the databases on instance click here to see more . We used the same logic and generated the commands that I am sharing with you.
--This is the script I used to generate ALTER statements for all objects
-- the schema for each object in cc was changed to cx.
--please note the case for case sensitive servers.
--U=user tables,
-- P=Stored Procedures
--V=VIEW
--'FN', 'FS', 'FT'=Functions
SELECT 'ALTER SCHEMA CX TRANSFER [' + SysSchemas.name + '].[' +DbObjects.Name+ '];', SysSchemas.name,DbObjects.type,DbObjects.type_desc
FROM sys.objectsDbObjects
INNER JOIN sys.schemasSysSchemasONDbObjects.schema_id=SysSchemas.schema_id
WHERE SysSchemas.name = 'CC'
AND (DbObjects.typeIN ('U', 'P', 'V', 'FN', 'FS', 'FT'))
order bytype_desc, name
This will generate the Alter commands and you are good to execute.
For some you may get error but find out in the object type it must be already present, so you need to delete.
We had a request to restore a database from Dev environment and change the schema on the destination server with some XYZ schema.
Now to generate the schema alteration command for all the tables functions etc. it would take proportional time with the size of database.
Remember Quick trick1 once we used to generate the command to change recovery model of all the databases on instance click here to see more . We used the same logic and generated the commands that I am sharing with you.
--This is the script I used to generate ALTER statements for all objects
-- the schema for each object in cc was changed to cx.
--please note the case for case sensitive servers.
--U=user tables,
-- P=Stored Procedures
--V=VIEW
--'FN', 'FS', 'FT'=Functions
SELECT 'ALTER SCHEMA CX TRANSFER [' + SysSchemas.name + '].[' +DbObjects.Name+ '];', SysSchemas.name,DbObjects.type,DbObjects.type_desc
FROM sys.objectsDbObjects
INNER JOIN sys.schemasSysSchemasONDbObjects.schema_id=SysSchemas.schema_id
WHERE SysSchemas.name = 'CC'
AND (DbObjects.typeIN ('U', 'P', 'V', 'FN', 'FS', 'FT'))
order bytype_desc, name
This will generate the Alter commands and you are good to execute.
For some you may get error but find out in the object type it must be already present, so you need to delete.
Thankful: To Mahboob Haidari: (Expert SQL DBA L3++ ( more than that may be L5) from Downers grove Illinois,US) for this knowledge.
NOTE; Do comment and let me know any topic you wish to see and your feedback is always welcome.
