USE [ToolTrac] GO /****** Object: Table [dbo].[ActivityLog] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ActivityLog]( [EntryID] [int] IDENTITY(1,1) NOT NULL, [StationID] [nvarchar](50) NOT NULL, [Zone] [nvarchar](50) NOT NULL, [TimeStamp] [datetime] NOT NULL, [ActionCode] [char](1) NOT NULL, [EmployeeID] [nvarchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [WorkGroup] [nvarchar](50) NULL, [Disposition] [int] NULL, [ToolID] [nvarchar](50) NOT NULL, [TagID] [nvarchar](50) NOT NULL, [Remarks] [nvarchar](250) NULL, [Inspected] [tinyint] NULL, [Lanyard] [tinyint] NULL, [Quantity] [int] NULL, [DateIn] [datetime] NULL, [DateOut] [datetime] NULL, [Contaminated] [tinyint] NULL, [MonitorID] [nvarchar](50) NULL, [MonitorNameFirst] [nvarchar](50) NULL, [MonitorNameLast] [nvarchar](50) NULL, [PCWO] [nvarchar](20) NULL, [EmployeeIDOut] [nvarchar](20) NULL, CONSTRAINT [PK_ActivityLog1] PRIMARY KEY CLUSTERED ( [EntryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[DeviceSubType] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DeviceSubType]( [SubTypeID] [int] IDENTITY(1,1) NOT NULL, [DeviceType] [nvarchar](50) NOT NULL, [DeviceSubType] [nvarchar](50) NOT NULL, [Active] [tinyint] NOT NULL, CONSTRAINT [PK_DeviceSubType] PRIMARY KEY CLUSTERED ( [SubTypeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DeviceTypes] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DeviceTypes]( [DeviceType] [nvarchar](50) NOT NULL, [Active] [tinyint] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[Employees] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Employees]( [EmployeeID] [varchar](250) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [TagID] [nvarchar](250) NULL, [PhotoBLOB] [varchar](max) NULL, [EmployeeSecurity] [int] NULL, [Password] [nvarchar](500) NULL, [EMailAddr] [nvarchar](100) NULL, CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [EmployeeID] 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] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[InventoryDetail] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[InventoryDetail]( [InventoryID] [nvarchar](50) NOT NULL, [TagID] [nvarchar](24) NOT NULL, [ItemID] [nvarchar](50) NOT NULL, [RowTransferred] [tinyint] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[InventoryMaster] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[InventoryMaster]( [InventoryID] [nvarchar](50) NOT NULL, [InventoryDate] [datetime] NOT NULL, [InventoriedBy] [nvarchar](50) NULL, [RowTransferred] [tinyint] NULL, [Location] [nvarchar](50) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[PhotoInfo] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[PhotoInfo]( [PhotoID] [int] IDENTITY(1,1) NOT NULL, [ToolID] [nvarchar](50) NOT NULL, [PhotoLocation] [nvarchar](200) NOT NULL, [FileName] [nvarchar](50) NOT NULL, [PhotoBLOB] [varchar](max) NULL, CONSTRAINT [PK_PhotoInfo] PRIMARY KEY CLUSTERED ( [PhotoID] 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] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Remarks] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Remarks]( [RemarkText] [nvarchar](250) NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[StoreLocations] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[StoreLocations]( [StoreID] [int] IDENTITY(1,1) NOT NULL, [StoreParentID] [int] NOT NULL, [StoreName] [nvarchar](50) NOT NULL, [Active] [tinyint] NOT NULL, [Level] [int] NULL, CONSTRAINT [PK_StoreLocations] PRIMARY KEY CLUSTERED ( [StoreID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[TagAssoc] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TagAssoc]( [Tag_Id] [char](24) NOT NULL, [AssetType] [char](1) NOT NULL, [AssetId] [varchar](20) NOT NULL, CONSTRAINT [PK_TagAssoc] PRIMARY KEY CLUSTERED ( [Tag_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] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[ToolDescriptionList] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ToolDescriptionList]( [ToolDescription] [nvarchar](150) NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[Tools] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Tools]( [_ID] [int] IDENTITY(1,1) NOT NULL, [ToolID] [nvarchar](50) NOT NULL, [TagID] [nvarchar](50) NULL, [Description] [nvarchar](100) NOT NULL, [SerialNumber] [nvarchar](50) NULL, [Status] [nvarchar](50) NULL, [CurrentZone] [nvarchar](50) NULL, [CurrentWorkGroup] [nvarchar](50) NULL, [CurrentEmployeeName] [nvarchar](100) NULL, [Quantity] [int] NULL, [CurrentEmployeeID] [nvarchar](50) NULL, [LastActivityDateTime] [datetime] NULL, [Inspected] [tinyint] NULL, [Lanyard] [tinyint] NULL, [CatID] [nvarchar](50) NULL, [MFRName] [nvarchar](50) NULL, [MFRPart] [nvarchar](50) NULL, [LastCalDate] [datetime] NULL, [CalDueDate] [datetime] NULL, [UTCNumber] [nvarchar](50) NULL, [InServiceDate] [datetime] NULL, [OutServiceDate] [datetime] NULL, [MaxComRating] [nvarchar](50) NULL, [MaxComCategory] [nvarchar](50) NULL, [SAMM_ID] [nvarchar](50) NULL, [SAMM_Status] [nvarchar](50) NULL, [ShelfLife] [nvarchar](50) NULL, [RecordLastUpdated] [datetime] NULL, [ExpectedReturnDate] [datetime] NULL, [Disposition] [int] NULL, [Contaminated] [tinyint] NULL, [CurrentFMEMonitorID] [nvarchar](50) NULL, [CurrentFMEMonitorName] [nvarchar](100) NULL, [Remarks] [nvarchar](2000) NULL, [Model] [nvarchar](50) NULL, [cost] [decimal](10, 2) NULL, [CalInterval] [int] NULL, [CalIntervalUnit] [nvarchar](50) NULL, [CalLocation] [nvarchar](50) NULL, [Capacity] [nvarchar](50) NULL, [DedicatedUse] [tinyint] NULL, [DedicatedUseDesc] [nvarchar](50) NULL, [ToolRemoved] [tinyint] NULL, [BaseLocation] [nvarchar](100) NULL, [Section] [nvarchar](100) NULL, [SubSection] [nvarchar](100) NULL, [Bin] [nvarchar](100) NULL, [Consumable] [tinyint] NULL, [SubCategory] [nvarchar](100) NULL, CONSTRAINT [PK_Tools] PRIMARY KEY CLUSTERED ( [ToolID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[ToolStatus] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ToolStatus]( [TSToolID] [nvarchar](50) NOT NULL, [TimeStamp] [datetime] NULL, [TSEmployeeID] [nvarchar](50) NULL, [TSCurrentStatus] [nvarchar](1) NULL, [TSCurrentZone] [nvarchar](50) NULL, [TSCurrentWorkGroup] [nvarchar](50) NULL, [TSQuantity] [int] NULL, [TSCurrentWorkOrder] [nvarchar](50) NULL, [TSExpectedReturnDate] [datetime] NULL, CONSTRAINT [PK_ToolStatus] PRIMARY KEY CLUSTERED ( [TSToolID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[UserActivity] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[UserActivity]( [UserName] [nvarchar](100) NOT NULL, [UserID] [nvarchar](50) NULL, [ActionCode] [nvarchar](10) NOT NULL, [TimeStamp] [datetime] NOT NULL, [Zone] [varchar](50) NULL, [WorkGroup] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[WorkGroups] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[WorkGroups]( [WorkGroup] [nvarchar](50) NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[Zones] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Zones]( [Zone] [nvarchar](50) NOT NULL, [Active] [tinyint] NULL ) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [idxActivityLogToolID] ******/ CREATE NONCLUSTERED INDEX [idxActivityLogToolID] ON [dbo].[ActivityLog] ( [ToolID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [DeviceSubType_DeviceType] ******/ CREATE NONCLUSTERED INDEX [DeviceSubType_DeviceType] ON [dbo].[DeviceSubType] ( [DeviceType] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE [dbo].[DeviceSubType] ADD CONSTRAINT [DF_DeviceSubType_Active] DEFAULT ((0)) FOR [Active] GO ALTER TABLE [dbo].[InventoryDetail] ADD CONSTRAINT [DF_InventoryDetail_RowTransferred] DEFAULT ((0)) FOR [RowTransferred] GO ALTER TABLE [dbo].[InventoryMaster] ADD CONSTRAINT [DF_InventoryMaster_RowTransferred] DEFAULT ((0)) FOR [RowTransferred] GO ALTER TABLE [dbo].[Zones] ADD CONSTRAINT [DF_Zones_Zone] DEFAULT ((1)) FOR [Zone] GO /****** Object: StoredProcedure [dbo].[BackUpDB] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= CREATE PROCEDURE [dbo].[BackUpDB] -- Add the parameters for the stored procedure here @FilePath varchar(200) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BACKUP DATABASE ToolTrac to disk = @filepath END GO