none
Executar comandos para vários Schemas RRS feed

  • Pergunta

  •  Bom dia!

      Preciso executar instruções sql de atualização de objetos(campos, tables, stored procedure, etc) em todos os schemas de um database, como por exemplo, tenho a seguinte estrutura simples para exemplificar:

       Database ERP

            Schema XXX
              Tabela Clientes

            Schema YYY
              Tabela Clientes

            Schema ZZZ
                Tabela Clientes

      Agora, por exemplo, preciso criar a tabela CarteiraContato para todos os schemas, onde tenho o script abaixo, onde antes do nome da tabela possuo a palavra [schema].  O que eu quero é pelo T-SQL possivelmente poder rodar o script abaixo substituindo a palavra schema para cada schema da base e executar a instrução para cada um.

      Outra maneira que pensei é ter a instrução abaixo sem a informação do [schema], e ter uma forma por T-SQL de executar a instrução para cada schema da base automaticamente.

       Alguem poderia ajudar nesse sentido?

    USE [ERP]
    GO

    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    SET ARITHABORT ON
    SET NUMERIC_ROUNDABORT OFF
    SET CONCAT_NULL_YIELDS_NULL ON
    SET XACT_ABORT ON
    GO


    BEGIN TRANSACTION
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [schema].[CarteiraContato]
    (
    [CARID] [int] IDENTITY(1,1),
    [USUID] [int] NULL,
    [CONID] [int] NULL,
    [CARPERTIPO] [nvarchar](12) COLLATE Latin1_General_CI_AS NULL,
    [CARSEMANA] [char](3) COLLATE Latin1_General_CI_AS NULL,
    [CARDIA] [int] NULL,
    [CARDTCON] [date] NULL,
    [CARSTAT] [smallint] NULL,
    [CARDTCAD] [date] NULL
    ) ON [PRIMARY]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    ALTER TABLE [schema].[CarteiraContato] ADD CONSTRAINT [PK_CarteiraContato] PRIMARY KEY CLUSTERED
    (
    [CARID] ASC
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO

    COMMIT TRANSACTION
    GO

    quinta-feira, 25 de junho de 2015 14:19

Respostas

  • Boa Tarde!

    Pensando aqui de forma bem rápida, eu só vejo um jeito de executar isso.

    Que é através de comando dinâmico. Trocando o nome do schema para cada comando

    Tem varias outra formas de executar isso, mas a que foi mais rápida de fazer foi essa.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    SET ANSI_WARNINGS ON
    GO
    SET ARITHABORT ON
    GO
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET CONCAT_NULL_YIELDS_NULL ON
    GO
    SET XACT_ABORT ON
    
    GO
    IF OBJECT_ID('tempdb..#schemas') IS NOT NULL DROP TABLE #schemas
    create table #schemas
    (
    	nomeschema varchar(128)
    )
    
    
    GO
    
    insert into #schemas
    select name from sys.schemas
    where schema_id =1 --dbo
    	or (schema_id >4 and schema_id < 16384)--de 16384 para cima é criado pelo sistema
    
    declare  @valor nvarchar(128)
    		,@sqlbase nvarchar(max) 
    		,@sql nvarchar(max)
    --dentro de um comando dinâmico de sql não é permitido o uso do GO,
    --então temos dual alternativas, ou cria a pk dessa forma que eu criei
    --quebra o comando em dois, e executa todos os creates e depois os comando de alter table
    set @sqlbase = '
    	CREATE TABLE [schema].[CarteiraContato]
    	(
    	[CARID] [int] IDENTITY(1,1) PRIMARY KEY,
    	[USUID] [int] NULL,
    	[CONID] [int] NULL,
    	[CARPERTIPO] [nvarchar](12) COLLATE Latin1_General_CI_AS NULL,
    	[CARSEMANA] [char](3) COLLATE Latin1_General_CI_AS NULL,
    	[CARDIA] [int] NULL,
    	[CARDTCON] [date] NULL,
    	[CARSTAT] [smallint] NULL,
    	[CARDTCAD] [date] NULL
    	) ON [PRIMARY]
    '	
    
    DECLARE FC CURSOR FAST_FORWARD FOR
        SELECT nomeschema
        FROM #schemas  
    OPEN FC
    FETCH NEXT FROM FC INTO @valor
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
    	BEGIN TRY
    		BEGIN TRAN TCREATE
    			set @sql = REPLACE(@sqlbase,'[schema]', '[' + @valor+']');
    			select @sql
    			exec sp_executesql @statement = @sql;
    		COMMIT TRAN TCREATE
    	END TRY
    	BEGIN CATCH
    		ROLLBACK TRAN TCREATE
    		declare @msg varchar(2000)
    		set @msg = 'Erro ao criar a tabela no schema:' + @valor + '. Erro:' + ERROR_MESSAGE()
    		RAISERROR(@msg, 16,1)
    	END CATCH
    	FETCH NEXT FROM FC INTO @valor   
    END   
    CLOSE FC   
    DEALLOCATE FC

    quinta-feira, 25 de junho de 2015 17:10

Todas as Respostas

  • Boa Tarde!

    Pensando aqui de forma bem rápida, eu só vejo um jeito de executar isso.

    Que é através de comando dinâmico. Trocando o nome do schema para cada comando

    Tem varias outra formas de executar isso, mas a que foi mais rápida de fazer foi essa.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    SET ANSI_WARNINGS ON
    GO
    SET ARITHABORT ON
    GO
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET CONCAT_NULL_YIELDS_NULL ON
    GO
    SET XACT_ABORT ON
    
    GO
    IF OBJECT_ID('tempdb..#schemas') IS NOT NULL DROP TABLE #schemas
    create table #schemas
    (
    	nomeschema varchar(128)
    )
    
    
    GO
    
    insert into #schemas
    select name from sys.schemas
    where schema_id =1 --dbo
    	or (schema_id >4 and schema_id < 16384)--de 16384 para cima é criado pelo sistema
    
    declare  @valor nvarchar(128)
    		,@sqlbase nvarchar(max) 
    		,@sql nvarchar(max)
    --dentro de um comando dinâmico de sql não é permitido o uso do GO,
    --então temos dual alternativas, ou cria a pk dessa forma que eu criei
    --quebra o comando em dois, e executa todos os creates e depois os comando de alter table
    set @sqlbase = '
    	CREATE TABLE [schema].[CarteiraContato]
    	(
    	[CARID] [int] IDENTITY(1,1) PRIMARY KEY,
    	[USUID] [int] NULL,
    	[CONID] [int] NULL,
    	[CARPERTIPO] [nvarchar](12) COLLATE Latin1_General_CI_AS NULL,
    	[CARSEMANA] [char](3) COLLATE Latin1_General_CI_AS NULL,
    	[CARDIA] [int] NULL,
    	[CARDTCON] [date] NULL,
    	[CARSTAT] [smallint] NULL,
    	[CARDTCAD] [date] NULL
    	) ON [PRIMARY]
    '	
    
    DECLARE FC CURSOR FAST_FORWARD FOR
        SELECT nomeschema
        FROM #schemas  
    OPEN FC
    FETCH NEXT FROM FC INTO @valor
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
    	BEGIN TRY
    		BEGIN TRAN TCREATE
    			set @sql = REPLACE(@sqlbase,'[schema]', '[' + @valor+']');
    			select @sql
    			exec sp_executesql @statement = @sql;
    		COMMIT TRAN TCREATE
    	END TRY
    	BEGIN CATCH
    		ROLLBACK TRAN TCREATE
    		declare @msg varchar(2000)
    		set @msg = 'Erro ao criar a tabela no schema:' + @valor + '. Erro:' + ERROR_MESSAGE()
    		RAISERROR(@msg, 16,1)
    	END CATCH
    	FETCH NEXT FROM FC INTO @valor   
    END   
    CLOSE FC   
    DEALLOCATE FC

    quinta-feira, 25 de junho de 2015 17:10
  •   Olá Vitor, obrigado pela resposta, mas tenho 3 dúvidas abaixo:

      1) Realmente a Chave Primária eu não posso criar como você colocou, pois preciso declarar o nome correto, necessitando fazer o alter table. Como eu faria quebrando em dois como vc comentou para poder executar a instrução abaixo? 

    ALTER TABLE [schema].[CarteiraContato] ADD CONSTRAINT [PK_CarteiraContato] PRIMARY KEY CLUSTERED
    (
    [CARID] ASC
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

      2) No caso de eu precisar ter outros comandos de alter procedure, table, drop, etc, posso colocar tudo dentro daquela variavel @sqlbase dentro das aspas simples?

      3) Existe uma forma de eu ter todas as instruções em um arquivo externo(.sql) onde a instrução exec sp_executesql @statement = @sql apontasse para o conteudo deste arquivo?

      Agradeço desde já sua ajuda!

      


    sexta-feira, 26 de junho de 2015 20:39