Back to OIM Explorer

dbo.QBM_PColumnDrop

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.148 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FGIObjectIsDropable source text reference
references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
references source dbo.QBM_PIndexDrop source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

1CREATE PROCEDURE QBM_PColumnDrop(2  @TableName nvarchar(64),3  @columnname nvarchar(64)4)5AS6BEGIN7  DECLARE @obj nvarchar(64) = NULL8  DECLARE @objColumn nvarchar(64) = NULL9  DECLARE @SQLcmd nvarchar(1024)10  DECLARE @Table_intern nvarchar(64)11  DECLARE @DebugSwitch int = 012  SELECT @obj = NULL13  SET XACT_ABORT OFF14  BEGIN TRY15    SELECT TOP 1 @Table_intern = o.name + N '.' + @TableName16    FROM sys.tables t17    JOIN sys.schemas o18      ON t.schema_id = o.schema_id19    WHERE20      t.name = @TableName collate database_default AND dbo.QBM_FGIObjectIsDropable(t.schema_id) =121    SELECT22      TOP 1 @obj = co.name,23      @objColumn = c.column_id24    FROM sys.tables t25      WITH(readpast)26    JOIN sys.columns c27      WITH(readpast)28      ON t.object_id = c.object_id29    LEFT30    OUTER31    JOIN sys.default_constraints co32      ON co.parent_object_id = t.object_id AND co.parent_column_id = c.column_id33    WHERE34      t.name = @TableName AND c.name = @columnname35    IF @objColumn IS NULL36    BEGIN37      IF @DebugSwitch > 038      BEGIN39        print 'Spalte nicht (mehr) da'40      END41      GOTO nurNochLogisch42    END43    IF @obj IS NOT NULL44    BEGIN45      SELECT46        @SQLcmd = N 'alter table ' + @Table_intern + N ' drop constraint "' + @obj + N '"'47      IF @DebugSwitch > 048      BEGIN49        print @SQLcmd50      END51      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,52        @LockTimeout_ms = DEFAULT,53        @MaxWaitTimeForLock_s = DEFAULT,54        @ProcIDForJournal = @@procid,55        @HandleErrorSilent = 0,56        @ExecuteWithTransact = 057    END58    ELSE59    BEGIN60      IF @DebugSwitch > 061      BEGIN62        print 'kein constraint gefunden'63      END64    END65    IF EXISTS(66      SELECT TOP 1 167      FROM information_schema.columns68      WHERE69        table_name = @TableName AND column_name = @Columnname AND isnull(COLUMN_DEFAULT, '') > ' ')70    BEGIN71      SELECT72        @SQLcmd = N 'exec sp_unbindefault "' + @Table_intern + N '.' + @columnname + N '"'73      IF @DebugSwitch > 074      BEGIN75        print @SQLcmd76      END77      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,78        @LockTimeout_ms = DEFAULT,79        @MaxWaitTimeForLock_s = DEFAULT,80        @ProcIDForJournal = @@procid,81        @HandleErrorSilent = 0,82        @ExecuteWithTransact = 083    END84    indexmarke:85    SELECT @obj = NULL86    SELECT TOP 1 @obj = i.name87    FROM sys.index_columns ik88      WITH(nolock)89    JOIN sys.columns c90      WITH(nolock)91      ON ik.column_id = c.column_id AND c.name = @columnname AND c.object_id = ik.object_id92    JOIN sys.tables t93      ON t.object_id = ik.object_id AND t.name = @TableName94    JOIN sys.indexes i95      ON ik.index_id = i.index_id AND i.object_id = t.object_id96    WHERE97      i.is_primary_key = 0 AND i.is_hypothetical = 0 AND i.type NOT IN(3,98    4,99    5,100    6)101    IF @obj IS NOT NULL102    BEGIN103      EXEC QBM_PIndexDrop @TableName,104        @obj105      GOTO indexmarke106    END107    marke:108    SELECT @obj = NULL109    SELECT TOP 1 @obj = i.name110    FROM sys.tables t111    JOIN sys.stats i112      WITH(nolock)113      ON i.object_id = t.object_id AND t.name = @TableName114    WHERE115      i.auto_created = 1116    IF @obj IS NOT NULL117    BEGIN118      SELECT @SQLcmd = N 'drop statistics ' + @TableName + N '."' + @obj + N '"'119      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,120        @LockTimeout_ms = DEFAULT,121        @MaxWaitTimeForLock_s = DEFAULT,122        @ProcIDForJournal = @@procid,123        @HandleErrorSilent = 0,124        @ExecuteWithTransact = 0125      GOTO marke126    END127    IF EXISTS(128      SELECT TOP 1 1129      FROM QBM_VSchemaColumns130      WHERE131        table_name = @TableName AND column_name = @columnname) AND @Table_intern > ' '132    BEGIN133      SELECT @SQLcmd = N 'alter table ' + @Table_intern + N ' drop column ' + @columnname134      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,135        @LockTimeout_ms = DEFAULT,136        @MaxWaitTimeForLock_s = DEFAULT,137        @ProcIDForJournal = @@procid,138        @HandleErrorSilent = 0,139        @ExecuteWithTransact = 0140    END141    nurNochLogisch:142    IF EXISTS(143      SELECT TOP 1 1144      FROM sys.tables t145      WHERE146        t.name = 'DialogColumnGroupRight')147    BEGIN148      DELETE DialogColumnGroupRight149      FROM DialogColumnGroupRight gr150      JOIN DialogColumn c151        ON gr.UID_DialogColumn = c.UID_DialogColumn152      JOIN DialogTable t153        ON c.UID_DialogTable = t.UID_DialogTable154      WHERE155        c.ColumnName = @columnname AND t.TableName = @TableName156    END157    IF EXISTS(158      SELECT TOP 1 1159      FROM sys.tables t160      WHERE161        t.name = 'DialogColumn')162    BEGIN163      DELETE DialogColumn164      FROM DialogColumn c165      JOIN DialogTable t166        ON c.UID_DialogTable = t.UID_DialogTable167      WHERE168        c.ColumnName = @columnname AND t.TableName = @TableName169    END170  END TRY171  BEGIN CATCH172    EXEC QBM_PSessionErrorAdd DEFAULT173    RAISERROR('',174    18,175    1)176      WITH NOWAIT177  END CATCH178END

Open raw exported source

1    create   procedure QBM_PColumnDrop ( @TableName nvarchar(64), @columnname nvarchar(64) ) as begin declare @obj nvarchar(64) = null declare @objColumn2 nvarchar(64) = null declare @SQLcmd nvarchar(1024) declare @Table_intern nvarchar(64) declare @DebugSwitch int = 0 select @obj = null SET XACT_ABORT OFF3 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 = @TableName4 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 with5 (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_id6 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 @DebugSwitch8 > 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_DEFAULT11, '') > ' ' ) 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 (nolock14) 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_id15 = 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_hypothetical16 = 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 select17 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 = @SQLcmd19 , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 goto20 marke end if exists (select top 1 1 from QBM_VSchemaColumns where table_name = @TableName and column_name = @columnname ) and @Table_intern > ' ' begin21 select @SQLcmd = N'alter table ' + @Table_intern + N' drop column ' + @columnname  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms22 = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 end nurNochLogisch: if23 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 = @columnname25 and t.TableName = @TableName end if exists (select top 1 1 from sys.tables t where t.name = 'DialogColumn' ) begin delete DialogColumn from DialogColumn26 c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable where c.ColumnName = @columnname and t.TableName = @TableName end END TRY BEGIN CATCH exec27 QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end  28