SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE [dbo].[Sample]( [CertificateID] [bigint] IDENTITY(1,1) NOT NULL, [ClientCertificateID] [nvarchar](50) NULL, [LabSampleID] [nvarchar](50) NULL, [DateReceived] [smalldatetime] NULL, [DateReported] [smalldatetime] NULL, [RequesterID] [int] NULL, [OrganizationID] [int] NULL, [OwnerID] [int] NULL, [ClientIndividualID] [nvarchar](50) NULL, [ClientOtherIndividualID] [nvarchar](200) NULL, [ClientSampleID] [nvarchar](50) NULL, [ClientIndividualName] [nvarchar](max) NULL, [PhotoPath] [nvarchar](max) NULL, [Photo] [varbinary](max) NULL, [SampleType] [nvarchar](50) NULL, [NoOfSamples] [int] NULL, [Breed] [nvarchar](50) NULL, [Species] [nvarchar](50) NULL, [Colour] [nvarchar](50) NULL, [Nationality] [nvarchar](50) NULL, [DateOfBirth] [nvarchar](50) NULL, [Gender] [nvarchar](50) NULL, [Collectedby] [nvarchar](50) NULL, [DateCollected] [smalldatetime] NULL, [TestCode] [nvarchar](50) NULL, [FatherLabSampleID] [nvarchar](50) NULL, [MotherLabSampleID] [nvarchar](50) NULL, [FatherName] [nvarchar](50) NULL, [MotherName] [nvarchar](50) NULL, [ClientFatherIndividualID] [nvarchar](50) NULL, [ClientMotherIndividualID] [nvarchar](50) NULL, [Urgency] [nvarchar](50) NULL, [IsAcceptable] [bit] NULL, [TestResult] [nvarchar](max) NULL, [Remarks] [nvarchar](max) NULL, [Notes] [nvarchar](max) NULL, [SampleLocationID] [int] NULL, [SampleShelfID] [nvarchar](50) NULL, [SampleBoxID] [nvarchar](50) NULL, [OutputLocationID] [int] NULL, [OutputShelfID] [nvarchar](50) NULL, [OutputBoxID] [nvarchar](50) NULL, [IsLock] [bit] NOT NULL, [IsActive] [bit] NOT NULL, [Createdby] [uniqueidentifier] NOT NULL, [DateCreated] [smalldatetime] NOT NULL, [LastModifiedby] [uniqueidentifier] NULL, [LastModifiedDate] [smalldatetime] NULL, [Deletedby] [uniqueidentifier] NULL, [DateDeleted] [smalldatetime] NULL, [LastLockedby] [uniqueidentifier] NULL, [LastDateLocked] [smalldatetime] NULL, [LastUnlockReason] [nvarchar](max) NULL, [Concurrency] [timestamp] NOT NULL, [IsDoNotUseAsAParent] [bit] NOT NULL, [IsReferenceIndividual] [bit] NOT NULL, [RequesterUID] [uniqueidentifier] NULL, [OrganizationUID] [uniqueidentifier] NULL, [OwnerUID] [uniqueidentifier] NULL, [IsPaid] [bit] NOT NULL, [WorkOrderId] [nvarchar](50) NULL, [SubSpecies] [nvarchar](50) NULL, CONSTRAINT [PK_Sample] PRIMARY KEY CLUSTERED ( [CertificateID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_IsLock] DEFAULT ((0)) FOR [IsLock] GO ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_IsActive_1] DEFAULT ((1)) FOR [IsActive] GO ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_DateCreated_1] DEFAULT (getdate()) FOR [DateCreated] GO ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_IsDoNotUseAsAParent] DEFAULT ((0)) FOR [IsDoNotUseAsAParent] GO ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_IsReferenceIndividual] DEFAULT ((0)) FOR [IsReferenceIndividual] GO ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_IsPaid] DEFAULT ((0)) FOR [IsPaid] GO ALTER TABLE [dbo].[Sample] WITH CHECK ADD CONSTRAINT [FK_Sample_ref_SenderOwnerDoctorOrganization] FOREIGN KEY([RequesterID]) REFERENCES [dbo].[ref_SenderOwnerDoctorOrganization] ([SenderOwnerDoctorOrganizationID]) GO ALTER TABLE [dbo].[Sample] CHECK CONSTRAINT [FK_Sample_ref_SenderOwnerDoctorOrganization] GO ALTER TABLE [dbo].[Sample] WITH CHECK ADD CONSTRAINT [FK_Sample_ref_SenderOwnerVet] FOREIGN KEY([OwnerID]) REFERENCES [dbo].[ref_SenderOwnerDoctorOrganization] ([SenderOwnerDoctorOrganizationID]) GO ALTER TABLE [dbo].[Sample] CHECK CONSTRAINT [FK_Sample_ref_SenderOwnerVet] GO ALTER TABLE [dbo].[Sample] WITH CHECK ADD CONSTRAINT [FK_Sample_ref_Test] FOREIGN KEY([TestCode]) REFERENCES [dbo].[ref_Test] ([TestCode]) ON UPDATE CASCADE GO ALTER TABLE [dbo].[Sample] CHECK CONSTRAINT [FK_Sample_ref_Test] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1=Normal; 2=Urgent; 3=Very Urgent' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Sample', @level2type=N'COLUMN',@level2name=N'Urgency' GO CREATE TABLE [dbo].[ref_SenderOwnerDoctorOrganization]( [SenderOwnerDoctorOrganizationID] [int] IDENTITY(1,1) NOT NULL, [SenderOwnerDoctorOrganizationName] [nvarchar](50) NOT NULL, [DefaultPosition] [varchar](50) NULL, [DefaultAddress] [varchar](max) NULL, [DefaultTelNo] [varchar](50) NULL, [DefaultFaxNo] [varchar](50) NULL, [DefaultEmail] [varchar](50) NULL, [IsActive] [bit] NOT NULL, [Createdby] [uniqueidentifier] NOT NULL, [DateCreated] [smalldatetime] NOT NULL, [LastModifiedby] [uniqueidentifier] NULL, [LastModifiedDate] [smalldatetime] NULL, [Deletedby] [uniqueidentifier] NULL, [DateDeleted] [smalldatetime] NULL, CONSTRAINT [PK_ref_Sender] PRIMARY KEY CLUSTERED ( [SenderOwnerDoctorOrganizationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[ref_SenderOwnerDoctorOrganization] ADD CONSTRAINT [DF_ref_Sender_IsActive] DEFAULT ((1)) FOR [IsActive] GO ALTER TABLE [dbo].[ref_SenderOwnerDoctorOrganization] ADD CONSTRAINT [DF_ref_Sender_DateCreated] DEFAULT (getdate()) FOR [DateCreated] GO CREATE TABLE [dbo].[CertificateVerificationResult]( [CertificateID] [bigint] NOT NULL, [TestResult] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SELECT TOP (100) PERCENT dbo.Sample.LabSampleID, dbo.Sample.ClientIndividualName, dbo.Sample.CertificateID, dbo.Sample.DateReceived, dbo.Sample.RequesterID, dbo.Sample.OrganizationID, dbo.Sample.OwnerID, dbo.Sample.ClientIndividualID, dbo.Sample.ClientSampleID, dbo.Sample.SampleType, dbo.Sample.Species, dbo.Sample.DateOfBirth, dbo.Sample.Gender, dbo.Sample.TestCode, dbo.Sample.FatherLabSampleID, dbo.Sample.MotherLabSampleID, dbo.Sample.FatherName, dbo.Sample.MotherName, dbo.Sample.ClientFatherIndividualID, dbo.Sample.ClientMotherIndividualID, dbo.CertificateVerificationResult.TestResult, dbo.Sample.Remarks, dbo.Sample.PhotoPath, dbo.Sample.Photo, dbo.Sample.DateReported, dbo.Sample.Concurrency, dbo.Sample.IsDoNotUseAsAParent, dbo.Sample.Breed, dbo.Sample.IsReferenceIndividual, dbo.Sample.ClientCertificateID, dbo.Sample.IsPaid, dbo.Sample.Notes, dbo.Sample.ClientOtherIndividualID, dbo.ref_SenderOwnerDoctorOrganization.SenderOwnerDoctorOrganizationName AS Owner, dbo.Sample.WorkOrderId, dbo.Sample.SubSpecies FROM dbo.Sample LEFT OUTER JOIN dbo.ref_SenderOwnerDoctorOrganization ON dbo.Sample.OwnerID = dbo.ref_SenderOwnerDoctorOrganization.SenderOwnerDoctorOrganizationID LEFT OUTER JOIN dbo.CertificateVerificationResult ON dbo.Sample.CertificateID = dbo.CertificateVerificationResult.CertificateID WHERE (dbo.Sample.IsActive = 1)