MSSQL: Batch Alter Table Drop Columns

-- USE DB_DEV;
-- USE DB_QA;
-- USE DB_SIT;
-- USE DB_PRO; -- 1. Configuration
DECLARE @SchemaName AS NVARCHAR (128) = N'PItt';
DECLARE @TableName AS NVARCHAR (128) = N'Phunsanit'; -- 2. stop scrip if column not null and empty
DECLARE @StopScript BIT = 0; -- flag to stop the script -- 3. Variables for the script
DECLARE @FullTableName NVARCHAR (257) ;
DECLARE @TableObjectID INT;
DECLARE @CurrentColumnName NVARCHAR (128) ;
DECLARE @SqlStatement NVARCHAR (MAX) ;
DECLARE @HasData BIT = 0;
DECLARE @CheckSql NVARCHAR (MAX) ;
DECLARE @CheckParams NVARCHAR (100) = N'@HasData BIT OUTPUT';
DECLARE @DataTypeName NVARCHAR (128) ; -- <-- NEW: To store the column's data type -- 4. Define the list of columns you want to drop
DECLARE @ColumnsToDrop TABLE (ColumnName NVARCHAR (128) PRIMARY KEY) ; INSERT INTO @ColumnsToDrop (ColumnName) VALUES (N'created_at') , (N'created_by') , (N'deleted_at') , (N'deleted_by') , (N'updated_at') , (N'updated_by') , -- 5. Store the actual commands to run (if checks pass) DECLARE @CommandsToRun TABLE (SqlStatement NVARCHAR (MAX) , ColumnName NVARCHAR (128)) ; -- 6. Safety Check: Stop if the table itself doesn't exist
SET @FullTableName = QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TableName) ;
SET @TableObjectID = OBJECT_ID (@FullTableName) ; IF @TableObjectID IS NULL
BEGIN PRINT 'Error: The table ' + @FullTableName + ' does not exist. Halting script.'; RETURN; -- Stops the script
END PRINT '--- PRE-CHECK ---';
PRINT 'Checking columns on table: ' + @FullTableName; -- 7. Create a cursor to loop through and check each column
DECLARE col_cursor CURSOR FOR
SELECT ColumnName FROM @ColumnsToDrop; OPEN col_cursor; FETCH NEXT FROM col_cursor INTO @CurrentColumnName; -- 8. First Loop (Pre-Check) WHILE @@FETCH_STATUS = 0
BEGIN SET @HasData = 0; -- Reset flag for each column -- Check if the column exists on this specific table IF EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = @TableObjectID AND c.name = @CurrentColumnName) BEGIN -- *** MODIFIED BLOCK *** -- First, get the column's data type SELECT @DataTypeName = ty.name FROM sys.columns c JOIN sys.types ty ON c.user_type_id = ty.user_type_id WHERE c.object_id = @TableObjectID AND c.name = @CurrentColumnName; -- Now build the data check SQL based on the type IF @DataTypeName IN (N'varchar', N'nvarchar', N'char', N'nchar', N'text', N'ntext') BEGIN -- For string types, check for NOT NULL AND not empty string (your request) -- This means NULLs or '' (empty strings) are OK to drop SET @CheckSql = N'SELECT TOP 1 @HasData = 1 FROM ' + @FullTableName + N' WHERE ' + QUOTENAME (@CurrentColumnName) + N' IS NOT NULL' + N' AND ' + QUOTENAME (@CurrentColumnName) + N' != '''''; -- '''' is an escaped ' PRINT ' [INFO] Column ' + QUOTENAME (@CurrentColumnName) + ' is a string. Checking for data (not NULL and not '''') .'; END ELSE BEGIN -- For non-string types (int, datetime, etc.) , just check for NOT NULL SET @CheckSql = N'SELECT TOP 1 @HasData = 1 FROM ' + @FullTableName + N' WHERE ' + QUOTENAME (@CurrentColumnName) + N' IS NOT NULL'; PRINT ' [INFO] Column ' + QUOTENAME (@CurrentColumnName) + ' is not a string. Checking for data (not NULL) .'; END -- Run the dynamic check EXEC sp_executesql @CheckSql, @CheckParams, @HasData = @HasData OUTPUT; -- *** END OF MODIFIED BLOCK *** IF @HasData = 1 BEGIN -- Data found! Mark the script to stop. PRINT ' [ERROR] Column ' + QUOTENAME (@CurrentColumnName) + ' contains data. Script will be stopped.'; SET @StopScript = 1; END ELSE BEGIN -- Column is empty (all NULL or '') . It is safe to drop. PRINT ' [OK] Column ' + QUOTENAME (@CurrentColumnName) + ' is empty. Queued for drop.'; -- Add the drop command to our queue SET @SqlStatement = N'ALTER TABLE ' + @FullTableName + N' DROP COLUMN ' + QUOTENAME (@CurrentColumnName) ; INSERT INTO @CommandsToRun (SqlStatement, ColumnName) VALUES (@SqlStatement, @CurrentColumnName) ; END END ELSE BEGIN -- Column does not exist, so skip it PRINT ' [INFO] Column ' + QUOTENAME (@CurrentColumnName) + ' (does not exist) . Skipping.'; END -- Get the next column from the list FETCH NEXT FROM col_cursor INTO @CurrentColumnName;
END; -- 9. Clean up the check cursor
CLOSE col_cursor;
DEALLOCATE col_cursor; -- 10. THE "STOP" CHECK
PRINT '---';
IF @StopScript = 1
BEGIN PRINT 'Execution HALTED. One or more columns contained data (see [ERROR] messages above) .'; PRINT 'No columns were dropped.'; RETURN; -- This is the STOP you requested
END -- 11. Execution (only runs if @StopScript is still 0) PRINT 'All checks passed. Proceeding with drop operations...'; DECLARE exec_cursor CURSOR FOR
SELECT SqlStatement, ColumnName FROM @CommandsToRun; OPEN exec_cursor; FETCH NEXT FROM exec_cursor INTO @SqlStatement, @CurrentColumnName; WHILE @@FETCH_STATUS = 0
BEGIN BEGIN TRY EXEC sp_executesql @SqlStatement; PRINT ' Successfully dropped column: ' + QUOTENAME (@CurrentColumnName) ; END TRY BEGIN CATCH PRINT ' ERROR: Could not drop column ' + QUOTENAME (@CurrentColumnName) + '. Please check dependencies (indexes, constraints) .'; PRINT ' Error Message: ' + ERROR_MESSAGE () ; END CATCH FETCH NEXT FROM exec_cursor INTO @SqlStatement, @CurrentColumnName;
END; -- 12. Clean up the execution cursor
CLOSE exec_cursor;
DEALLOCATE exec_cursor; PRINT 'Column drop process complete.';