if(select count(*) from information_schema.tables where table_name='luporgcodes')>0 begin drop table luporgcodes end GO CREATE TABLE luporgcodes ( orgcode varchar(50) default NULL, orgid int default NULL) GO if(select count(*) from information_schema.tables where table_name='luppriority')>0 begin drop table luppriority end GO CREATE TABLE luppriority ( PriorityID int, Name varchar(50) default NULL, PRIMARY KEY (PriorityID)) GO INSERT INTO luppriority (PriorityID,Name) VALUES (1,'Critical') GO INSERT INTO luppriority (PriorityID,Name) VALUES (2,'High') GO INSERT INTO luppriority (PriorityID,Name) VALUES (3,'Normal') GO INSERT INTO luppriority (PriorityID,Name) VALUES (4,'Low') GO INSERT INTO luppriority (PriorityID,Name) VALUES (5,'Not decided') GO if(select count(*) from information_schema.tables where table_name='lupstatus')>0 begin drop table lupstatus end GO CREATE TABLE lupstatus ( StatusID int default NULL, Name varchar(50) default NULL, PRIMARY KEY (StatusID) ) GO INSERT INTO lupstatus (StatusID,Name) VALUES (1,'Open') GO INSERT INTO lupstatus (StatusID,Name) VALUES (2,'Closed') GO INSERT INTO lupstatus (StatusID,Name) VALUES (3,'In Test') GO if(select count(*) from information_schema.tables where table_name='tblcategory')>0 begin drop table tblcategory end GO CREATE TABLE tblcategory ( TEST int default NULL, PRIMARY KEY (TEST) ) GO if(select count(*) from information_schema.tables where table_name='tblclient')>0 begin drop table tblclient end GO CREATE TABLE tblclient ( orgid int default NULL, Name varchar(50) default NULL, Address1 varchar(50) default NULL, Address2 varchar(50) default NULL, Address3 varchar(50) default NULL, City varchar(50) default NULL, Country varchar(50) default NULL, Email varchar(50) default NULL, Phone varchar(50) default NULL, Fax varchar(50) default NULL, Website varchar(200) default NULL, PRIMARY KEY (orgid) ) GO if(select count(*) from information_schema.tables where table_name='tblcrs')>0 begin drop table tblcrs end GO CREATE TABLE tblcrs ( crid int default NULL, projectid int default NULL, requestdate datetime, requeststaffid int default NULL, priorityid int default NULL, scope bit default NULL, description text default NULL, justify text default NULL, impactofnot text default NULL, impactcost text default NULL, impactschedule text default NULL, impactconfig text default NULL, alternatives text default NULL, recommend text default NULL, decisionid int default NULL, decisiondate datetime default NULL, decisionstaffid int default NULL, statusid int default NULL, category varchar(255) default NULL ) GO if(select count(*) from information_schema.tables where table_name='tblissues')>0 begin drop table tblissues end GO CREATE TABLE tblissues ( IssueNo int default NULL, ProjectID int default NULL, Status int default NULL, RaisedByID int default NULL, Date_Raised datetime default NULL, Issue_Description text, Impact_Activity text, Date_Resolved datetime default NULL, Category varchar(50) default NULL, Priorityid int default NULL, Call_No varchar(50) default NULL, UAT_No varchar(50) default NULL, Owner int default NULL, DateDue datetime default NULL, Progress int default NULL, PRIMARY KEY (IssueNo) ) GO if(select count(*) from information_schema.tables where table_name='tbllocked')>0 begin drop table tbllocked end GO CREATE TABLE tbllocked ( tablename varchar(30) default NULL, fieldname varchar(30) default NULL, fieldid smallint default NULL, expiry datetime default NULL, staffid smallint default NULL ) GO if(select count(*) from information_schema.tables where table_name='tblorg')>0 begin drop table tblorg end GO CREATE TABLE tblorg ( orgid int default NULL, Name varchar(50) default NULL, Address1 varchar(50) default NULL, Address2 varchar(50) default NULL, Address3 varchar(50) default NULL, City varchar(50) default NULL, Country varchar(50) default NULL, Email varchar(50) default NULL, Phone varchar(50) default NULL, Fax varchar(50) default NULL, Website varchar(200) default NULL ) GO if(select count(*) from information_schema.tables where table_name='tblprojectstaff')>0 begin drop table tblprojectstaff end GO CREATE TABLE tblprojectstaff ( projectstaffid int default NULL, staffid int default NULL, projectid int default NULL, title varchar(255) default NULL, expense decimal(19,4) default NULL, budget decimal(19,4) default NULL ) GO if(select count(*) from information_schema.tables where table_name='tblreleases')>0 begin drop table tblreleases end GO CREATE TABLE tblreleases ( ReleaseID int default NULL, ProjectID int default NULL, ReleaseName varchar(50) default NULL, ReleaseDate varchar(50) default NULL, Description varchar(4000) default NULL, status int default NULL, staffid int default NULL, progress int default NULL, priorityid int default NULL, crid int default NULL, category varchar(50) default NULL ) GO if(select count(*) from information_schema.tables where table_name='tblreleasestatus')>0 begin drop table tblreleasestatus end GO CREATE TABLE tblreleasestatus ( name varchar(255) default NULL, description varchar(255) default NULL, statusid int default NULL ) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('New','New',1) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('Open','Open releases',2) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('Design','Currently in design process',3) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('Develop','Currently in development phase',4) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('In Test','Currently in QA/ Testing environment',5) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('UAT','Currently in User Acceptance Testing',6) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('Production','Currently in Production',7) GO if(select count(*) from information_schema.tables where table_name='tblstaff')>0 begin drop table tblstaff end GO CREATE TABLE tblstaff ( StaffID int default NULL, Initial varchar(50) default NULL, Name varchar(50) default NULL, orgid int default NULL, Phone2 varchar(50) default NULL, Email varchar(50) default NULL, username varchar(50) default NULL, password varchar(50) default NULL, isadmin bit default NULL, isglobaladmin bit default NULL, First varchar(50) default NULL, Last varchar(50) default NULL, position varchar(50) default NULL, department varchar(50) default NULL, info varchar(1000) default NULL, address1 varchar(50) default NULL, address2 varchar(50) default NULL, address3 varchar(50) default NULL, town varchar(50) default NULL, Ext varchar(50) default NULL, Fax varchar(50) default NULL, inactive bit default NULL, Phone varchar(50) default NULL, PRIMARY KEY (StaffID) ) GO if(select count(*) from information_schema.tables where table_name='tbltaskresources')>0 begin drop table tbltaskresources end GO CREATE TABLE tbltaskresources ( taskresourceid int default NULL, resourceid int default NULL, projectid int default NULL, taskid int default NULL, hours int default NULL, description varchar(255) default NULL ) GO if(select count(*) from information_schema.tables where table_name='tbltasks')>0 begin drop table tbltasks end GO CREATE TABLE tbltasks ( description varchar(4000) default NULL, projectid int default NULL, startdate datetime default NULL, enddate datetime default NULL, taskid int default NULL, taskstatus int default NULL, status int default NULL, priorityid int default NULL, category varchar(50) default NULL ) GO if(select count(*) from information_schema.tables where table_name='tbltime')>0 begin drop table tbltime end GO CREATE TABLE tbltime ( timeid int default NULL, projectid int default NULL, issueid int default NULL, start datetime default NULL, [end] datetime default NULL, worktypeid int default NULL ) GO if(select count(*) from information_schema.tables where table_name='tblfiles')>0 begin drop table tblfiles end GO CREATE TABLE tblfiles( filename varchar(500) default NULL, projectid int default NULL ) GO if(select count(*) from information_schema.tables where table_name='tblattach')>0 begin drop table tblattach end GO CREATE TABLE tblAttach( filename varchar(200) default NULL, projectid int default NULL, tablename nchar(20) default NULL, field nchar(20) default NULL, fieldid int default NULL ) GO if(select count(*) from information_schema.tables where table_name='tblhistory')>0 begin drop table tblhistory end GO CREATE TABLE tblhistory ( historyid int default NULL, historytype int default NULL, projectid int default NULL, description varchar(4000) default NULL, date datetime default NULL, link varchar(255) default NULL ) GO if(select count(*) from information_schema.tables where table_name='tblprojects')>0 begin drop table tblprojects end GO CREATE TABLE tblprojects ( ProjectID int default NULL, Name varchar(50) default NULL, Start datetime default NULL, Deadline datetime default NULL, orgid int default NULL, Contact varchar(50) default NULL, budget decimal(19,4) default NULL, PRIMARY KEY (ProjectID) ) GO