course.sql

机械工业出版社《SQL Server应用开发实例教程》源代码

/*
   2001年4月10日 10:38:05
   User: 
   Server: JERRY
   Database: teaching_ Database
   Application: MS SQLEM - Data Tools
*/

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.course
	DROP CONSTRAINT DF_course_course_xuefen
GO
CREATE TABLE dbo.Tmp_course
	(
	course_name nvarchar(50) NOT NULL,
	course_no nchar(10) NOT NULL,
	course_xuefen tinyint NULL,
	department nvarchar(50) NULL
	)  ON [PRIMARY]
GO
DECLARE @v sql_variant 
SET @v = N'课程表,有课程名,课程编号,学分,开课系字段'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Tmp_course', NULL, NULL
GO
DECLARE @v sql_variant 
SET @v = N'课程名'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Tmp_course', N'column', N'course_name'
GO
DECLARE @v sql_variant 
SET @v = N'课程号'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Tmp_course', N'column', N'course_no'
GO
DECLARE @v sql_variant 
SET @v = N'学分'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Tmp_course', N'column', N'course_xuefen'
GO
DECLARE @v sql_variant 
SET @v = N'开课系'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Tmp_course', N'column', N'department'
GO
ALTER TABLE dbo.Tmp_course ADD CONSTRAINT
	DF_course_course_xuefen DEFAULT (0) FOR course_xuefen
GO
IF EXISTS(SELECT * FROM dbo.course)
	 EXEC('INSERT INTO dbo.Tmp_course (course_name, course_no, course_xuefen, department)
		SELECT course_name, course_no, course_xuefen, department FROM dbo.course TABLOCKX')
GO
ALTER TABLE dbo.Grade
	DROP CONSTRAINT FK_Grade_course
GO
ALTER TABLE dbo.course_teaching
	DROP CONSTRAINT FK_course_teaching_course
GO
DROP TABLE dbo.course
GO
EXECUTE sp_rename N'dbo.Tmp_course', N'course', 'OBJECT'
GO
ALTER TABLE dbo.course ADD CONSTRAINT
	PK_course PRIMARY KEY CLUSTERED 
	(
	course_no
	) ON [PRIMARY]

GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.course_teaching WITH NOCHECK ADD CONSTRAINT
	FK_course_teaching_course FOREIGN KEY
	(
	course_no
	) REFERENCES dbo.course
	(
	course_no
	)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Grade WITH NOCHECK ADD CONSTRAINT
	FK_Grade_course FOREIGN KEY
	(
	course_no
	) REFERENCES dbo.course
	(
	course_no
	)
GO
COMMIT