-- 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 = '' >Move that code before DECLARE cursor.
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,>