sql-server-2012 - tabla - sql script with data

¿Cómo generar SQL Server 2012 scripts en sql server 2008 r2?

Question

Tengo un script de SQL Server 2012 para crear una base de datos y necesito generarlo en SQL Server 2008 R2. Crea la base de datos pero no se crean tablas ni vistas, etc.

Este es mi guion:

/*
Deployment script for CMS
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar DatabaseName "CMS"
:setvar DefaultFilePrefix "CMS"
:setvar DefaultDataPath "C:/Program Files/Microsoft SQL
Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/"
:setvar DefaultLogPath "C:/Program Files/Microsoft SQL
Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/"
GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not
supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N''$(__IsSqlCmdEnabled)'' NOT LIKE N''True''
BEGIN
PRINT N''SQLCMD mode must be enabled to successfully execute this
script.'';
SET NOEXEC ON;
END
GO
USE [master];
GO
IF (DB_ID(N''$(DatabaseName)'') IS NOT NULL)
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [$(DatabaseName)];
END
GO
PRINT N''Creating $(DatabaseName)...''
GO
CREATE DATABASE [$(DatabaseName)]
ON
PRIMARY(NAME = [$(DatabaseName)], FILENAME =
N''$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf'')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME =
N''$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf'') COLLATE
SQL_Latin1_General_CP1_CI_AS
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET ANSI_NULLS ON,
ANSI_PADDING ON,
ANSI_WARNINGS ON,
ARITHABORT ON,
CONCAT_NULL_YIELDS_NULL ON,
NUMERIC_ROUNDABORT OFF,
QUOTED_IDENTIFIER ON,
ANSI_NULL_DEFAULT ON,
CURSOR_DEFAULT LOCAL,
RECOVERY FULL,
CURSOR_CLOSE_ON_COMMIT OFF,
AUTO_CREATE_STATISTICS ON,
AUTO_SHRINK OFF,
AUTO_UPDATE_STATISTICS ON,
RECURSIVE_TRIGGERS OFF
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [$(DatabaseName)]
SET AUTO_CLOSE OFF
WITH ROLLBACK IMMEDIATE;
END
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET ALLOW_SNAPSHOT_ISOLATION OFF;
END
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
END
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
PAGE_VERIFY NONE,
DATE_CORRELATION_OPTIMIZATION OFF,
DISABLE_BROKER,
PARAMETERIZATION SIMPLE,
SUPPLEMENTAL_LOGGING OFF
WITH ROLLBACK IMMEDIATE;
END
GO
IF IS_SRVROLEMEMBER(N''sysadmin'') = 1
BEGIN
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
EXECUTE sp_executesql N''ALTER DATABASE [$(DatabaseName)]
SET TRUSTWORTHY OFF,
DB_CHAINING OFF
WITH ROLLBACK IMMEDIATE'';
END
END
ELSE
BEGIN
PRINT N''The database settings cannot be modified. You must be a SysAdmin
to apply these settings.'';
END
GO
IF IS_SRVROLEMEMBER(N''sysadmin'') = 1
BEGIN
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
EXECUTE sp_executesql N''ALTER DATABASE [$(DatabaseName)]
SET HONOR_BROKER_PRIORITY OFF
WITH ROLLBACK IMMEDIATE'';
END
END
ELSE
BEGIN
PRINT N''The database settings cannot be modified. You must be a SysAdmin
to apply these settings.'';
END
GO
ALTER DATABASE [$(DatabaseName)]
SET TARGET_RECOVERY_TIME = 0 SECONDS
WITH ROLLBACK IMMEDIATE;
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
CONTAINMENT = NONE
WITH ROLLBACK IMMEDIATE;
END
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
DELAYED_DURABILITY = DISABLED
WITH ROLLBACK IMMEDIATE;
END
GO
USE [$(DatabaseName)];
GO
IF fulltextserviceproperty(N''IsFulltextInstalled'') = 1
EXECUTE sp_fulltext_database ''enable'';
GO
PRINT N''Creating [Evangelism]...'';
GO
CREATE SCHEMA [Evangelism]
AUTHORIZATION [dbo];
GO
PRINT N''Creating [Evangelism].[tbl_Note]...'';
GO
CREATE TABLE [Evangelism].[tbl_Note] (
[NoteId] INT IDENTITY (1, 1) NOT NULL,
[Description] NVARCHAR (1000) NOT NULL,
[CreatedDate] DATETIME NOT NULL,
[UpdatedDate] DATETIME NULL,
[ProspectId] INT NOT NULL,
[CreatedBy] INT NOT NULL,
[UpdatedBy] INT NULL,
CONSTRAINT [pk_Note_NoteId] PRIMARY KEY CLUSTERED ([NoteId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Address]...'';
GO
CREATE TABLE [dbo].[tbl_Address] (
[AddressId] INT IDENTITY (1, 1) NOT NULL,
[AddressLine1] NVARCHAR (100) NOT NULL,
[AddressLine2] NVARCHAR (100) NULL,
[Postcode] NVARCHAR (10) NOT NULL,
[BarangayId] INT NULL,
[DistrictId] INT NULL,
[SuburbId] INT NULL,
[CityId] INT NULL,
[ProvinceId] INT NULL,
[StateId] INT NULL,
[CountryId] INT NULL,
CONSTRAINT [pk_Address_AddressId] PRIMARY KEY CLUSTERED ([AddressId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Barangay]...'';
GO
CREATE TABLE [dbo].[tbl_Barangay] (
[BarangayId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Barangay_BarangayId] PRIMARY KEY CLUSTERED ([BarangayId]
ASC),
CONSTRAINT [uq_Barangay_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Church]...'';
GO
CREATE TABLE [dbo].[tbl_Church] (
[ChurchId] INT IDENTITY (1, 1) NOT NULL,
[Code] NVARCHAR (50) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
[ContactPerson] NVARCHAR (100) NOT NULL,
[AddressId] INT NOT NULL,
[ContactId] INT NOT NULL,
CONSTRAINT [pk_Church_ChurchId] PRIMARY KEY CLUSTERED ([ChurchId] ASC),
CONSTRAINT [uq_Church_Code] UNIQUE NONCLUSTERED ([Code] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_City]...'';
GO
CREATE TABLE [dbo].[tbl_City] (
[CityId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_City_CityId] PRIMARY KEY CLUSTERED ([CityId] ASC),
CONSTRAINT [uq_City_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Contact]...'';
GO
CREATE TABLE [dbo].[tbl_Contact] (
[ContactId] INT IDENTITY (1, 1) NOT NULL,
[Description] NVARCHAR (100) NOT NULL,
[ContactTypeId] INT NOT NULL,
CONSTRAINT [pk_Contact_ContactId] PRIMARY KEY CLUSTERED ([ContactId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_ContactType]...'';
GO
CREATE TABLE [dbo].[tbl_ContactType] (
[ContactTypeId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_ContactType_ContactTypeId] PRIMARY KEY CLUSTERED
([ContactTypeId] ASC),
CONSTRAINT [uq_ContactType_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Country]...'';
GO
CREATE TABLE [dbo].[tbl_Country] (
[CountryId] INT IDENTITY (1, 1) NOT NULL,
[Code] NVARCHAR (3) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Country_CountryId] PRIMARY KEY CLUSTERED ([CountryId] ASC),
CONSTRAINT [uq_Country_Code] UNIQUE NONCLUSTERED ([Code] ASC),
CONSTRAINT [uq_Country_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_District]...'';
GO
CREATE TABLE [dbo].[tbl_District] (
[DistrictId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_District_DistrictId] PRIMARY KEY CLUSTERED ([DistrictId]
ASC),
CONSTRAINT [uq_District_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Member]...'';
GO
CREATE TABLE [dbo].[tbl_Member] (
[MemberId] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (50) NOT NULL,
[MiddleName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NOT NULL,
[BaptismDate] DATE NULL,
[ChurchId] INT NOT NULL,
[AddressId] INT NOT NULL,
[ContactId] INT NOT NULL,
CONSTRAINT [pk_Member_MemberId] PRIMARY KEY CLUSTERED ([MemberId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Prospect]...'';
GO
CREATE TABLE [dbo].[tbl_Prospect] (
[ProspectId] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (50) NOT NULL,
[MiddleName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NOT NULL,
[LastContactDate] DATE NOT NULL,
[ChurchId] INT NOT NULL,
[AssignedTo] INT NULL,
[AddressId] INT NOT NULL,
[ContactId] INT NOT NULL,
[ProspectStatusId] INT NOT NULL,
CONSTRAINT [pk_Prospect_ProspectId] PRIMARY KEY CLUSTERED ([ProspectId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_ProspectStatus]...'';
GO
CREATE TABLE [dbo].[tbl_ProspectStatus] (
[ProspectStatusId] INT IDENTITY (1, 1) NOT NULL,
[Description] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_ProspectStatus_ProspectStatusId] PRIMARY KEY CLUSTERED
([ProspectStatusId] ASC),
CONSTRAINT [uq_ProspectStatus_Description] UNIQUE NONCLUSTERED
([Description] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Province]...'';
GO
CREATE TABLE [dbo].[tbl_Province] (
[ProvinceId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Province_ProvinceId] PRIMARY KEY CLUSTERED ([ProvinceId]
ASC),
CONSTRAINT [uq_Province_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_State]...'';
GO
CREATE TABLE [dbo].[tbl_State] (
[StateId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_State_StateId] PRIMARY KEY CLUSTERED ([StateId] ASC),
CONSTRAINT [uq_State_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Suburb]...'';
GO
CREATE TABLE [dbo].[tbl_Suburb] (
[SuburbId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Suburb_SuburbId] PRIMARY KEY CLUSTERED ([SuburbId] ASC),
CONSTRAINT [uq_Suburb_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [Evangelism].[fk_Note_ProspectId]...'';
GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_ProspectId] FOREIGN KEY ([ProspectId]) REFERENCES
[dbo].[tbl_Prospect] ([ProspectId]);
GO
PRINT N''Creating [Evangelism].[fk_Note_CreatedBy]...'';
GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_CreatedBy] FOREIGN KEY ([CreatedBy]) REFERENCES
[dbo].[tbl_Member] ([MemberId]);
GO
PRINT N''Creating [Evangelism].[fk_Note_UpdatedBy]...'';
GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_UpdatedBy] FOREIGN KEY ([UpdatedBy]) REFERENCES
[dbo].[tbl_Member] ([MemberId]);
GO
PRINT N''Creating [dbo].[fk_Address_BarangayId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_BarangayId] FOREIGN KEY ([BarangayId]) REFERENCES
[dbo].[tbl_Barangay] ([BarangayId]);
GO
PRINT N''Creating [dbo].[fk_Address_DistrictId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_DistrictId] FOREIGN KEY ([DistrictId]) REFERENCES
[dbo].[tbl_District] ([DistrictId]);
GO
PRINT N''Creating [dbo].[fk_Address_SuburbId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_SuburbId] FOREIGN KEY ([SuburbId]) REFERENCES
[dbo].[tbl_Suburb] ([SuburbId]);
GO
PRINT N''Creating [dbo].[fk_Address_CityId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_CityId] FOREIGN KEY ([CityId]) REFERENCES [dbo].
[tbl_City] ([CityId]);
GO
PRINT N''Creating [dbo].[fk_Address_ProvinceId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_ProvinceId] FOREIGN KEY ([ProvinceId]) REFERENCES
[dbo].[tbl_Province] ([ProvinceId]);
GO
PRINT N''Creating [dbo].[fk_Address_StateId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_StateId] FOREIGN KEY ([StateId]) REFERENCES
[dbo].[tbl_State] ([StateId]);
GO
PRINT N''Creating [dbo].[fk_Address_CountryId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_CountryId] FOREIGN KEY ([CountryId]) REFERENCES
[dbo].[tbl_Country] ([CountryId]);
GO
PRINT N''Creating [dbo].[fk_Church_AddressId]...'';
GO
ALTER TABLE [dbo].[tbl_Church]
ADD CONSTRAINT [fk_Church_AddressId] FOREIGN KEY ([AddressId]) REFERENCES
[dbo].[tbl_Address] ([AddressId]);
GO
PRINT N''Creating [dbo].[fk_Church_ContactId]...'';
GO
ALTER TABLE [dbo].[tbl_Church]
ADD CONSTRAINT [fk_Church_ContactId] FOREIGN KEY ([ContactId]) REFERENCES
[dbo].[tbl_Contact] ([ContactId]);
GO
PRINT N''Creating [dbo].[fk_Contact_ContactTypeId]...'';
GO
ALTER TABLE [dbo].[tbl_Contact]
ADD CONSTRAINT [fk_Contact_ContactTypeId] FOREIGN KEY ([ContactTypeId])
REFERENCES [dbo].[tbl_ContactType] ([ContactTypeId]);
GO
PRINT N''Creating [dbo].[fk_Member_ChurchId]...'';
GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_ChurchId] FOREIGN KEY ([ChurchId]) REFERENCES
[dbo].[tbl_Church] ([ChurchId]);
GO
PRINT N''Creating [dbo].[fk_Member_AddressId]...'';
GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_AddressId] FOREIGN KEY ([AddressId]) REFERENCES
[dbo].[tbl_Address] ([AddressId]);
GO
PRINT N''Creating [dbo].[fk_Member_ContactId]...'';
GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_ContactId] FOREIGN KEY ([ContactId]) REFERENCES
[dbo].[tbl_Contact] ([ContactId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_ChurchId]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ChurchId] FOREIGN KEY ([ChurchId]) REFERENCES
[dbo].[tbl_Church] ([ChurchId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_AssignedTo]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_AssignedTo] FOREIGN KEY ([AssignedTo])
REFERENCES [dbo].[tbl_Member] ([MemberId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_AddressId]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_AddressId] FOREIGN KEY ([AddressId]) REFERENCES
[dbo].[tbl_Address] ([AddressId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_ContactId]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ContactId] FOREIGN KEY ([ContactId]) REFERENCES
[dbo].[tbl_Contact] ([ContactId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_ProspectStatusId]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ProspectStatusId] FOREIGN KEY
([ProspectStatusId]) REFERENCES [dbo].[tbl_ProspectStatus]
([ProspectStatusId]);
GO
DECLARE @VarDecimalSupported AS BIT;
SELECT @VarDecimalSupported = 0;
IF ((ServerProperty(N''EngineEdition'') = 3)
AND (((@@microsoftversion / power(2, 24) = 9)
AND (@@microsoftversion & 0xffff >= 3024))
OR ((@@microsoftversion / power(2, 24) = 10)
AND (@@microsoftversion & 0xffff >= 1600))))
SELECT @VarDecimalSupported = 1;
IF (@VarDecimalSupported > 0)
BEGIN
EXECUTE sp_db_vardecimal_storage_format N''$(DatabaseName)'', ''ON'';
END
GO
PRINT N''Update complete.'';
GO

Tengo errores:

Msg 102, nivel 15, estado 6, línea 2
Sintaxis incorrecta cerca de ''TARGET_RECOVERY_TIME''.

Msg 319, nivel 15, estado 1, línea 3
Sintaxis incorrecta cerca de la palabra clave ''con''. Si esta instrucción es una expresión de tabla común, una cláusula xmlnamespaces o una cláusula de contexto de seguimiento de cambios, la instrucción anterior debe terminarse con un punto y coma.

Msg 102, nivel 15, estado 1, línea 3
Sintaxis incorrecta cerca de ''INMEDIATO''.

Se encontró un error durante la ejecución del lote. Saliendo

Espero que alguien pueda ayudar. ¡Gracias!

Answer 1

Comentando algunas de esas declaraciones funciona para mí.

/*
Deployment script for CMS
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar DatabaseName "CMS"
:setvar DefaultFilePrefix "CMS"
:setvar DefaultDataPath "C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/"
:setvar DefaultLogPath "C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/DATA/"
GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N''$(__IsSqlCmdEnabled)'' NOT LIKE N''True''
BEGIN
PRINT N''SQLCMD mode must be enabled to successfully execute this script.'';
SET NOEXEC ON;
END
GO
USE [master];
GO
IF (DB_ID(N''$(DatabaseName)'') IS NOT NULL)
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [$(DatabaseName)];
END
GO
PRINT N''Creating $(DatabaseName)...''
GO
CREATE DATABASE [$(DatabaseName)]
ON
PRIMARY(NAME = [$(DatabaseName)], FILENAME = N''$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf'')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N''$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf'') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET ANSI_NULLS ON,
ANSI_PADDING ON,
ANSI_WARNINGS ON,
ARITHABORT ON,
CONCAT_NULL_YIELDS_NULL ON,
NUMERIC_ROUNDABORT OFF,
QUOTED_IDENTIFIER ON,
ANSI_NULL_DEFAULT ON,
CURSOR_DEFAULT LOCAL,
RECOVERY FULL,
CURSOR_CLOSE_ON_COMMIT OFF,
AUTO_CREATE_STATISTICS ON,
AUTO_SHRINK OFF,
AUTO_UPDATE_STATISTICS ON,
RECURSIVE_TRIGGERS OFF
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [$(DatabaseName)]
SET AUTO_CLOSE OFF
WITH ROLLBACK IMMEDIATE;
END
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET ALLOW_SNAPSHOT_ISOLATION OFF;
END
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
END
GO
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
PAGE_VERIFY NONE,
DATE_CORRELATION_OPTIMIZATION OFF,
DISABLE_BROKER,
PARAMETERIZATION SIMPLE,
SUPPLEMENTAL_LOGGING OFF
WITH ROLLBACK IMMEDIATE;
END
GO
IF IS_SRVROLEMEMBER(N''sysadmin'') = 1
BEGIN
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
EXECUTE sp_executesql N''ALTER DATABASE [$(DatabaseName)]
SET TRUSTWORTHY OFF,
DB_CHAINING OFF
WITH ROLLBACK IMMEDIATE'';
END
END
ELSE
BEGIN
PRINT N''The database settings cannot be modified. You must be a SysAdmin to apply these settings.'';
END
GO
IF IS_SRVROLEMEMBER(N''sysadmin'') = 1
BEGIN
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N''$(DatabaseName)'')
BEGIN
EXECUTE sp_executesql N''ALTER DATABASE [$(DatabaseName)]
SET HONOR_BROKER_PRIORITY OFF
WITH ROLLBACK IMMEDIATE'';
END
END
ELSE
BEGIN
PRINT N''The database settings cannot be modified. You must be a SysAdmin to apply these settings.'';
END
--GO
--ALTER DATABASE [$(DatabaseName)]
-- SET TARGET_RECOVERY_TIME = 0 SECONDS
-- WITH ROLLBACK IMMEDIATE;
--GO
--IF EXISTS (SELECT 1
-- FROM [master].[dbo].[sysdatabases]
-- WHERE [name] = N''$(DatabaseName)'')
-- BEGIN
-- ALTER DATABASE [$(DatabaseName)]
-- SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
-- CONTAINMENT = NONE
-- WITH ROLLBACK IMMEDIATE;
-- END
--GO
--IF EXISTS (SELECT 1
-- FROM [master].[dbo].[sysdatabases]
-- WHERE [name] = N''$(DatabaseName)'')
-- BEGIN
-- ALTER DATABASE [$(DatabaseName)]
-- SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
-- MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
-- DELAYED_DURABILITY = DISABLED
-- WITH ROLLBACK IMMEDIATE;
-- END
GO
USE [$(DatabaseName)];
GO
IF fulltextserviceproperty(N''IsFulltextInstalled'') = 1
EXECUTE sp_fulltext_database ''enable'';
GO
PRINT N''Creating [Evangelism]...'';
GO
CREATE SCHEMA [Evangelism]
AUTHORIZATION [dbo];
GO
PRINT N''Creating [Evangelism].[tbl_Note]...'';
GO
CREATE TABLE [Evangelism].[tbl_Note] (
[NoteId] INT IDENTITY (1, 1) NOT NULL,
[Description] NVARCHAR (1000) NOT NULL,
[CreatedDate] DATETIME NOT NULL,
[UpdatedDate] DATETIME NULL,
[ProspectId] INT NOT NULL,
[CreatedBy] INT NOT NULL,
[UpdatedBy] INT NULL,
CONSTRAINT [pk_Note_NoteId] PRIMARY KEY CLUSTERED ([NoteId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Address]...'';
GO
CREATE TABLE [dbo].[tbl_Address] (
[AddressId] INT IDENTITY (1, 1) NOT NULL,
[AddressLine1] NVARCHAR (100) NOT NULL,
[AddressLine2] NVARCHAR (100) NULL,
[Postcode] NVARCHAR (10) NOT NULL,
[BarangayId] INT NULL,
[DistrictId] INT NULL,
[SuburbId] INT NULL,
[CityId] INT NULL,
[ProvinceId] INT NULL,
[StateId] INT NULL,
[CountryId] INT NULL,
CONSTRAINT [pk_Address_AddressId] PRIMARY KEY CLUSTERED ([AddressId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Barangay]...'';
GO
CREATE TABLE [dbo].[tbl_Barangay] (
[BarangayId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Barangay_BarangayId] PRIMARY KEY CLUSTERED ([BarangayId] ASC),
CONSTRAINT [uq_Barangay_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Church]...'';
GO
CREATE TABLE [dbo].[tbl_Church] (
[ChurchId] INT IDENTITY (1, 1) NOT NULL,
[Code] NVARCHAR (50) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
[ContactPerson] NVARCHAR (100) NOT NULL,
[AddressId] INT NOT NULL,
[ContactId] INT NOT NULL,
CONSTRAINT [pk_Church_ChurchId] PRIMARY KEY CLUSTERED ([ChurchId] ASC),
CONSTRAINT [uq_Church_Code] UNIQUE NONCLUSTERED ([Code] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_City]...'';
GO
CREATE TABLE [dbo].[tbl_City] (
[CityId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_City_CityId] PRIMARY KEY CLUSTERED ([CityId] ASC),
CONSTRAINT [uq_City_Name] UNIQUE NONCLUSTERED ([Name] ASC)

);

GO
PRINT N''Creating [dbo].[tbl_Contact]...'';
GO
CREATE TABLE [dbo].[tbl_Contact] (
[ContactId] INT IDENTITY (1, 1) NOT NULL,
[Description] NVARCHAR (100) NOT NULL,
[ContactTypeId] INT NOT NULL,
CONSTRAINT [pk_Contact_ContactId] PRIMARY KEY CLUSTERED ([ContactId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_ContactType]...'';
GO
CREATE TABLE [dbo].[tbl_ContactType] (
[ContactTypeId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_ContactType_ContactTypeId] PRIMARY KEY CLUSTERED ([ContactTypeId] ASC),
CONSTRAINT [uq_ContactType_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Country]...'';
GO
CREATE TABLE [dbo].[tbl_Country] (
[CountryId] INT IDENTITY (1, 1) NOT NULL,
[Code] NVARCHAR (3) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Country_CountryId] PRIMARY KEY CLUSTERED ([CountryId] ASC),
CONSTRAINT [uq_Country_Code] UNIQUE NONCLUSTERED ([Code] ASC),
CONSTRAINT [uq_Country_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_District]...'';
GO
CREATE TABLE [dbo].[tbl_District] (
[DistrictId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_District_DistrictId] PRIMARY KEY CLUSTERED ([DistrictId] ASC),
CONSTRAINT [uq_District_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Member]...'';
GO
CREATE TABLE [dbo].[tbl_Member] (
[MemberId] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (50) NOT NULL,
[MiddleName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NOT NULL,
[BaptismDate] DATE NULL,
[ChurchId] INT NOT NULL,
[AddressId] INT NOT NULL,
[ContactId] INT NOT NULL,
CONSTRAINT [pk_Member_MemberId] PRIMARY KEY CLUSTERED ([MemberId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Prospect]...'';
GO
CREATE TABLE [dbo].[tbl_Prospect] (
[ProspectId] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (50) NOT NULL,
[MiddleName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NOT NULL,
[LastContactDate] DATE NOT NULL,
[ChurchId] INT NOT NULL,
[AssignedTo] INT NULL,
[AddressId] INT NOT NULL,
[ContactId] INT NOT NULL,
[ProspectStatusId] INT NOT NULL,
CONSTRAINT [pk_Prospect_ProspectId] PRIMARY KEY CLUSTERED ([ProspectId] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_ProspectStatus]...'';
GO
CREATE TABLE [dbo].[tbl_ProspectStatus] (
[ProspectStatusId] INT IDENTITY (1, 1) NOT NULL,
[Description] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_ProspectStatus_ProspectStatusId] PRIMARY KEY CLUSTERED ([ProspectStatusId] ASC),
CONSTRAINT [uq_ProspectStatus_Description] UNIQUE NONCLUSTERED ([Description] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Province]...'';
GO
CREATE TABLE [dbo].[tbl_Province] (
[ProvinceId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Province_ProvinceId] PRIMARY KEY CLUSTERED ([ProvinceId] ASC),
CONSTRAINT [uq_Province_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_State]...'';
GO
CREATE TABLE [dbo].[tbl_State] (
[StateId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_State_StateId] PRIMARY KEY CLUSTERED ([StateId] ASC),
CONSTRAINT [uq_State_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [dbo].[tbl_Suburb]...'';
GO
CREATE TABLE [dbo].[tbl_Suburb] (
[SuburbId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (100) NOT NULL,
CONSTRAINT [pk_Suburb_SuburbId] PRIMARY KEY CLUSTERED ([SuburbId] ASC),
CONSTRAINT [uq_Suburb_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
PRINT N''Creating [Evangelism].[fk_Note_ProspectId]...'';
GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_ProspectId] FOREIGN KEY ([ProspectId]) REFERENCES [dbo].[tbl_Prospect] ([ProspectId]);
GO
PRINT N''Creating [Evangelism].[fk_Note_CreatedBy]...'';
GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_CreatedBy] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[tbl_Member] ([MemberId]);
GO
PRINT N''Creating [Evangelism].[fk_Note_UpdatedBy]...'';
GO
ALTER TABLE [Evangelism].[tbl_Note]
ADD CONSTRAINT [fk_Note_UpdatedBy] FOREIGN KEY ([UpdatedBy]) REFERENCES [dbo].[tbl_Member] ([MemberId]);
GO
PRINT N''Creating [dbo].[fk_Address_BarangayId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_BarangayId] FOREIGN KEY ([BarangayId]) REFERENCES [dbo].[tbl_Barangay] ([BarangayId]);
GO
PRINT N''Creating [dbo].[fk_Address_DistrictId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_DistrictId] FOREIGN KEY ([DistrictId]) REFERENCES [dbo].[tbl_District] ([DistrictId]);
GO
PRINT N''Creating [dbo].[fk_Address_SuburbId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_SuburbId] FOREIGN KEY ([SuburbId]) REFERENCES [dbo].[tbl_Suburb] ([SuburbId]);
GO
PRINT N''Creating [dbo].[fk_Address_CityId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_CityId] FOREIGN KEY ([CityId]) REFERENCES [dbo].[tbl_City] ([CityId]);
GO
PRINT N''Creating [dbo].[fk_Address_ProvinceId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_ProvinceId] FOREIGN KEY ([ProvinceId]) REFERENCES [dbo].[tbl_Province] ([ProvinceId]);
GO
PRINT N''Creating [dbo].[fk_Address_StateId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_StateId] FOREIGN KEY ([StateId]) REFERENCES [dbo].[tbl_State] ([StateId]);
GO
PRINT N''Creating [dbo].[fk_Address_CountryId]...'';
GO
ALTER TABLE [dbo].[tbl_Address]
ADD CONSTRAINT [fk_Address_CountryId] FOREIGN KEY ([CountryId]) REFERENCES [dbo].[tbl_Country] ([CountryId]);
GO
PRINT N''Creating [dbo].[fk_Church_AddressId]...'';
GO
ALTER TABLE [dbo].[tbl_Church]
ADD CONSTRAINT [fk_Church_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [dbo].[tbl_Address] ([AddressId]);
GO
PRINT N''Creating [dbo].[fk_Church_ContactId]...'';
GO
ALTER TABLE [dbo].[tbl_Church]
ADD CONSTRAINT [fk_Church_ContactId] FOREIGN KEY ([ContactId]) REFERENCES [dbo].[tbl_Contact] ([ContactId]);
GO
PRINT N''Creating [dbo].[fk_Contact_ContactTypeId]...'';
GO
ALTER TABLE [dbo].[tbl_Contact]
ADD CONSTRAINT [fk_Contact_ContactTypeId] FOREIGN KEY ([ContactTypeId]) REFERENCES [dbo].[tbl_ContactType] ([ContactTypeId]);
GO
PRINT N''Creating [dbo].[fk_Member_ChurchId]...'';
GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_ChurchId] FOREIGN KEY ([ChurchId]) REFERENCES [dbo].[tbl_Church] ([ChurchId]);
GO
PRINT N''Creating [dbo].[fk_Member_AddressId]...'';
GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [dbo].[tbl_Address] ([AddressId]);
GO
PRINT N''Creating [dbo].[fk_Member_ContactId]...'';
GO
ALTER TABLE [dbo].[tbl_Member]
ADD CONSTRAINT [fk_Member_ContactId] FOREIGN KEY ([ContactId]) REFERENCES
[dbo].[tbl_Contact] ([ContactId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_ChurchId]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ChurchId] FOREIGN KEY ([ChurchId]) REFERENCES [dbo].[tbl_Church] ([ChurchId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_AssignedTo]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_AssignedTo] FOREIGN KEY ([AssignedTo]) REFERENCES [dbo].[tbl_Member] ([MemberId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_AddressId]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [dbo].[tbl_Address] ([AddressId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_ContactId]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ContactId] FOREIGN KEY ([ContactId]) REFERENCES [dbo].[tbl_Contact] ([ContactId]);
GO
PRINT N''Creating [dbo].[fk_Prospect_ProspectStatusId]...'';
GO
ALTER TABLE [dbo].[tbl_Prospect]
ADD CONSTRAINT [fk_Prospect_ProspectStatusId] FOREIGN KEY ([ProspectStatusId]) REFERENCES [dbo].[tbl_ProspectStatus] ([ProspectStatusId]);
GO
DECLARE @VarDecimalSupported AS BIT;
SELECT @VarDecimalSupported = 0;
IF ((ServerProperty(N''EngineEdition'') = 3)
AND (((@@microsoftversion / power(2, 24) = 9)
AND (@@microsoftversion & 0xffff >= 3024))
OR ((@@microsoftversion / power(2, 24) = 10)
AND (@@microsoftversion & 0xffff >= 1600))))
SELECT @VarDecimalSupported = 1;
IF (@VarDecimalSupported > 0)
BEGIN
EXECUTE sp_db_vardecimal_storage_format N''$(DatabaseName)'', ''ON'';
END
GO
PRINT N''Update complete.'';
GO

¡Gracias por tu ayuda!