Chaks

USE [Shire]GO/****** Object:  StoredProcedure [dbo].[CreateTables]    Script Date: 02/02/2008 23:08:33 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:		<Author,,Name>-- Create date: <Create Date,,>-- Description:	<Description,,>-- =============================================ALTER PROCEDURE [dbo].[CreateTables]	-- Add the parameters for the stored procedure here	@database_name varchar(MAX)ASBEGIN	DECLARE @name varchar(MAX)	DECLARE @courses_prefix varchar(100)	DECLARE @subjects_prefix varchar(100)	DECLARE @tutors_prefix varchar(100)	DECLARE @students_prefix varchar(100)	DECLARE @tutorsubjects_prefix varchar(100)	DECLARE @studentsubjects_prefix varchar(100)		SET @name=(SELECT DepartmentGUID from edu_departments where DepartmentId=@database_name);	SET @courses_prefix='['+@name + ']'+'.[dbo].[courses]'	SET @subjects_prefix='['+@name + ']'+'.[dbo].[subjects]'	SET @tutors_prefix='['+@name + ']'+'.[dbo].[tutors]'	SET @students_prefix='['+@name + ']'+'.[dbo].[students]'	SET @tutorsubjects_prefix='['+@name + ']'+'.[dbo].[tutorsubjects]'	SET @studentsubjects_prefix='['+@name + ']'+'.[dbo].[studentsubjects]'	--Courses	exec('CREATE TABLE '+@courses_prefix+'(		[Id] [varchar](100) NOT NULL,		[Name] [varchar](300) NOT NULL,		[Contact] [varchar](100) NULL,		[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_courses_Timestamp]  DEFAULT (getdate()),	 CONSTRAINT [PK_courses_1] PRIMARY KEY CLUSTERED 	(		[Id] ASC	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]	) ON [PRIMARY]')	--Subjects	exec('CREATE TABLE '+@subjects_prefix+'(		[Id] [varchar](100) NOT NULL,		[Name] [varchar](300) NOT NULL,		[Contact] [varchar](100) NULL,		[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_subjects_Timestamp]  DEFAULT (getdate()),	 CONSTRAINT [PK_subjects_1] PRIMARY KEY CLUSTERED 	(		[Id] ASC	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]		) ON [PRIMARY]')	--Tutors	exec('CREATE TABLE '+@tutors_prefix+'(	[UserName] [varchar](100) NOT NULL,	[Name] [varchar](300) NOT NULL,	[Contact] [varchar](100) NULL,	[Photo] [image] NULL,	[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_tutors_Timestamp]  DEFAULT (getdate()),	 CONSTRAINT [PK_tutors_1] PRIMARY KEY CLUSTERED 	(		[UserName] ASC	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]')	--Students	exec('CREATE TABLE '+@students_prefix+'(	[UserName] [varchar](100) NOT NULL,	[Name] [varchar](300) NOT NULL,	[Course] [varchar](100) NOT NULL,	[Contact] [varchar](100) NULL,	[Photo] [image] NULL,	[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_students_Timestamp]  DEFAULT (getdate()),	 CONSTRAINT [PK_students_1] PRIMARY KEY CLUSTERED 	(		[UserName] ASC	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]')	--Tutorsubjects	exec('CREATE TABLE '+@tutorsubjects_prefix+'(		[Key] [bigint] IDENTITY(1,1) NOT NULL,		[Tutor] [varchar](100) NOT NULL,		[Subject] [varchar](100) NOT NULL,		[Course] [varchar](100) NOT NULL,		[SessionName] [varchar](300) NOT NULL,		[SessionDate] [varchar](100) NOT NULL,		[SessionTime] [varchar](100) NOT NULL,		[SessionVenue] [varchar](300) NOT NULL,		[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_tutorsubjects_Timestamp]  DEFAULT (getdate()),	 CONSTRAINT [PK_tutorsubjects] PRIMARY KEY CLUSTERED 	(		[Key] ASC	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],	CONSTRAINT [IX_tutorsubjects] UNIQUE NONCLUSTERED 	(		[Tutor] ASC,		[Subject] ASC,		[SessionName] ASC	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]	) ON [PRIMARY]')	--Studentsubjects	exec('CREATE TABLE '+@studentsubjects_prefix+'(		[Key] [bigint] IDENTITY(1,1) NOT NULL,		[Student] [varchar](100) NOT NULL,		[Session] [bigint] NOT NULL,		[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_studentsubjects_Timestamp]  DEFAULT (getdate()),	 CONSTRAINT [PK_studentsubjects] PRIMARY KEY CLUSTERED 	(		[Key] ASC	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]	) ON [PRIMARY]')END