如何在sql server 2008 r2中生成sql server 2012脚本? [英] How to generate sql server 2012 scripts in sql server 2008 r2?
本文介绍了如何在sql server 2008 r2中生成sql server 2012脚本?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个来自 SQL Server 2012 的脚本来创建数据库，我需要在 SQL Server 2008 R2 中生成它.它创建数据库，但没有创建表和视图等.
I have a script from SQL Server 2012 to create a database and I need to generate it in SQL Server 2008 R2. It create the database but no tables and views etc is created.
这是我的脚本:
/*
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
我遇到了错误:
消息 102，级别 15，状态 6，第 2 行
TARGET_RECOVERY_TIME"附近的语法不正确.
Msg 102, Level 15, State 6, Line 2
Incorrect syntax near 'TARGET_RECOVERY_TIME'.
消息 319，级别 15，状态 1，第 3 行
关键字with"附近的语法不正确.如果此语句是公用表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前一条语句必须以分号终止.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
消息 102，级别 15，状态 1，第 3 行
'IMMEDIATE' 附近的语法不正确.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'IMMEDIATE'.
执行批处理时遇到错误.退出.
An error was encountered during execution of batch. Exiting.
希望有人能帮忙.谢谢！
Hope someone could help. Thanks!
推荐答案
注释掉其中一些语句对我有用.
Commenting out some of those statements works for me.
/*
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
感谢您的帮助！
这篇关于如何在sql server 2008 r2中生成sql server 2012脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
