>-- 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,>>