T-SQL help needed ....How do we copy schema objects from one schema to another ?

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 26, 2017 at 2:16 pm

  #401489

  

  So I created a new schema. Now I need to move the stored procedures from  schema "HEDIS_TO_HAP" to  HEDIS_TO_HAP2018" schema.

  Can you provide the T-SQl please ?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 26, 2017 at 2:37 pm

  #1925201

  mw112009 - Thursday, January 26, 2017 2:16 PM

  

  So I created a new schema. Now I need to move the stored procedures from  schema "HEDIS_TO_HAP" to  HEDIS_TO_HAP2018" schema.

  Can you provide the T-SQl please ?

  you want to use the ALTER SCHEMA TRANSFER command.it's for any object, whether a proc or table, or view etc
  
ALTER SCHEMA NEWSCHEMA TRANSFER OLDSCHEMA.OBJECTNAME
--your example
ALTER SCHEMA [HEDIS_TO_HAP2018] TRANSFER [HEDIS_TO_HAP].[MAKE_INT_MW_2017_Claims]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 27, 2017 at 6:16 am

  #1925302

  Lowell - Thursday, January 26, 2017 2:37 PM

  mw112009 - Thursday, January 26, 2017 2:16 PM

  

  So I created a new schema. Now I need to move the stored procedures from  schema "HEDIS_TO_HAP" to  HEDIS_TO_HAP2018" schema.

  Can you provide the T-SQl please ?

  you want to use the ALTER SCHEMA TRANSFER command.it's for any object, whether a proc or table, or view etc
  
ALTER SCHEMA NEWSCHEMA TRANSFER OLDSCHEMA.OBJECTNAME
--your example
ALTER SCHEMA [HEDIS_TO_HAP2018] TRANSFER [HEDIS_TO_HAP].[MAKE_INT_MW_2017_Claims]

  Thanks
  But I do want to move all objects in one command  ( tables, sp's and functions)

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 27, 2017 at 6:24 am

  #1925304

  mw112009 - Friday, January 27, 2017 6:16 AM

  Lowell - Thursday, January 26, 2017 2:37 PM

  mw112009 - Thursday, January 26, 2017 2:16 PM

  

  So I created a new schema. Now I need to move the stored procedures from  schema "HEDIS_TO_HAP" to  HEDIS_TO_HAP2018" schema.

  Can you provide the T-SQl please ?

  you want to use the ALTER SCHEMA TRANSFER command.it's for any object, whether a proc or table, or view etc
  
ALTER SCHEMA NEWSCHEMA TRANSFER OLDSCHEMA.OBJECTNAME
--your example
ALTER SCHEMA [HEDIS_TO_HAP2018] TRANSFER [HEDIS_TO_HAP].[MAKE_INT_MW_2017_Claims]

  Thanks
  But I do want to move all objects in one command  ( tables, sp's and functions)

  Just ran your command and it moved the object from one schema to another... This is not what I want. I need to copy all the objects from one schema to the new one.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 27, 2017 at 6:25 am

  #1925305

  COPY or MOVE? there's a difference!
  copy duplicates the data, so you have to script all the objects out, change the schema in that script , and create the objects like that.
  then insert from one table to another, taking into consideration SET IDENTITY_INSERT most likely as well.
  there is no easy way to do that. it's lots of dba work, some tools can make it easier, like Redgates SQL compare and Data Compare.

  if you are MOVING, here's some code :
  you have to build the commands from the meta data: sys.objects

  here's something i've quickly adapted for your use;
  I typically only move Tables between schemas, but i modified this to do tables/views/procs/functions.
  if you have other objects, like CLR int he schema, you'll have to modify to suite your requirements.
  
CREATE PROCEDURE MoveAllObjectsBetweenSchemas(@SourceSchema  VARCHAR(128),
                @DestinationSchema VARCHAR(128),
                @CreateSchemaIfNotExists BIT = 0)
AS
BEGIN
  DECLARE @err VARCHAR(2000);

    --#################################################################################################
  --Housekeeping and validation
  --#################################################################################################

    --source schema must exist, unless the override bit is sent
  --===================================================================================================
  IF NOT EXISTS(SELECT
        *
       FROM sys.schemas
       WHERE NAME = @SourceSchema)
   AND @CreateSchemaIfNotExists = 0
   BEGIN
    SET @err = 'The Schema '
        + Quotename(ISNULL(@SourceSchema, 'NULL'))
        + ' Does not exist in sys.schemas in the '
        + Quotename(Db_name()) + ' database.';

      RAISERROR(@err,16,1)
   END

    --destination schema must exist
  --===================================================================================================
  IF NOT EXISTS(SELECT
        *
       FROM sys.schemas
       WHERE NAME = @DestinationSchema)
   BEGIN
    SET @err = 'The Schema '
        + Quotename(ISNULL(@DestinationSchema, 'NULL'))
        + ' Does not exist in sys.schemas in the '
        + Quotename(Db_name()) + ' database.';

      RAISERROR(@err,16,1)
   END

    --destination schema must not contain any shared tables
  --===================================================================================================
  IF EXISTS (SELECT
       NAME
      FROM sys.tables
      WHERE SCHEMA_NAME(schema_id) = @DestinationSchema
      INTERSECT
      SELECT
       NAME
      FROM sys.tables
      WHERE SCHEMA_NAME(schema_id) = @SourceSchema)
   BEGIN
    SET @err = 'The Destination Schema '
        + Quotename(ISNULL(@DestinationSchema, 'NULL'))
        + ' shares at one or more tables with the same name as the '
        + Quotename(ISNULL(@DestinationSchema, 'NULL'))
        + ' Schema, and cannot continue until the Destination schema is cleared of identically named objects in the '
        + Quotename(Db_name()) + ' database.';

      RAISERROR(@err,16,1)
   END

    --you damn well must be sysadmin for this process
  --===================================================================================================
  IF NOT EXISTS(SELECT
        1
       WHERE Is_srvrolemember('sysadmin') = 1)
   BEGIN
    SET @err = 'This Stored procedure is specifically restricted to sysadmin users only, regardless of execute permissions in the '
        + Quotename(Db_name()) + ' database';
    RAISERROR(@err,16,1)
   END
  --###############################################################################################
  --Optional Create Schema If Not Exists
  --###############################################################################################
  IF @CreateSchemaIfNotExists = 1
   BEGIN
   --DECLARE @DestinationSchema VARCHAR(128) = 'SandBox'
  --DECLARE @SourceSchema VARCHAR(128) = 'PreLoad'
  --Schema [SandBox]
  --Optional Block to create missing schemas
  IF NOT EXISTS(SELECT
        *
       FROM sys.schemas
       WHERE NAME = @DestinationSchema)
   BEGIN
    DECLARE @cmd VARCHAR(500) = 'CREATE SCHEMA '
     + Quotename(@DestinationSchema) + ';'
    PRINT @cmd
    EXEC (@cmd)
   END;
   END --IF @CreateSchemaIfNotExists
  
  --###############################################################################################
  --core logic below: build the alter statements
  --###############################################################################################
  DECLARE @isql VARCHAR(8000)
  DECLARE c1 CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
   --###############################################################################################
   --cursor definition
   --###############################################################################################
   SELECT
   --example results 'ALTER SCHEMA [SandBox] TRANSFER [dbo].[Patient];'
    'ALTER SCHEMA '
    + Quotename(@DestinationSchema)
    + ' TRANSFER '
    + Quotename(OBJECT_SCHEMA_NAME(object_id))
    + '.' + Quotename(NAME) + ';'
   FROM sys.objects
   WHERE OBJECT_SCHEMA_NAME(object_id) = @SourceSchema
   AND [objects].[type_desc] IN('VIEW',
             'SQL_STORED_PROCEDURE',
             'USER_TABLE',
             'SQL_INLINE_TABLE_VALUED_FUNCTION',
             'SQL_SCALAR_FUNCTION',
             'SQL_TABLE_VALUED_FUNCTION')
  --###############################################################################################
  OPEN c1
  FETCH next FROM c1 INTO @isql
  WHILE @@FETCH_STATUS <> -1
   BEGIN
    PRINT @isql
    EXEC(@isql)
    FETCH next FROM c1 INTO @isql
   END
  CLOSE c1
  DEALLOCATE c1
END

  GO

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 27, 2017 at 7:13 am

  #1925319

  Lowell - Friday, January 27, 2017 6:25 AM

  COPY or MOVE? there's a difference!
  copy duplicates the data, so you have to script all the objects out, change the schema in that script , and create the objects like that.
  then insert from one table to another, taking into consideration SET IDENTITY_INSERT most likely as well.
  there is no easy way to do that. it's lots of dba work, some tools can make it easier, like Redgates SQL compare and Data Compare.

  if you are MOVING, here's some code :
  you have to build the commands from the meta data: sys.objects

  here's something i've quickly adapted for your use;
  I typically only move Tables between schemas, but i modified this to do tables/views/procs/functions.
  if you have other objects, like CLR int he schema, you'll have to modify to suite your requirements.
  
CREATE PROCEDURE MoveAllObjectsBetweenSchemas(@SourceSchema  VARCHAR(128),
                @DestinationSchema VARCHAR(128),
                @CreateSchemaIfNotExists BIT = 0)
AS
BEGIN
  DECLARE @err VARCHAR(2000);

    --#################################################################################################
  --Housekeeping and validation
  --#################################################################################################

    --source schema must exist, unless the override bit is sent
  --===================================================================================================
  IF NOT EXISTS(SELECT
        *
       FROM sys.schemas
       WHERE NAME = @SourceSchema)
   AND @CreateSchemaIfNotExists = 0
   BEGIN
    SET @err = 'The Schema '
        + Quotename(ISNULL(@SourceSchema, 'NULL'))
        + ' Does not exist in sys.schemas in the '
        + Quotename(Db_name()) + ' database.';

      RAISERROR(@err,16,1)
   END

    --destination schema must exist
  --===================================================================================================
  IF NOT EXISTS(SELECT
        *
       FROM sys.schemas
       WHERE NAME = @DestinationSchema)
   BEGIN
    SET @err = 'The Schema '
        + Quotename(ISNULL(@DestinationSchema, 'NULL'))
        + ' Does not exist in sys.schemas in the '
        + Quotename(Db_name()) + ' database.';

      RAISERROR(@err,16,1)
   END

    --destination schema must not contain any shared tables
  --===================================================================================================
  IF EXISTS (SELECT
       NAME
      FROM sys.tables
      WHERE SCHEMA_NAME(schema_id) = @DestinationSchema
      INTERSECT
      SELECT
       NAME
      FROM sys.tables
      WHERE SCHEMA_NAME(schema_id) = @SourceSchema)
   BEGIN
    SET @err = 'The Destination Schema '
        + Quotename(ISNULL(@DestinationSchema, 'NULL'))
        + ' shares at one or more tables with the same name as the '
        + Quotename(ISNULL(@DestinationSchema, 'NULL'))
        + ' Schema, and cannot continue until the Destination schema is cleared of identically named objects in the '
        + Quotename(Db_name()) + ' database.';

      RAISERROR(@err,16,1)
   END

    --you damn well must be sysadmin for this process
  --===================================================================================================
  IF NOT EXISTS(SELECT
        1
       WHERE Is_srvrolemember('sysadmin') = 1)
   BEGIN
    SET @err = 'This Stored procedure is specifically restricted to sysadmin users only, regardless of execute permissions in the '
        + Quotename(Db_name()) + ' database';
    RAISERROR(@err,16,1)
   END
  --###############################################################################################
  --Optional Create Schema If Not Exists
  --###############################################################################################
  IF @CreateSchemaIfNotExists = 1
   BEGIN
   --DECLARE @DestinationSchema VARCHAR(128) = 'SandBox'
  --DECLARE @SourceSchema VARCHAR(128) = 'PreLoad'
  --Schema [SandBox]
  --Optional Block to create missing schemas
  IF NOT EXISTS(SELECT
        *
       FROM sys.schemas
       WHERE NAME = @DestinationSchema)
   BEGIN
    DECLARE @cmd VARCHAR(500) = 'CREATE SCHEMA '
     + Quotename(@DestinationSchema) + ';'
    PRINT @cmd
    EXEC (@cmd)
   END;
   END --IF @CreateSchemaIfNotExists
  
  --###############################################################################################
  --core logic below: build the alter statements
  --###############################################################################################
  DECLARE @isql VARCHAR(8000)
  DECLARE c1 CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
   --###############################################################################################
   --cursor definition
   --###############################################################################################
   SELECT
   --example results 'ALTER SCHEMA [SandBox] TRANSFER [dbo].[Patient];'
    'ALTER SCHEMA '
    + Quotename(@DestinationSchema)
    + ' TRANSFER '
    + Quotename(OBJECT_SCHEMA_NAME(object_id))
    + '.' + Quotename(NAME) + ';'
   FROM sys.objects
   WHERE OBJECT_SCHEMA_NAME(object_id) = @SourceSchema
   AND [objects].[type_desc] IN('VIEW',
             'SQL_STORED_PROCEDURE',
             'USER_TABLE',
             'SQL_INLINE_TABLE_VALUED_FUNCTION',
             'SQL_SCALAR_FUNCTION',
             'SQL_TABLE_VALUED_FUNCTION')
  --###############################################################################################
  OPEN c1
  FETCH next FROM c1 INTO @isql
  WHILE @@FETCH_STATUS <> -1
   BEGIN
    PRINT @isql
    EXEC(@isql)
    FETCH next FROM c1 INTO @isql
   END
  CLOSE c1
  DEALLOCATE c1
END

  GO

  So there is no direct command for copying  objects.....

  But I was able to do it.....  I had created a ssis package that will write to a flat file the definition of all tables, sps, functions in a given database. ( I forgot that I had created that already ). Only thing I had to do was to tweak it to include only objects from the HEDIS_TO_HAP schema. Did that it worked... here is the output
  So now I only have to do a search and replace ( Change the old schema name to the new schema name, run the script and I am done , Cool ) 

  If you do like I can send you the dtsx file..  ( You just need to change the Project Parameters  and run ) 

  This is a part of the file that was generated ( below )