OPMS/03-程式設計階段/Develop/Trunk/DB/201611142341/DB.sql

��USE [master]
GO
/****** Object:  Database [odb_generic]    Script Date: 2016/11/14 NHS 11:43:05 ******/
CREATE DATABASE [odb_generic]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'odb_generic', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.HERCULESMS2014\MSSQL\DATA\odb_generic.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'odb_generic_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.HERCULESMS2014\MSSQL\DATA\odb_generic_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [odb_generic] SET COMPATIBILITY_LEVEL = 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [odb_generic].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [odb_generic] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [odb_generic] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [odb_generic] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [odb_generic] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [odb_generic] SET ARITHABORT OFF 
GO
ALTER DATABASE [odb_generic] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [odb_generic] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [odb_generic] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [odb_generic] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [odb_generic] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [odb_generic] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [odb_generic] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [odb_generic] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [odb_generic] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [odb_generic] SET  DISABLE_BROKER 
GO
ALTER DATABASE [odb_generic] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [odb_generic] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [odb_generic] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [odb_generic] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [odb_generic] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [odb_generic] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [odb_generic] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [odb_generic] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [odb_generic] SET  MULTI_USER 
GO
ALTER DATABASE [odb_generic] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [odb_generic] SET DB_CHAINING OFF 
GO
ALTER DATABASE [odb_generic] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [odb_generic] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
ALTER DATABASE [odb_generic] SET DELAYED_DURABILITY = DISABLED 
GO
USE [odb_generic]
GO
/****** Object:  User [ta]    Script Date: 2016/11/14 NHS 11:43:05 ******/
CREATE USER [ta] FOR LOGIN [ta] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [ta]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ta]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [ta]
GO
/****** Object:  Table [dbo].[otb_role]    Script Date: 2016/11/14 NHS 11:43:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[otb_role](
	[guid] [char](36) NOT NULL CONSTRAINT [DF_tb_role_guid]  DEFAULT (newid()),
	[role_id] [varchar](16) NULL,
	[active_flag] [char](1) NOT NULL CONSTRAINT [DF_tb_role_active_flag]  DEFAULT ('Y'),
	[create_user_guid] [char](36) NULL,
	[create_date] [datetime] NOT NULL CONSTRAINT [DF_tb_role_create_date]  DEFAULT (getdate()),
	[modify_user_guid] [char](36) NULL,
	[modify_date] [datetime] NOT NULL CONSTRAINT [DF_tb_role_modify_date]  DEFAULT (getdate()),
 CONSTRAINT [PK_tb_role] PRIMARY KEY CLUSTERED 
(
	[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[otb_session]    Script Date: 2016/11/14 NHS 11:43:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[otb_session](
	[guid] [char](36) NOT NULL CONSTRAINT [DF_otb_session_guid]  DEFAULT (newid()),
	[active_flag] [char](1) NOT NULL CONSTRAINT [DF_otb_session_active_flag]  DEFAULT ('Y'),
	[create_user_guid] [char](36) NULL,
	[create_date] [datetime] NOT NULL CONSTRAINT [DF_otb_session_create_date]  DEFAULT (getdate()),
	[modify_user_guid] [char](36) NULL,
	[modify_date] [datetime] NOT NULL CONSTRAINT [DF_otb_session_modify_date]  DEFAULT (getdate()),
 CONSTRAINT [PK_otb_session] PRIMARY KEY CLUSTERED 
(
	[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[otb_user]    Script Date: 2016/11/14 NHS 11:43:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[otb_user](
	[guid] [char](36) NOT NULL CONSTRAINT [DF_tb_user_guid]  DEFAULT (newid()),
	[user_id] [nvarchar](16) NOT NULL,
	[role_guid] [char](36) NULL,
	[user_name] [nvarchar](32) NULL,
	[email] [varchar](64) NULL,
	[tel] [varchar](20) NULL,
	[mobile] [varchar](15) NULL,
	[active_flag] [char](1) NOT NULL CONSTRAINT [DF_tb_user_active_flag]  DEFAULT ('Y'),
	[create_user_guid] [char](36) NULL,
	[create_date] [datetime] NOT NULL CONSTRAINT [DF_tb_user_create_date]  DEFAULT (getdate()),
	[modify_user_guid] [char](36) NULL,
	[modify_date] [datetime] NOT NULL CONSTRAINT [DF_tb_user_modify_date]  DEFAULT (getdate()),
 CONSTRAINT [PK_tb_user] PRIMARY KEY CLUSTERED 
(
	[guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[otb_role] ([guid], [role_id], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'F7B925A1-54B9-4E28-B585-AA4347B49747', N'Admin', N'Y', NULL, CAST(N'2016-08-22 23:42:17.853' AS DateTime), NULL, CAST(N'2016-08-22 23:42:17.853' AS DateTime))
INSERT [dbo].[otb_role] ([guid], [role_id], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'FBF6C416-E973-4174-A3AD-299C13AE311C', N'User', N'Y', NULL, CAST(N'2016-08-22 23:42:44.833' AS DateTime), NULL, CAST(N'2016-08-22 23:42:44.833' AS DateTime))
INSERT [dbo].[otb_session] ([guid], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'e718ad5a-ba58-405a-9cc0-815f5807ab54', N'Y', N'D19126A3-CABC-4CD6-BA73-3766D8C51222', CAST(N'2016-11-14 23:35:28.737' AS DateTime), N'D19126A3-CABC-4CD6-BA73-3766D8C51222', CAST(N'2016-11-14 23:35:28.737' AS DateTime))
INSERT [dbo].[otb_user] ([guid], [user_id], [role_guid], [user_name], [email], [tel], [mobile], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'287DD4C7-D445-493A-8E33-46217B639F41', N'Member1', N'FBF6C416-E973-4174-A3AD-299C13AE311C', N'Merber 1', NULL, NULL, NULL, N'Y', N'D19126A3-CABC-4CD6-BA73-3766D8C51222', CAST(N'2016-08-22 23:47:44.807' AS DateTime), N'D19126A3-CABC-4CD6-BA73-3766D8C51222', CAST(N'2016-08-22 23:47:44.807' AS DateTime))
INSERT [dbo].[otb_user] ([guid], [user_id], [role_guid], [user_name], [email], [tel], [mobile], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'D19126A3-CABC-4CD6-BA73-3766D8C51222', N'Hercules', N'F7B925A1-54B9-4E28-B585-AA4347B49747', N'Hercules Chen', NULL, NULL, NULL, N'Y', NULL, CAST(N'2016-08-22 23:46:30.030' AS DateTime), NULL, CAST(N'2016-08-22 23:46:30.030' AS DateTime))
ALTER TABLE [dbo].[otb_role]  WITH CHECK ADD  CONSTRAINT [FK_tb_role_tb_user_create_user] FOREIGN KEY([create_user_guid])
REFERENCES [dbo].[otb_user] ([guid])
GO
ALTER TABLE [dbo].[otb_role] CHECK CONSTRAINT [FK_tb_role_tb_user_create_user]
GO
ALTER TABLE [dbo].[otb_role]  WITH CHECK ADD  CONSTRAINT [FK_tb_role_tb_user_modify_user] FOREIGN KEY([modify_user_guid])
REFERENCES [dbo].[otb_user] ([guid])
GO
ALTER TABLE [dbo].[otb_role] CHECK CONSTRAINT [FK_tb_role_tb_user_modify_user]
GO
ALTER TABLE [dbo].[otb_session]  WITH CHECK ADD  CONSTRAINT [FK_otb_session_tb_user_create_user] FOREIGN KEY([create_user_guid])
REFERENCES [dbo].[otb_user] ([guid])
GO
ALTER TABLE [dbo].[otb_session] CHECK CONSTRAINT [FK_otb_session_tb_user_create_user]
GO
ALTER TABLE [dbo].[otb_session]  WITH CHECK ADD  CONSTRAINT [FK_otb_session_tb_user_modify_user] FOREIGN KEY([modify_user_guid])
REFERENCES [dbo].[otb_user] ([guid])
GO
ALTER TABLE [dbo].[otb_session] CHECK CONSTRAINT [FK_otb_session_tb_user_modify_user]
GO
ALTER TABLE [dbo].[otb_user]  WITH CHECK ADD  CONSTRAINT [FK_tb_user_tb_role] FOREIGN KEY([role_guid])
REFERENCES [dbo].[otb_role] ([guid])
GO
ALTER TABLE [dbo].[otb_user] CHECK CONSTRAINT [FK_tb_user_tb_role]
GO
ALTER TABLE [dbo].[otb_user]  WITH CHECK ADD  CONSTRAINT [FK_tb_user_tb_user_create_user] FOREIGN KEY([create_user_guid])
REFERENCES [dbo].[otb_user] ([guid])
GO
ALTER TABLE [dbo].[otb_user] CHECK CONSTRAINT [FK_tb_user_tb_user_create_user]
GO
ALTER TABLE [dbo].[otb_user]  WITH CHECK ADD  CONSTRAINT [FK_tb_user_tb_user_modify_user] FOREIGN KEY([modify_user_guid])
REFERENCES [dbo].[otb_user] ([guid])
GO
ALTER TABLE [dbo].[otb_user] CHECK CONSTRAINT [FK_tb_user_tb_user_modify_user]
GO
USE [master]
GO
ALTER DATABASE [odb_generic] SET  READ_WRITE 
GO