dbo.QBM_PColumnDrop

1    create   procedure QBM_PColumnDrop ( @TableName nvarchar(64), @columnname nvarchar(64) ) as begin declare @obj nvarchar(64) = null declare @objColumn
2 nvarchar(64) = null declare @SQLcmd nvarchar(1024) declare @Table_intern nvarchar(64) declare @DebugSwitch int = 0 select @obj = null SET XACT_ABORT OFF
3 BEGIN TRY select top 1 @Table_intern = o.name + N'.' + @TableName from sys.tables t join sys.schemas o on t.schema_id = o.schema_id where t.name = @TableName
4 collate database_default and dbo.QBM_FGIObjectIsDropable (t.schema_id)=1 select top 1 @obj = co.name , @objColumn = c.column_id from sys.tables t with
5 (readpast) join sys.columns c with (readpast) on t.object_id = c.object_id left outer join sys.default_constraints co on co.parent_object_id = t.object_id
6 and co.parent_column_id = c.column_id where t.name = @TableName and c.name = @columnname if @objColumn is null begin if @DebugSwitch > 0 begin print 'Spalte nicht (mehr) da'
7 end goto nurNochLogisch end if @obj is not null begin select @SQLcmd = N'alter table ' + @Table_intern + N' drop constraint "' + @obj + N'"' if @DebugSwitch
8 > 0 begin print @SQLcmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , 
9@ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 end else begin if @DebugSwitch > 0 begin print 'kein constraint gefunden'
10 end end  if exists (select top 1 1  from information_schema.columns where table_name = @TableName and column_name = @Columnname and isnull(COLUMN_DEFAULT
11, '') > ' ' ) begin select @SQLcmd = N'exec sp_unbindefault "' + @Table_intern + N'.' + @columnname + N'"' if @DebugSwitch > 0 begin print @SQLcmd end 
12exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid 
13, @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 end indexmarke: select @obj = null select top 1 @obj = i.name from sys.index_columns ik with (nolock
14) join sys.columns c with (nolock) on ik.column_id = c.column_id and c.name = @columnname and c.object_id = ik.object_id join sys.tables t on t.object_id
15 = ik.object_id and t.name = @TableName join sys.indexes i on ik.index_id = i.index_id and i.object_id = t.object_id where i.is_primary_key = 0 and i.is_hypothetical
16 = 0  and i.type not in (3,4,5,6) if @obj is not null begin exec QBM_PIndexDrop @TableName , @obj goto indexmarke end  marke: select @obj = null select
17 top 1 @obj = i.name from sys.tables t join sys.stats i with (nolock) on i.object_id = t.object_id and t.name = @TableName where i.auto_created = 1 if 
18@obj is not null begin select @SQLcmd = N'drop statistics ' + @TableName + N'."' + @obj + N'"'  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd
19 , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 goto
20 marke end if exists (select top 1 1 from QBM_VSchemaColumns where table_name = @TableName and column_name = @columnname ) and @Table_intern > ' ' begin
21 select @SQLcmd = N'alter table ' + @Table_intern + N' drop column ' + @columnname  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms
22 = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 end nurNochLogisch: if
23 exists (select top 1 1 from sys.tables t where t.name = 'DialogColumnGroupRight' ) begin delete DialogColumnGroupRight from DialogColumnGroupRight gr 
24join DialogColumn c on gr.UID_DialogColumn = c.UID_DialogColumn join DialogTable t on c.UID_DialogTable = t.UID_DialogTable where c.ColumnName = @columnname
25 and t.TableName = @TableName end if exists (select top 1 1 from sys.tables t where t.name = 'DialogColumn' ) begin delete DialogColumn from DialogColumn
26 c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable where c.ColumnName = @columnname and t.TableName = @TableName end END TRY BEGIN CATCH exec
27 QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end  
28