ALTER ALL TABLE,Procedure and UDF SCHEMA IN A SINGLE QUERY

 

By using this query we can alter the schema to SCHEMANAME .

select 'ALTER SCHEMA SCHEMANAME transfer ['+SCHEMA_NAME(schema_id)+'].['+name +']' from sys.tables

OUTPUT

select all the query and execute in query window.All the schema will automatically changes to SCHEMANAME

Note :sys.tables – Returns a row for each table object ,currently only with

sys.objects.type =U

SCHEMA_NAME(schema_id) – Returns the schema name associated with schema_id

schema_id – ID of the schema.Schema_id is a int. If schema is not defined schema name of default will return

E.g : select SCHEMA_NAME() from sys.tables

OUTPUT

For Procedure

select 'ALTER SCHEMA SCHEMANAME transfer ['+SCHEMA_NAME(schema_id)+'].['+name +']' from sys.procedures

For UDF

select 'ALTER SCHEMA SCHEMANAME transfer ['+specific_schema+'].['+specific_name +']'
FROM information_schema.routines
WHERE routine_type='function'

Note : routines - Returns one row for each stored procedure and function that can be accessed by the current user in the current database.