>-- 2011.06.16 hewbxk 5055 Created Covering index > >USE [MyDB] >GO >/****** Object: Index [IDX_File_Name_File_Seq_No] Script Date: 06/17/2011 15:09:19 ******/ >IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Log_File]') AND name = N'IDX_File_Name_File_Seq_No') >DROP INDEX [IDX_File_Name_File_Seq_No] ON [dbo].[Log_File] WITH ( ONLINE = OFF ) > > >CREATE NONCLUSTERED INDEX [IDX_File_Name_File_Seq_No] ON [dbo].[Log_File] >( > [File_Name] ASC, > [User_ID] ASC, > [Log_File_Seq_No] ASC, > [Imported_File_Name] ASC >)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, > ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] > >>
> >-- Add a column to a table > >IF NOT EXISTS ( > SELECT * > FROM sys.columns > WHERE object_id = OBJECT_ID(N'[dbo].[My_Table]') > AND name = N'New_Code' > ) >ALTER TABLE [dbo].[My_Table] >ADD [New_Code] CHAR(1) NULL >GO > >>
> >-- ******************************************************************* >-- Add table >-- ******************************************************************* > >IF NOT EXISTS ( > SELECT * > FROM sys.tables > WHERE object_id = OBJECT_ID(N'[dbo].[Contact]') > ) >CREATE TABLE [dbo].[Contact]( > [Contact_Seq_No] INT IDENTITY(1,1) NOT NULL, > [Parent_Seq_No] INT NOT NULL, > [Contact_Name] VARCHAR(60) NULL, > [Contact_Telephone] VARCHAR(15) NULL, > [Contact_Telephone_Extension] VARCHAR(15) NULL, > [Unique_Batch_ID] INT NULL, >CONSTRAINT [PK_Contact] PRIMARY KEY NONCLUSTERED >( > [Contact_Seq_No] ASC >)WITH ( > PAD_INDEX = OFF, > STATISTICS_NORECOMPUTE = OFF, > IGNORE_DUP_KEY = OFF, > ALLOW_ROW_LOCKS = ON, > ALLOW_PAGE_LOCKS = ON > ) ON [DATA] >) ON [DATA] > >GO > >SET ANSI_PADDING OFF >GO > >IF EXISTS ( > SELECT * > FROM sys.tables > WHERE object_id = OBJECT_ID(N'[dbo].[Contact]') > ) > IF NOT EXISTS ( > SELECT * > FROM sys.objects > WHERE name = 'FK_Contact_Parent_Seq_No_Parent_Parent_Seq_No' > AND parent_object_id = OBJECT_ID('[dbo].[Contact]') > ) > ALTER TABLE [dbo].[Contact] WITH CHECK > ADD CONSTRAINT [FK_Contact_Parent_Seq_No_Parent_Parent_Seq_No] FOREIGN KEY([Claim_Seq_No]) > REFERENCES [dbo].[Parent] ([Parent_Seq_No]) >GO > >IF EXISTS ( > SELECT * > FROM sys.tables > WHERE object_id = OBJECT_ID(N'[dbo].[Contact]') > ) >ALTER TABLE [dbo].[Contact] >CHECK CONSTRAINT [FK_Contact_Parent_Seq_No_Parent_Parent_Seq_No] >GO > > >>