How to set go to error handler and roll back for each sql execution statements in Sql?
<pre lang="SQL">
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Remove_Identity_Columns]
as
declare @Identity_Details table
(
IDVal int identity(1,1) ,
SchemaName varchar(25),
TableName varchar(25),
ColumnName varchar(25),
DataType varchar(10)
)
declare @Foreign_keys table
(
Idval int identity(1,1),
ForeignKeyName varchar(1000),
ParentTable varchar(100)
)
declare
@NewColumn_Creation_Query nvarchar(1000),
@UpdateColumn_Query nvarchar(1000),
@Column_Rename_Query nvarchar(1000),
@Primary_Key_Creation_Query nvarchar(1000),
@DropColumn_Query nvarchar(1000),
@Leadingstring varchar(5),
@SchemaName varchar(25),
@TableName varchar(25),
@ColumnName varchar(25),
@DataType varchar(10),
@Count int,
@Loop int,
@Count_FK int,
@Loop_FK int,
@Drop_Key_Query nvarchar(1000)
BEGIN TRAN
insert @Identity_Details
(
SchemaName,
TableName,
ColumnName,
DataType
)
select
c.TABLE_SCHEMA ,
t.TABLE_NAME ,
c.COLUMN_NAME,
c.DATA_TYPE
from
information_schema.columns c
inner join
information_schema.tables t
on t.TABLE_NAME = c.TABLE_NAME
where
columnproperty(object_id(c.TABLE_NAME),c.COLUMN_NAME, 'IsIdentity') = 1
select
@Leadingstring= '_dup',
@Count =count(*),
@Loop = 1,
@NewColumn_Creation_Query = '',
@UpdateColumn_Query='',
@DropColumn_Query='',
@Column_Rename_Query='',
@Primary_Key_Creation_Query=''
from
@Identity_Details
while(@Loop<=@Count)
begin
select
@SchemaName = SchemaName,
@TableName = TableName,
@ColumnName = ColumnName,
@DataType = DataType
from @Identity_Details
where IDVal = @Loop
select @NewColumn_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.'+ @TableName + ' ADD ' +@ColumnName + @Leadingstring + ' ' + @DataType
exec sp_executesql @NewColumn_Creation_Query
Print 'New Column Creation Completed Successfully'
select @UpdateColumn_Query = 'UPDATE ' + @SchemaName + '.'+@TableName + ' SET ' + @ColumnName + @Leadingstring+ ' = ' + @ColumnName
exec sp_executesql @UpdateColumn_Query
Print 'New Column Values Updated with Existing Values'
insert @Foreign_keys (ForeignKeyName,ParentTable)
select name,object_name([parent_object_id])
from sys.foreign_keys
where object_name([referenced_object_id]) = @TableName
select
@Count_FK = max(IDVal),
@Loop_FK = MIN(IdVal)
from
@Foreign_keys
while(@Loop_FK<=@Count_FK)
begin
select @Drop_Key_Query = 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT ' + ForeignKeyName
from @Foreign_keys where Idval = @Loop_FK
exec sp_executesql @Drop_Key_Query
select @Drop_Key_Query = ''
select @Loop_FK = @Loop_FK + 1
end
Print 'All the Foreign key Constriants Dropped'
while 0=0 begin
declare @constraintName varchar(128)
set @constraintName = (
select top 1 constraint_name
from information_schema.constraint_column_usage
where table_name = @tableName and column_name = @columnName )
if @constraintName is null break
exec ('alter table "'+@TableName+'" drop constraint "'+@constraintName+'"')
end
Print 'Primary key Constriants Dropped'
select @DropColumn_Query = 'ALTER TABLE '+ @SchemaName + '.'+@TableName + ' DROP COLUMN ' + @ColumnName
exec sp_executesql @DropColumn_Query
Print 'Already Exisiting Columns Dropped'
select @Column_Rename_Query = 'exec sp_rename ' + '''' + @SchemaName + '.' + @TableName + '.' +@ColumnName + @Leadingstring +'''' + ',' + '''' + @ColumnName + '''' + ',' + '''' +'COLUMN' +''''
exec sp_executesql @Column_Rename_Query
Print 'Column Name Renamed from Temporary to Original Existing Column Names'
select @NewColumn_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.'+ @TableName + ' ALTER COLUMN ' +@ColumnName + ' ' + @DataType + ' Not Null'
exec sp_executesql @NewColumn_Creation_Query
Print 'New Column Name Data Type set as Not Nullable'
select @Primary_Key_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' add constraint ' + 'PK_' + @TableName + +'_' + @ColumnName+' primary key(' + '['+@ColumnName+']' + ')'
exec sp_executesql @Primary_Key_Creation_Query
Print 'Primary Key Constraint Added for Primary Key Columns'
select
@Loop = @Loop + 1,
@NewColumn_Creation_Query = '',
@UpdateColumn_Query = '',
@Column_Rename_Query = '',
@Primary_Key_Creation_Query = '',
@DropColumn_Query = ''
delete from @Foreign_keys
end
COMMIT TRAN</pre>
What I have tried:
Script I am using:
/****** Object: StoredProcedure [dbo].[Remove_Identity_Columns] Script Date: 04/11/2016 18:32:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec CHK_INST_IDENTFIER '400220101','',''
--exec Remove_Identity_Columns
ALTER proc [dbo].[Remove_Identity_Columns]
as
--Create a Temporary Table @Identity_Details to which Identity Value,Schema Name,Table/Column Name/Data Type from Identity Insert Tables Data is Passed
declare @Identity_Details table
(
IDVal int identity(1,1) ,
SchemaName varchar(25),
TableName varchar(25),
ColumnName varchar(25),
DataType varchar(10)
)
declare @Foreign_keys table
(
Idval int identity(1,1),
ForeignKeyName varchar(1000),
ParentTable varchar(100)
)
declare
@NewColumn_Creation_Query nvarchar(1000),
@UpdateColumn_Query nvarchar(1000),
@Column_Rename_Query nvarchar(1000),
@Primary_Key_Creation_Query nvarchar(1000),
@DropColumn_Query nvarchar(1000),
@Leadingstring varchar(5),
@SchemaName varchar(25),
@TableName varchar(25),
@ColumnName varchar(25),
@DataType varchar(10),
@Count int,
@Loop int,
@Count_FK int,
@Loop_FK int,
@Drop_Key_Query nvarchar(1000)
BEGIN TRAN
insert @Identity_Details
(
SchemaName,
TableName,
ColumnName,
DataType
)
-- Schema/Table Name/Column Name/Data Type etc with Identity Insert Columns passed to Temporary Table @Identity_Details
select
c.TABLE_SCHEMA ,
t.TABLE_NAME ,
c.COLUMN_NAME,
c.DATA_TYPE
from
information_schema.columns c
inner join
information_schema.tables t
on t.TABLE_NAME = c.TABLE_NAME
where
columnproperty(object_id(c.TABLE_NAME),c.COLUMN_NAME, 'IsIdentity') = 1
--End
-- Column Names with _dup suffixed and count etc passed to Temporary Table @Identity_Details
select
@Leadingstring= '_dup',
@Count =count(*),
@Loop = 1,
@NewColumn_Creation_Query = '',
@UpdateColumn_Query='',
@DropColumn_Query='',
@Column_Rename_Query='',
@Primary_Key_Creation_Query=''
from
@Identity_Details
--End
--Passing the Identity Insert Tables one by one from Temporary Table with Table/Column/Schema Names of the same one by one in the While Loop
while(@Loop<=@Count)
begin
select
@SchemaName = SchemaName,
@TableName = TableName,
@ColumnName = ColumnName,
@DataType = DataType
from @Identity_Details
where IDVal = @Loop
--To each identity insert a new column created with _dup suffixed(Leading String)
select @NewColumn_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.'+ @TableName + ' ADD ' +@ColumnName + @Leadingstring + ' ' + @DataType
exec sp_executesql @NewColumn_Creation_Query
Print 'New Column Creation Completed Successfully'
--End
--To each identity update data in the Temp Column newly created (_dup) to Real Column
select @UpdateColumn_Query = 'UPDATE ' + @SchemaName + '.'+@TableName + ' SET ' + @ColumnName + @Leadingstring+ ' = ' + @ColumnName
exec sp_executesql @UpdateColumn_Query
Print 'New Column Values Updated with Existing Values'
--End
---exec Drop_FK @TableName
--Drop the Foreign key Constriants of Newly Created Tables
insert @Foreign_keys (ForeignKeyName,ParentTable)
select name,object_name([parent_object_id])
from sys.foreign_keys
where object_name([referenced_object_id]) = @TableName
select
@Count_FK = max(IDVal),
@Loop_FK = MIN(IdVal)
from
@Foreign_keys
while(@Loop_FK<=@Count_FK)
begin
select @Drop_Key_Query = 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT ' + ForeignKeyName
from @Foreign_keys where Idval = @Loop_FK
--select @Drop_Key_Query
exec sp_executesql @Drop_Key_Query
select @Drop_Key_Query = ''
select @Loop_FK = @Loop_FK + 1
end
Print 'All the Foreign key Constriants Dropped'
--End
---exec DropAllColumnConstraints @TableName,@ColumnName
--Drop All Column Constraints
while 0=0 begin
declare @constraintName varchar(128)
set @constraintName = (
select top 1 constraint_name
from information_schema.constraint_column_usage
where table_name = @tableName and column_name = @columnName )
if @constraintName is null break
--print ('alter table "'+@tableName+'" drop constraint "'+@constraintName+'"')
exec ('alter table "'+@TableName+'" drop constraint "'+@constraintName+'"')
end
Print 'Primary key Constriants Dropped'
--End
--Already Existing Columns dropped afterwards.
select @DropColumn_Query = 'ALTER TABLE '+ @SchemaName + '.'+@TableName + ' DROP COLUMN ' + @ColumnName
--PRINT @DropColumn_Query
exec sp_executesql @DropColumn_Query
Print 'Already Exisiting Columns Dropped'
--End
--Rename Columns with_dup suffixed to Column already existed before
select @Column_Rename_Query = 'exec sp_rename ' + '''' + @SchemaName + '.' + @TableName + '.' +@ColumnName + @Leadingstring +'''' + ',' + '''' + @ColumnName + '''' + ',' + '''' +'COLUMN' +''''
-- Print @Column_Rename_Query
exec sp_executesql @Column_Rename_Query
Print 'Column Name Renamed from Temporary to Original Existing Column Names'
--End
--Set the New Column renamed as not null
select @NewColumn_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.'+ @TableName + ' ALTER COLUMN ' +@ColumnName + ' ' + @DataType + ' Not Null'
--PRINT @NewColumn_Creation_Query
exec sp_executesql @NewColumn_Creation_Query
Print 'New Column Name Data Type set as Not Nullable'
--End
--Primary Key Constraint Added for Identity Insert Columns
select @Primary_Key_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' add constraint ' + 'PK_' + @TableName + +'_' + @ColumnName+' primary key(' + '['+@ColumnName+']' + ')'
--Print @Primary_Key_Creation_Query
exec sp_executesql @Primary_Key_Creation_Query
Print 'Primary Key Constraint Added for Primary Key Columns'
--End
select
@Loop = @Loop + 1,
@NewColumn_Creation_Query = '',
@UpdateColumn_Query = '',
@Column_Rename_Query = '',
@Primary_Key_Creation_Query = '',
@DropColumn_Query = ''
delete from @Foreign_keys
end
COMMIT TRAN