>>>-- Declare temp variable to fetch records into >>>> DECLARE @ColumnName varchar(128) >>>> DECLARE @ColumnType varchar(128) >>>> DECLARE @ColumnLength smallint >>>> DECLARE @ColumnNullable int >>>> DECLARE @ColumnCollation sysname >>>> DECLARE @ColumnPrecision tinyint >>>> DECLARE @ColumnScale tinyint >>>> >>>> -- Declare variable to build statements >>>> DECLARE @CreateStatement varchar(max) >>>> DECLARE @ListOfFields varchar(max) >>>> SET @ListOfFields = '' >>>>>>>
USE [BPOptical] >>>>GO >>>> >>>>/****** Object: Table [dbo].[cltrials] Script Date: 08/09/2015 11:00:22 AM ******/ >>>>SET ANSI_NULLS ON >>>>GO >>>> >>>>SET QUOTED_IDENTIFIER ON >>>>GO >>>> >>>>SET ANSI_PADDING ON >>>>GO >>>> >>>>CREATE TABLE [dbo].[cltrials]( >>>> [itrialid] [char](16) NOT NULL, >>>> [iexamid] [char](16) NOT NULL, >>>> [mnotesod] [text] NULL, >>>> [mnotesos] [text] NULL, >>>> [mnotes] [text] NULL, >>>> [ibasecurveod] [char](16) NULL, >>>> [ibasecurveos] [char](16) NULL, >>>> [idiameterod] [char](16) NULL, >>>> [idiameteros] [char](16) NULL, >>>> [isphereod] [char](16) NULL, >>>> [isphereos] [char](16) NULL, >>>> [itypeidod] [char](16) NULL, >>>> [itypeidos] [char](16) NULL, >>>> [nkeradiaod1] [numeric](4, 2) NULL, >>>> [nkeradiaos1] [numeric](4, 2) NULL, >>>> [nkeradiaod2] [numeric](4, 2) NULL, >>>> [nkeradiaos2] [numeric](4, 2) NULL, >>>> [nkeradiaod3] [numeric](4, 2) NULL, >>>> [nkeradiaos3] [numeric](4, 2) NULL, >>>> [nkeraradod1] [numeric](4, 2) NULL, >>>> [nkerarados1] [numeric](4, 2) NULL, >>>> [nkeraradod2] [numeric](4, 2) NULL, >>>> [nkerarados2] [numeric](4, 2) NULL, >>>> [nkeraradod3] [numeric](4, 2) NULL, >>>> [nkerarados3] [numeric](4, 2) NULL, >>>> [ckerapowerod] [char](6) NULL, >>>> [ckerapoweros] [char](6) NULL, >>>> [cdesc] [varchar](50) NOT NULL, >>>> [lfinal] [bit] NULL, >>>> [icylod] [char](16) NULL, >>>> [icylos] [char](16) NULL, >>>> [iaxisod] [int] NULL, >>>> [iaxisos] [int] NULL, >>>> [iaddod] [char](16) NULL, >>>> [iaddos] [char](16) NULL, >>>> [ctintod] [char](20) NULL, >>>> [ctintos] [char](20) NULL, >>>> [imanuidod] [char](16) NULL, >>>> [imanuidos] [char](16) NULL, >>>> [ldailywearod] [bit] NULL, >>>> [ldailywearos] [bit] NULL, >>>> [lextendedwearod] [bit] NULL, >>>> [lextendedwearos] [bit] NULL, >>>> [lgaspermeableod] [bit] NULL, >>>> [lgaspermeableos] [bit] NULL, >>>> [ldisposableod] [bit] NULL, >>>> [ldisposableos] [bit] NULL, >>>> [ltoricod] [bit] NULL, >>>> [ltoricos] [bit] NULL, >>>> [clt_date] [date] NULL, >>>> [clt_type] [nchar](3) NULL, >>>> [clt_solutions] [text] NULL, >>>> [clt_otherrxod] [varchar](50) NULL, >>>> [clt_otherrxos] [varchar](50) NULL, >>>> [clt_wlfitod] [text] NULL, >>>> [clt_wlfitos] [text] NULL, >>>> [clt_flfitod] [text] NULL, >>>> [clt_flfitos] [text] NULL, >>>> [clt_vaod] [varchar](50) NULL, >>>> [clt_vaos] [varchar](50) NULL, >>>> [clt_orod] [varchar](50) NULL, >>>> [clt_oros] [varchar](50) NULL, >>>> [clt_orvaod] [varchar](50) NULL, >>>> [clt_orvaos] [varchar](50) NULL, >>>> [clt_finalrxod] [varchar](50) NULL, >>>> [clt_finalrxos] [varchar](50) NULL, >>>> [clt_rxnotesod] [text] NULL, >>>> [clt_rxnotesos] [text] NULL, >>>> [clt_finalrxnotesod] [text] NULL, >>>> [clt_finalrxnotesos] [text] NULL, >>>> [clt_action] [text] NULL, >>>> [clt_aftercarecornod] [text] NULL, >>>> [clt_aftercarecornos] [text] NULL, >>>> [clt_aftercaredate] [date] NULL, >>>> [clt_aftercarecornstainod] [text] NULL, >>>> [clt_aftercarecornstainos] [text] NULL, >>>> [clt_aftercareconjstainod] [text] NULL, >>>> [clt_aftercareconjstainos] [text] NULL, >>>> [clt_aftercareaction] [text] NULL, >>>> [clt_multifocalod] [text] NULL, >>>> [clt_multifocalos] [text] NULL, >>>> [clt_vadistod] [varchar](50) NULL, >>>> [clt_vadistos] [varchar](50) NULL, >>>> [clt_vanearod] [varchar](50) NULL, >>>> [clt_vanearos] [varchar](50) NULL, >>>> [clt_vabin] [varchar](50) NULL, >>>> [clt_orvabin] [varchar](50) NULL, >>>> [clt_wtt] [text] NULL, >>>> [clt_lwt] [text] NULL, >>>> [clt_aftercareconjinjod] [text] NULL, >>>> [clt_aftercareconjinjos] [text] NULL, >>>> [clt_aftercarelashesod] [text] NULL, >>>> [clt_aftercarelashesos] [text] NULL, >>>> [clt_aftercarePAPod] [text] NULL, >>>> [clt_aftercarePAPos] [text] NULL, >>>> [clt_aftercareTBUTod] [text] NULL, >>>> [clt_aftercareTBUTos] [text] NULL, >>>> [clt_rgpcentrationod] [text] NULL, >>>> [clt_rgpcentrationos] [text] NULL, >>>> [clt_rgpmovementod] [text] NULL, >>>> [clt_rgpmovementos] [text] NULL, >>>> [clt_rgpFLFitod] [text] NULL, >>>> [clt_rgpFLFitos] [text] NULL, >>>> [clt_slitlampbaseline] [varchar](50) NULL, >>>> [clt_coverageod] [char](16) NULL, >>>> [clt_coverageos] [char](16) NULL, >>>> [clt_centrationod] [char](16) NULL, >>>> [clt_centrationos] [char](16) NULL, >>>> [clt_movementod] [char](16) NULL, >>>> [clt_movementos] [char](16) NULL, >>>> [clt_rotationangleod] [char](16) NULL, >>>> [clt_rotationangleos] [char](16) NULL, >>>> [clt_rotationod] [char](16) NULL, >>>> [clt_rotationos] [char](16) NULL, >>>> [clt_vabinnear] [varchar](50) NULL, >>>> [clt_vabindist] [varchar](50) NULL, >>>> [clt_orsphereod] [char](16) NULL, >>>> [clt_orsphereos] [char](16) NULL, >>>> [clt_orcylinderod] [char](16) NULL, >>>> [clt_orcylinderos] [char](16) NULL, >>>> [clt_ordiameterod] [char](16) NULL, >>>> [clt_ordiameteros] [char](16) NULL, >>>> [clt_oraxisod] [int] NULL, >>>> [clt_oraxisos] [int] NULL, >>>> [clt_oraddod] [char](16) NULL, >>>> [clt_oraddos] [char](16) NULL, >>>> [clt_orvabinnear] [varchar](50) NULL, >>>> [clt_orvabindist] [varchar](50) NULL, >>>> [clt_orvanearod] [varchar](50) NULL, >>>> [clt_orvanearos] [varchar](50) NULL, >>>> [clt_orvadistod] [varchar](50) NULL, >>>> [clt_orvadistos] [varchar](50) NULL, >>>> [clt_finalsphereod] [char](16) NULL, >>>> [clt_finalsphereos] [char](16) NULL, >>>> [clt_finalcylinderod] [char](16) NULL, >>>> [clt_finalcylinderos] [char](16) NULL, >>>> [clt_finalaxisod] [int] NULL, >>>> [clt_finalaxisos] [int] NULL, >>>> [clt_finaladdod] [char](16) NULL, >>>> [clt_finaladdos] [char](16) NULL, >>>> [clt_pushupod] [char](16) NULL, >>>> [clt_pushupos] [char](16) NULL, >>>> [clt_rgpotherod] [varchar](50) NULL, >>>> [clt_rgpotheros] [varchar](50) NULL, >>>> [usr_fk] [uniqueidentifier] NULL, >>>> CONSTRAINT [PK_cltrials] PRIMARY KEY NONCLUSTERED >>>>( >>>> [itrialid] 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 >>>> >>>>ALTER TABLE [dbo].[cltrials] WITH CHECK ADD CONSTRAINT [FK_cltrials_exams] FOREIGN KEY([iexamid]) >>>>REFERENCES [dbo].[exams] ([iexamid]) >>>>ON DELETE CASCADE >>>>GO >>>> >>>>ALTER TABLE [dbo].[cltrials] CHECK CONSTRAINT [FK_cltrials_exams] >>>>GO >>>>>>>>
>>>>CREATE PROCEDURE [dbo].[GenerateAudittrail] >>>> @TableName varchar(128), >>>> @Owner varchar(128) = 'dbo', >>>> @AuditNameExtention varchar(128) = '_shadow', >>>> @DropAuditTable bit = 0 >>>>AS >>>>BEGIN >>>> >>>> -- Check if table exists >>>> IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) >>>> BEGIN >>>> PRINT 'ERROR: Table does not exist' >>>> RETURN >>>> END >>>> >>>> -- Check @AuditNameExtention >>>> IF @AuditNameExtention is null >>>> BEGIN >>>> PRINT 'ERROR: @AuditNameExtention cannot be null' >>>> RETURN >>>> END >>>> >>>> -- Drop audit table if it exists and drop should be forced >>>> IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1) >>>> BEGIN >>>> PRINT 'Dropping audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']' >>>> EXEC ('drop table ' + @TableName + @AuditNameExtention) >>>> END >>>> >>>> -- Declare cursor to loop over columns >>>> DECLARE TableColumns CURSOR Read_Only >>>> FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale >>>> FROM sysobjects a >>>> inner join syscolumns b on a.id = b.id >>>> inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname' >>>> WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']') >>>> and OBJECTPROPERTY(a.id, N'IsUserTable') = 1 >>>> ORDER BY b.colId >>>> >>>> OPEN TableColumns >>>> >>>> -- Declare temp variable to fetch records into >>>> DECLARE @ColumnName varchar(128) >>>> DECLARE @ColumnType varchar(128) >>>> DECLARE @ColumnLength smallint >>>> DECLARE @ColumnNullable int >>>> DECLARE @ColumnCollation sysname >>>> DECLARE @ColumnPrecision tinyint >>>> DECLARE @ColumnScale tinyint >>>> >>>> -- Declare variable to build statements >>>> DECLARE @CreateStatement varchar(8000) >>>> DECLARE @ListOfFields varchar(2000) >>>> SET @ListOfFields = '' >>>> >>>> >>>> -- Check if audit table exists >>>> IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) >>>> BEGIN >>>> -- AuditTable exists, update needed >>>> PRINT 'Table already exists. Only triggers will be updated.' >>>> >>>> FETCH Next FROM TableColumns >>>> INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale >>>> >>>> WHILE @@FETCH_STATUS = 0 >>>> BEGIN >>>> IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp') >>>> BEGIN >>>> SET @ListOfFields = @ListOfFields + @ColumnName + ',' >>>> END >>>> >>>> FETCH Next FROM TableColumns >>>> INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale >>>> >>>> END >>>> END >>>> ELSE >>>> BEGIN >>>> -- AuditTable does not exist, create new >>>> >>>> -- Start of create table >>>> SET @CreateStatement = 'CREATE TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] (' >>>> SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,' >>>> >>>> FETCH Next FROM TableColumns >>>> INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale >>>> >>>> WHILE @@FETCH_STATUS = 0 >>>> BEGIN >>>> IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp') >>>> BEGIN >>>> SET @ListOfFields = @ListOfFields + @ColumnName + ',' >>>> >>>> SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] ' >>>> >>>> IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar') >>>> BEGIN >>>> IF (@ColumnLength = -1) >>>> Set @CreateStatement = @CreateStatement + '(max) ' >>>> ELSE >>>> SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') ' >>>> END >>>> >>>> IF @ColumnType in ('decimal', 'numeric') >>>> SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') ' >>>> >>>> IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext') >>>> SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' ' >>>> >>>> IF @ColumnNullable = 0 >>>> SET @CreateStatement = @CreateStatement + 'NOT ' >>>> >>>> SET @CreateStatement = @CreateStatement + 'NULL, ' >>>> END >>>> >>>> FETCH Next FROM TableColumns >>>> INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale >>>> END >>>> >>>> -- Add audit trail columns >>>> SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,' >>>> SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,' >>>> SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,' >>>> SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)' >>>> >>>> -- Create audit table >>>> PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']' >>>> EXEC (@CreateStatement) >>>> >>>> -- Set primary key and default values >>>> SET @CreateStatement = 'ALTER TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ADD ' >>>> SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],' >>>> SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtention + '] PRIMARY KEY CLUSTERED ' >>>> SET @CreateStatement = @CreateStatement + '([AuditId]) ON [PRIMARY], ' >>>> SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]' >>>> >>>> EXEC (@CreateStatement) >>>> >>>> END >>>> >>>> CLOSE TableColumns >>>> DEALLOCATE TableColumns >>>> >>>> /* Drop Triggers, if they exist */ >>>> PRINT 'Dropping triggers' >>>> IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) >>>> EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]') >>>> >>>> IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) >>>> EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]') >>>> >>>> IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) >>>> EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]') >>>> >>>> /* Create triggers */ >>>> PRINT 'Creating triggers' >>>> EXEC ('CREATE TRIGGER tr_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted') >>>> >>>> EXEC ('CREATE TRIGGER tr_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted') >>>> >>>> EXEC ('CREATE TRIGGER tr_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted') >>>> >>>>END >>>>>>>>
CREATE TABLE [dbo].[cltrials_shadow] (... lots of other fields here..., [clt_rotationos] [char] (16) COLLATE Latin1_General_CI_AS NULL, [clt_vabinnear] [varchar] (50) COLLATENULL,>>>>