Executar comandos para vários Schemas

Question

 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

Answer 1

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

Answer 2

  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!