Execute multiple DDL statements dynamically with a single script?

Try below solution (I tested with success):

Instead of run this (at last):

execute (@commands)

Run below proc to go around limit:

EXEC dbo.execute_MULTI_command @commands;

Definition of SQL proc dbo.execute_MULTI_command:

CREATE or alter PROC dbo.execute_MULTI_command 
@commands varchar(max) = '
    go
    create trigger tr_Countries on
        dbo.Countries for insert as
    begin
        print ''A new country was created.''
    end
    go
    create trigger tr_Cities on
        dbo.Cities for insert as
    begin
        print ''A new city was created.''
    end
'
as

BEGIN
declare @search_string varchar(64)='create trigger';


declare @i_begin int = 0;
declare @i_end   int = 0;
declare @each_command varchar(max) = NULL;

while @i_begin < len(@commands)
    begin
        set @i_end=CHARINDEX(@search_string, @commands, @i_begin+1)
        --print @i_begin;
        --print @i_end;
        if @i_end=0
          break;
        select @each_command = SUBSTRING(@commands,@i_begin,@i_end-@i_begin);
        --print @each_command;
        execute (@each_command);
        select @i_begin = @i_end
    end
declare @last_command varchar(max)=SUBSTRING(@commands,@i_begin,len(@commands)-@i_begin);
--print @last_command;
execute (@last_command);

END

The whole approach has some smell...

Why?

1. Well, triggers have a small always. But there are cases... Try to avoid them...
2. There are many mature approaches to achieve something like this, just to name a few:

• Database projects in Visual Studio
  (allow for a clean "master", will create diff scripts to any sibling)
• EF-Migrations (if your work may follow code-first principles)
• Database compare tools
  (various free and commercial tools create diff scripts between versions of databases).

It looks like you trying to re-invent such tools...

However, some statements are not possible to be called within the the same batch. Some tell you something like "must be the first command", others, like CREATE TRIGGER, are very picky in many ways (e.g. cannot be applied to another database than the one in USE and don't allow USE otherDB in dynamically created statements.)

What can you do?

1. Try to re-think your approach and find existing tools for the same.
2. Create a separated script and use a SP like the other answer to execute as a one-liner.
   Hint: I would not use GO, but something more distinct, like #$%mySep%$# (read GO is not a statement)
3. Use your own batch execution with separated commands like the following example (one of the rare cases where I use a CURSOR):

-- a test db just not to ruin anything in your system

CREATE DATABASE dummyShnugo;
GO
USE dummyShnugo;
GO

--creating your command stack (ID IDENTITY helps with the sorting)

DECLARE @tbl TABLE(ID INT IDENTITY, cmd NVARCHAR(MAX));
INSERT INTO @tbl(cmd) VALUES(N'CREATE TABLE Test(ID INT IDENTITY, SomeText NVARCHAR(100), SomeDate DATETIME2);');
INSERT INTO @tbl(cmd) VALUES(N'
CREATE TRIGGER tr_Test ON Test FOR INSERT 
AS
BEGIN
    UPDATE Test SET SomeDate = SYSUTCDATETIME() WHERE ID IN(SELECT inserted.ID FROM inserted); --yeah, no need for this, DEFAULT does the same.
END');
INSERT INTO @tbl(cmd) VALUES(N'INSERT INTO Test(SomeText) VALUES(N''abc''),(N''def'');');
INSERT INTO @tbl(cmd) VALUES(N'SELECT * FROM Test;');

-- CURSOR iteration for execution (you might use PRINT instead of EXEC)

DECLARE @cmd NVARCHAR(MAX);
DECLARE cur CURSOR FOR SELECT cmd FROM @tbl ORDER BY ID;
OPEN cur
FETCH NEXT FROM cur INTO @cmd;

WHILE @@FETCH_STATUS=0
BEGIN
    --PRINT @cmd;
    EXEC (@cmd);
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;
GO

--Clean up

USE master;
GO
DROP DATABASE dummyShnugo;

The way I found (on my own) is to emulate the 'go' batches by nesting 'execute()' commands in the string to execute.

Then I can apply this amazing pattern (IMHO) to metadata maintenance:

• Select the objects to be processed
• Create commands over those objects with a query
• Compile the commands list into a string
• Execute dynamically the commands string

/* Clean up code */
drop table if exists geo.Continents
drop table if exists geo.Countries
drop table if exists geo.Cities
drop schema if exists geo
go

/* Setup code */
create schema geo
create table geo.Continents(
    id int identity not null primary key,
    Continent varchar(50) not null
)
create table geo.Countries(
    id int identity not null primary key,
    Country varchar(50) not null
)
create table geo.Cities(
    id int identity not null primary key,
    City varchar(50) not null
)
go

/* Generate the create trigger commands and execute them */
declare @Commands varchar(max)
declare @command varchar(max) = '
execute(''    
    create or alter trigger geo.tr_%s_i 
        on geo.%s for insert 
    as
        print ''''A new rew(s) was(were) created in the table: %s.''''
'')'
declare @geo_schema_id int = schema_id('geo')

;with GeoTables as (
    /* Get the 'geo' schema tables */
    select name
        from sys.tables
        where schema_id=@geo_schema_id 
)
,CommandsList as (
    select formatmessage(@command, name, name, name)  Command
        from GeoTables
)
select @Commands = string_agg(cast(Command as varchar(max)), char(13))
    from CommandsList

print @Commands
/* Execute dynamically the commands string */
Execute(@Commands)