sql.txt

是 个典型的 OA系统

CREATE TABLE [dbo].[Document] (
	[DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
	[AccepterID] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[Date] [datetime] NULL ,
	[State] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[PersonID] [int] NULL ,
	[Result] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
	[Content] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
	[Process] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[WriterID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[CheckedMarker] [nvarchar] (120) COLLATE Chinese_PRC_CI_AS NULL ,
	[Contents] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
;

CREATE TABLE [dbo].[Files] (
	[FileID] [int] IDENTITY (1, 1) NOT NULL ,
	[Title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Writer] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[State] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[PersonID] [int] NULL ,
	[Date] [datetime] NULL ,
	[SystemID] [int] NULL ,
	[OrganizationID] [int] NULL ,
	[Contents] [image] NULL ,
	[ContentType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ByteSize] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[FileKind] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[AccepterID] [nvarchar] (120) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
;

CREATE TABLE [dbo].[InfoCategories] (
	[InfoCategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[InfoCategoryName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[OrganizationID] [int] NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[Infomation] (
	[InfoID] [int] IDENTITY (1, 1) NOT NULL ,
	[Writer] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Title] [nvarchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
	[PersonID] [int] NOT NULL ,
	[Date] [datetime] NULL ,
	[State] [int] NULL ,
	[InfoCategoryID] [int] NULL ,
	[Contents] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
;

CREATE TABLE [dbo].[Job] (
	[JobID] [int] IDENTITY (1, 1) NOT NULL ,
	[JobName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[Meeting] (
	[MeetingID] [int] IDENTITY (1, 1) NOT NULL ,
	[Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Caller] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Attends] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[Date] [datetime] NULL ,
	[Address] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[State] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[AccepterID] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[MeetingDate] [datetime] NULL ,
	[PersonID] [int] NULL ,
	[Result] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[ReadTime] [datetime] NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[Message] (
	[MessageID] [int] IDENTITY (1, 1) NOT NULL ,
	[Contents] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
	[Date] [datetime] NULL ,
	[AccepterID] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[state] [int] NULL ,
	[PersonID] [int] NOT NULL ,
	[ReadTime] [datetime] NULL ,
	[Attachment] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[Notification] (
	[NotificationID] [int] IDENTITY (1, 1) NOT NULL ,
	[State] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Contents] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[PersonID] [int] NULL ,
	[Date] [datetime] NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[Online] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[UserSN] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Date] [datetime] NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[Organization] (
	[OrganizationID] [int] IDENTITY (1, 1) NOT NULL ,
	[OrganizationName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SystemID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[TypeID] [int] NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[Persons] (
	[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[OrganizationID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[JobID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Friends] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[Address] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Phoneo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Mobile] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Password] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[State] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Phonef] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[Private] (
	[PrivateID] [int] NOT NULL ,
	[Contents] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[PersonID] [int] NULL ,
	[Date] [datetime] NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[Schedule] (
	[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
	[PlanTime] [datetime] NULL ,
	[Itime] [datetime] NULL ,
	[Contents] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[Result] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[State] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[PersonID] [int] NULL ,
	[Date] [datetime] NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[System] (
	[SystemID] [int] IDENTITY (1, 1) NOT NULL ,
	[SystemName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
;

ALTER TABLE [dbo].[Document] WITH NOCHECK ADD 
	CONSTRAINT [PK_Document] PRIMARY KEY  CLUSTERED 
	(
		[DocumentID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Files] WITH NOCHECK ADD 
	CONSTRAINT [PK_File] PRIMARY KEY  CLUSTERED 
	(
		[FileID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Infomation] WITH NOCHECK ADD 
	CONSTRAINT [PK_Infomation] PRIMARY KEY  CLUSTERED 
	(
		[InfoID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Job] WITH NOCHECK ADD 
	CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED 
	(
		[JobID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Meeting] WITH NOCHECK ADD 
	CONSTRAINT [PK_Meeting] PRIMARY KEY  CLUSTERED 
	(
		[MeetingID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Message] WITH NOCHECK ADD 
	CONSTRAINT [PK_Message] PRIMARY KEY  CLUSTERED 
	(
		[MessageID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Notification] WITH NOCHECK ADD 
	CONSTRAINT [PK_Notification] PRIMARY KEY  CLUSTERED 
	(
		[NotificationID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Online] WITH NOCHECK ADD 
	CONSTRAINT [PK_Online] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Organization] WITH NOCHECK ADD 
	CONSTRAINT [PK_Organization] PRIMARY KEY  CLUSTERED 
	(
		[OrganizationID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Persons] WITH NOCHECK ADD 
	CONSTRAINT [PK_Persons] PRIMARY KEY  CLUSTERED 
	(
		[PersonID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Private] WITH NOCHECK ADD 
	CONSTRAINT [PK_Private] PRIMARY KEY  CLUSTERED 
	(
		[PrivateID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Schedule] WITH NOCHECK ADD 
	CONSTRAINT [PK_Schedule] PRIMARY KEY  CLUSTERED 
	(
		[ScheduleID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[System] WITH NOCHECK ADD 
	CONSTRAINT [PK_System] PRIMARY KEY  CLUSTERED 
	(
		[SystemID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[Document] ADD 
	CONSTRAINT [FK_Document_Persons] FOREIGN KEY 
	(
		[PersonID]
	) REFERENCES [dbo].[Persons] (
		[PersonID]
	) ON DELETE CASCADE 
;

ALTER TABLE [dbo].[Files] ADD 
	CONSTRAINT [FK_Files_Persons] FOREIGN KEY 
	(
		[PersonID]
	) REFERENCES [dbo].[Persons] (
		[PersonID]
	) ON UPDATE CASCADE 
;

ALTER TABLE [dbo].[Meeting] ADD 
	CONSTRAINT [FK_Meeting_Persons] FOREIGN KEY 
	(
		[PersonID]
	) REFERENCES [dbo].[Persons] (
		[PersonID]
	) ON UPDATE CASCADE 
;

ALTER TABLE [dbo].[Message] ADD 
	CONSTRAINT [FK_Message_Persons] FOREIGN KEY 
	(
		[PersonID]
	) REFERENCES [dbo].[Persons] (
		[PersonID]
	) ON DELETE CASCADE 
;

ALTER TABLE [dbo].[Private] ADD 
	CONSTRAINT [FK_Private_Persons] FOREIGN KEY 
	(
		[PersonID]
	) REFERENCES [dbo].[Persons] (
		[PersonID]
	) ON DELETE CASCADE ;
|
CREATE Procedure [dbo].[CheckOnlineUser](@UserName NVARCHAR(50) , @UserOnline CHAR(10)  OUTPUT) AS
   SET @UserOnline = 'false'
    IF EXISTS(SELECT UserName  FROM ONLINE  WHERE UserName =@UserName)
   SET @UserOnline = 'true'
|
CREATE Procedure [dbo].[CheckOnlineUser1](@UserName NVARCHAR(50) , @UserOnline CHAR(10)  OUTPUT) AS
   SET @UserOnline = 'false'
    IF EXISTS(SELECT UserName  FROM ONLINE  WHERE UserName =@UserName)
   SET @UserOnline = 'true'
|
CREATE   Procedure [dbo].[CheckOnlineUser2](@UserName NVARCHAR(50)) AS
    DECLARE  @UserOnline CHAR(10)
    SET @UserOnline = 'false'
    IF EXISTS(SELECT UserName  FROM ONLINE  WHERE UserName =@UserName)
    SET @UserOnline = 'true'
    select @UserOnline
|
CREATE Procedure [dbo].[CheckSetUser](@Name nvarchar(50),@Password nvarchar(50),@SetUser Char(10) OUTPUT,@State Char(10) OUTPUT,@PersonID int OUTPUT) AS
    Set @SetUser = 'false'
    IF EXISTS(SELECT * FROM Persons WHERE Name=@Name AND Password=@Password)
     Set @SetUser = 'true'  
     SELECT @State =State, @PersonID =PersonID FROM  Persons WHERE Name=@Name AND Password=@Password
|
CREATE PROCEDURE [dbo].[DeleteDocument] (@DocumentID int)  AS
DELETE  FROM Document WHERE DocumentID = @DocumentID
return
|
CREATE PROCEDURE [dbo].[DeleteFile]  (@FileID int)  AS
DELETE  FROM Files WHERE FileID = @FileID
return
|
CREATE PROCEDURE [dbo].[DeleteInfoCategory]  (@InfoCategoryID int)  AS
DELETE  FROM InfoCategories WHERE InfoCategoryID = @InfoCategoryID
return
|
CREATE PROCEDURE [dbo].[DeleteInfomation] (@InfoID int)  AS
DELETE  FROM Infomation WHERE InfoID = @InfoID
return
|
CREATE PROCEDURE [dbo].[DeleteJob]  (@JobID int)  AS
DELETE  FROM Job WHERE JobID = @JobID
return
|
CREATE PROCEDURE [dbo].[DeleteMeeting]  (@MeetingID int)  AS
DELETE  FROM Meeting WHERE MeetingID = @MeetingID
return
|
CREATE PROCEDURE [dbo].[DeleteNotification]  (@NotificationID int)  AS
DELETE  FROM Notification WHERE NotificationID = @NotificationID
return
|
CREATE PROCEDURE [dbo].[DeleteOnlineUser] (@UserSN nvarchar(50)) AS
DELETE FROM Online WHERE UserSN = @UserSN
return
|
CREATE PROCEDURE [dbo].[DeleteOrganization]  (@OrganizationID int)  AS
DELETE  FROM Organization WHERE OrganizationID = @OrganizationID
return
|
CREATE PROCEDURE [dbo].[DeletePerson]  (@PersonID int)  AS
DELETE  FROM Persons WHERE PersonID = @PersonID
return
|
CREATE PROCEDURE [dbo].[DeleteSchedule]  (@ScheduleID int)  AS
DELETE  FROM Schedule WHERE ScheduleID = @ScheduleID
return
|
CREATE PROCEDURE [dbo].[DeleteSenderMessage]  (@MessageID int)  AS
DELETE  FROM Message WHERE MessageID = @MessageID
return
|
CREATE PROCEDURE [dbo].[DeleteSystem]  (@SystemID int)  AS
DELETE  FROM System WHERE SystemID = @SystemID
return
|
CREATE Procedure [dbo].[DocumentContents] (@DocumentID int) AS
    SELECT * FROM Document WHERE DocumentID=@DocumentID
    return
|
CREATE PROCEDURE [dbo].[DocumentPrompt] (@AccepterID nvarchar(120))  AS
SELECT * FROM Document WHERE AccepterID=@AccepterID  AND state=0
return
|
CREATE PROCEDURE [dbo].[FileContens] (@FileID INT) AS
SELECT dbo.Files.[Date], dbo.Files.State, dbo.Files.FileKind, dbo.Files.Title, 
      dbo.Files.Writer, dbo.Organization.OrganizationName, dbo.System.SystemName, 
      dbo.Persons.Name, dbo.Files.Contents, dbo.Files.ContentType, dbo.Files.ByteSize 
FROM dbo.Files INNER JOIN
      dbo.Organization ON 
      dbo.Files.OrganizationID = dbo.Organization.OrganizationID INNER JOIN
      dbo.System ON dbo.Files.SystemID = dbo.System.SystemID INNER JOIN
      dbo.Persons ON dbo.Files.PersonID = dbo.Persons.PersonID
WHERE FileID=@FileID
RETURN
|
CREATE PROCEDURE [dbo].[FileContents] (@FileID INT) AS