USE [Inventory]
GO

CREATE TABLE [dbo].[ClusterServers](
	[clustername] [varchar](128) NOT NULL,
	[servername] [varchar](128) NOT NULL,
	[moddt] [datetime] NULL,
	[moduser] [varchar](50) NULL,
 CONSTRAINT [PK_ClusterServers_ServerName] PRIMARY KEY CLUSTERED 
(
	[servername] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ClusterServers] ADD  CONSTRAINT [DF_ClusterServers_ModDT]  DEFAULT (getdate()) FOR [moddt]
GO

ALTER TABLE [dbo].[ClusterServers] ADD  CONSTRAINT [DF_ClusterServers_ModUser]  DEFAULT (suser_sname()) FOR [moduser]

GO


CREATE TABLE [dbo].[ServerInstances](
	[instancename] [varchar](128) NOT NULL,
	[servername] [varchar](128) NOT NULL,
	[moddt] [datetime] NULL,
	[moduser] [varchar](50) NULL,
	[status] [varchar](24) NULL,
 CONSTRAINT [PK_ServerInstances_InstanceName] PRIMARY KEY CLUSTERED 
(
	[instancename] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ServerInstances] ADD  CONSTRAINT [DF_ServerInstances_ModDT]  DEFAULT (getdate()) FOR [moddt]
GO

ALTER TABLE [dbo].[ServerInstances] ADD  CONSTRAINT [DF_ServerInstances_ModUser]  DEFAULT (suser_sname()) FOR [moduser]
GO





CREATE TABLE [dbo].[ServerDetails](
	[Servername] [varchar](128) NOT NULL,
	[Manufacturer] [varchar](256) NULL,
	[Model] [varchar](256) NULL,
	[VM] [bit] NULL,
	[TotalMemory] [varchar](256) NULL,
	[BiosDescription] [varchar](512) NULL,
	[BiosVersion] [varchar](128) NULL,
	[BiosSerial] [varchar](128) NULL,
	[OSName] [varchar](128) NULL,
	[Architecture] [varchar](12) NULL,
	[Processors] [int] NULL,
	[Cores] [int] NULL,
	[Mod_Dt] [datetime] NULL,
	[Mod_User] [varchar](255) NULL,
	[Retired] [bit] NULL,
	[Environment] [varchar](24) NULL,
	[DomainJoined] [bit] NULL,
 CONSTRAINT [PK_ServerDetails_Servername] PRIMARY KEY CLUSTERED 
(
	[Servername] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ServerDetails] ADD  CONSTRAINT [DF_ServerDetails_ModDt]  DEFAULT (getdate()) FOR [Mod_Dt]
GO

ALTER TABLE [dbo].[ServerDetails] ADD  CONSTRAINT [DF_ServerDetails_ModUser]  DEFAULT (suser_sname()) FOR [Mod_User]
GO

ALTER TABLE [dbo].[ServerDetails] ADD  CONSTRAINT [DF_ServerDetails_Retired]  DEFAULT ((0)) FOR [Retired]
GO

ALTER TABLE [dbo].[ServerDetails] ADD  CONSTRAINT [DF_ServerDetails_DomainJoined]  DEFAULT ((1)) FOR [DomainJoined]
GO





CREATE TABLE [dbo].[InstanceDetails](
	[InstanceName] [varchar](128) NOT NULL,
	[Platform] [varchar](24) NULL,
	[OSVersion] [varchar](24) NULL,
	[IsClustered] [bit] NULL,
	[Processors] [int] NULL,
	[Memory] [bigint] NULL,
	[Instance] [varchar](50) NULL,
	[ServiceAccount] [varchar](50) NULL,
	[BuildNumber] [varchar](24) NULL,
	[Edition] [varchar](50) NULL,
	[SQLVersion] [varchar](24) NULL,
	[SPLevel] [varchar](6) NULL,
	[AutoAffinity] [bit] NULL,
	[Collation] [varchar](255) NULL,
	[FullText] [bit] NULL,
	[RootPath] [varchar](255) NULL,
	[MasterDBPath] [varchar](255) NULL,
	[InstallPath] [varchar](255) NULL,
	[SharedPath] [varchar](255) NULL,
	[ErrorLogPath] [varchar](255) NULL,
	[DefaultDataPath] [varchar](255) NULL,
	[DefaultLogPath] [varchar](255) NULL,
	[BackupPath] [varchar](255) NULL,
	[Retired] [bit] NULL,
	[BackupDirectory] [varchar](256) NULL,
	[Mod_Dt] [datetime] NULL,
	[Mod_User] [varchar](255) NULL,
	[Dedicated] [bit] NULL,
	[Environment] [varchar](24) NULL,
	[LicenseOnly] [bit] NULL,
	[IPAddress] [varchar](32) NULL,
	[Port] [int] NULL,
 CONSTRAINT [PK_InstanceDetails_InstanceName] PRIMARY KEY CLUSTERED 
(
	[InstanceName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[InstanceDetails] ADD  CONSTRAINT [DF_InstanceDetails_Retired]  DEFAULT ((0)) FOR [Retired]
GO

ALTER TABLE [dbo].[InstanceDetails] ADD  CONSTRAINT [DF_InstanceDetails_ModDt]  DEFAULT (getdate()) FOR [Mod_Dt]
GO

ALTER TABLE [dbo].[InstanceDetails] ADD  CONSTRAINT [DF_InstanceDetails_ModUser]  DEFAULT (suser_sname()) FOR [Mod_User]
GO

ALTER TABLE [dbo].[InstanceDetails] ADD  CONSTRAINT [DF_InstanceDetails_LicenseOnly]  DEFAULT ((0)) FOR [LicenseOnly]
GO




CREATE TABLE [dbo].[ServerDetails_STAGE](
	[Servername] [varchar](128) NOT NULL,
	[Manufacturer] [varchar](256) NULL,
	[Model] [varchar](256) NULL,
	[VM] [bit] NULL,
	[TotalMemory] [varchar](256) NULL,
	[BiosDescription] [varchar](512) NULL,
	[BiosVersion] [varchar](128) NULL,
	[BiosSerial] [varchar](128) NULL,
	[OSName] [varchar](128) NULL,
	[Architecture] [varchar](12) NULL,
	[Processors] [int] NULL,
	[Cores] [int] NULL
) ON [PRIMARY]

GO





CREATE TABLE [dbo].[InstanceDetails_STAGE](
	[InstanceName] [varchar](128) NOT NULL,
	[Platform] [varchar](24) NULL,
	[OSVersion] [varchar](24) NULL,
	[IsClustered] [bit] NULL,
	[Processors] [int] NULL,
	[Memory] [bigint] NULL,
	[Instance] [varchar](50) NULL,
	[ServiceAccount] [varchar](50) NULL,
	[BuildNumber] [varchar](24) NULL,
	[Edition] [varchar](50) NULL,
	[SQLVersion] [varchar](24) NULL,
	[SPLevel] [varchar](6) NULL,
	[AutoAffinity] [bit] NULL,
	[Collation] [varchar](255) NULL,
	[FullText] [bit] NULL,
	[RootPath] [varchar](255) NULL,
	[MasterDBPath] [varchar](255) NULL,
	[InstallPath] [varchar](255) NULL,
	[SharedPath] [varchar](255) NULL,
	[ErrorLogPath] [varchar](255) NULL,
	[DefaultDataPath] [varchar](255) NULL,
	[DefaultLogPath] [varchar](255) NULL,
	[BackupPath] [varchar](255) NULL
) ON [PRIMARY]

GO