【SQL开源代码栏目提醒】:本文主要为网学会员提供CreateTaskListDBSql7.sql,希望对需要CreateTaskListDBSql7.sql网友有所帮助,学习一下!
CREATE DATABASE TaskList
GO
USE TaskList
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListNames]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListNames] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) NOT NULL
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListUsers] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (50) NOT NULL CONSTRAINT [DF_TaskListUsers_Username] DEFAULT (''),
[PasswordHash] [varchar] (50) NOT NULL CONSTRAINT [DF_TaskListUsers_PasswordHash] DEFAULT (''),
[IsManager] [bit] NOT NULL CONSTRAINT [DF_TaskListUsers_IsManager] DEFAULT (0),
[IsAdministrator] [bit] NOT NULL CONSTRAINT [DF_TaskListUsers_IsAdministrator] DEFAULT (0),
[StartupViewOption] [varchar] (20) NOT NULL CONSTRAINT [DF_TaskListUsers_StartupViewOption] DEFAULT ('LastTaskList'),
[StartupViewArgument] [int] NULL ,
[ItemsToDisplay] [varchar] (20) NOT NULL CONSTRAINT [DF_TaskListUsers_ItemsToDisplay] DEFAULT ('All'),
[ShouldNotify] [bit] NOT NULL CONSTRAINT [DF_TaskListUsers_ShouldNotify] DEFAULT (0),
[NotifyPeriod] [int] NOT NULL CONSTRAINT [DF_TaskListUsers_NotifyPeriod] DEFAULT (24),
[LastNotified] [datetime] NOT NULL CONSTRAINT [DF__tasklistu__LastN__095F58DF] DEFAULT (getdate())
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListItems] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TaskListID] [int] NOT NULL ,
[OwnerUserID] [int] NOT NULL ,
[CreatedDate] [datetime] NOT NULL ,
[ModifiedDate] [datetime] NOT NULL ,
[Subject] [varchar] (255) NULL ,
[Body] [varchar] (5000) NULL ,
[Status] [int] NOT NULL CONSTRAINT [DF_TaskListItems_Status] DEFAULT (0),
[CategoryID] [int] NULL ,
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListCategories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListCategories] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) NOT NULL ,
[IconUrl] [varchar] (500) NULL
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListStatusShowPreferences]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListStatusShowPreferences] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[StatusID] [int] NOT NULL
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListAssignments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListAssignments] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[TaskListID] [int] NOT NULL ,
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListStatus] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[StatusDescription] [varchar] (50) NOT NULL CONSTRAINT [DF_TaskListStatus_StatusDescription] DEFAULT (''),
[StatusValue] [int] NOT NULL CONSTRAINT [DF_TaskListStatus_StatusValue] DEFAULT (0),
[StatusFontFlags] [varchar] (500) NOT NULL CONSTRAINT [DF_TaskListStatus_StatusFontFlags] DEFAULT (''),
[DefaultSortOrder] [int] NOT NULL CONSTRAINT [DF_TaskListStatus_DefaultSortOrder] DEFAULT (0)
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListSortOrderPreferences]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListSortOrderPreferences] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[StatusID] [int] NOT NULL ,
[SortOrder] [int] NOT NULL
)
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TaskListNotification]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TaskListNotification] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[NotificationEmail] [varchar] (5000) NOT NULL
)
END
GO
-- =============================================
-- TaskList_Add
-- =============================================
-- First checks to see if the user specif