There are many ways to crack this problem. This technique changes the line separator to be two new lines:

use strict;
use warnings;

my $filename = "MyFileName0000";

local $/ = "\n\n";

while (<DATA>) {
    chomp;
    ++$filename;
    print "**** Open file $filename here\n";
    print "$_\n";
    print "**** Close file $filename here\n";
}

__DATA__
CREATE TABLE sec_afs
(
rpt_per_typ_c char(1) NOT NULL,
rpt_per_typ_t varchar(20) NULL,
LOCK ALLPAGES
go
EXEC sp_primarykey 'sec_afs', rpt_per_typ_c
go
GRANT SELECT ON sec_afs TO developer_read_only
go

CREATE TABLE dbo.sec_iccc
(
user_nt_id_c char(16) NOT NULL,
unit_id_c char(4) NOT NULL
)
LOCK ALLPAGES
go
GRANT SELECT ON sec_iccc TO developer_read_only
go

CREATE TABLE sac_recon(
rec_number int NOT NULL,
rec_grp_number int NOT NULL,
)
go
[download]

Prints:

**** Open file MyFileName0001 here
CREATE TABLE sec_afs
(
rpt_per_typ_c char(1) NOT NULL,
rpt_per_typ_t varchar(20) NULL,
LOCK ALLPAGES
go
EXEC sp_primarykey 'sec_afs', rpt_per_typ_c
go
GRANT SELECT ON sec_afs TO developer_read_only
go
**** Close file MyFileName0001 here
**** Open file MyFileName0002 here
CREATE TABLE dbo.sec_iccc
(
user_nt_id_c char(16) NOT NULL,
unit_id_c char(4) NOT NULL
)
LOCK ALLPAGES
go
GRANT SELECT ON sec_iccc TO developer_read_only
go
**** Close file MyFileName0002 here
**** Open file MyFileName0003 here
CREATE TABLE sac_recon(
rec_number int NOT NULL,
rec_grp_number int NOT NULL,
)
go

**** Close file MyFileName0003 here
[download]

Update: Note cdarke's++ improvement of using "" to get 'paragraph' behavior.