Drop Columns in multiple tables that have Column Name similar to values in another Table

Question

I need help with a requirement. I am able to manually complete the requirement but hoping this could be automated. Please see below.

1. DB has many tables.

2. One of the tables 'TblMaster' has 2 columns.

   a. CName
   b. Flag

   Sample Table (TblMaster) Creation Code:

    CREATE TABLE TblMaster (CName varchar(30), Flag int)
    INSERT INTO TblMaster (cname,flag) values ('ABC',0);
    INSERT INTO TblMaster (cname,flag) values ('DEF',1);
    INSERT INTO TblMaster (cname,flag) values ('GHI',1);
    INSERT INTO TblMaster (cname,flag) values ('JKL',1);
    INSERT INTO TblMaster (cname,flag) values ('MNO',1);

3. There could be several other tables in the DB that has one or more columns with string content 'CName' from the table 'TblMaster'.

   Sample Tables (TB1 and TB2) Creation Code:

   
    CREATE TABLE TB1 
    (
       ABC int ,
       ABC1 int, 
       ABC2 int,
       DEF varchar(30),
       DEF1 varchar(30),
       DEF2 varchar(30),
       GHI varchar(30),
       GHI1 varchar(30),
       JKL numeric,
       JKL1 numeric,
       MNO datetime,
       MNO1 datetime,
       NOTEXIST varchar(30),
       NOTEXIST1 varchar(30), 
       NOTEXIST2 varchar(30)  
    )

    INSERT INTO TB1 values (0,1,2,'d1','d2','d3','g1','g2',0.0,1.0,null,null,Null,NUll,Null)
    INSERT INTO TB1 values (0,1,2,'d1','d2','d3','g1','g2',0.0,1.0,null,null,Null,NUll,Null)
    INSERT INTO TB1 values (0,1,2,'d1','d2','d3','g1','g2',0.0,1.0,null,null,Null,NUll,Null)
    INSERT INTO TB1 values (0,1,2,'d1','d2','d3','g1','g2',0.0,1.0,null,null,Null,NUll,Null)


    CREATE TABLE TB2 
    (
       ABC int ,
       ABC1 int, 
       ABC2 int,
       DEF varchar(30),
       DEF1 varchar(30),
       DEF2 varchar(30),
       GHI varchar(30),
       GHI1 varchar(30),
       JKL numeric,
       JKL1 numeric,
       MNO datetime,
       MNO1 datetime,
       NOTEXIST varchar(30),
       NOTEXIST1 varchar(30), 
       NOTEXIST2 varchar(30)  
    )

    INSERT INTO TB2 values (0,1,2,'d1','d2','d3','g1','g2',0.0,1.0,null,null,Null,NUll,Null)
    INSERT INTO TB2 values (0,1,2,'d1','d2','d3','g1','g2',0.0,1.0,null,null,Null,NUll,Null)
    INSERT INTO TB2 values (0,1,2,'d1','d2','d3','g1','g2',0.0,1.0,null,null,Null,NUll,Null)
    INSERT INTO TB2 values (0,1,2,'d1','d2','d3','g1','g2',0.0,1.0,null,null,Null,NUll,Null)

4. Requirement: I want to automate the below manual steps in a single execution using loops/variables. I tried defining a variable but it is not working.

   a. Select the CName values from TblMaster that have a Flag = 1.

   Select CName from TblMaster where FLAG = 1
   
   This yields result:
   DEF
   GHI
   JKL
   MNO
   

   b. Use the results in 4.a above to identify Column Names in other tables in the DB with Similar string content (values). Below code snipped selects for the value of 'DEF'

   SELECT t.name AS table_name,c.name AS column_name
   FROM sys.tables AS t
   INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
   WHERE c.name LIKE '%DEF%' and t.name in ('TB1','TB2')
   ORDER BY  table_name;
   
   This yields result:
   
   table_name  column_name
   TB1             DEF
   TB1             DEF1
   TB1             DEF2
   TB2             DEF
   TB2             DEF1
   TB2             DEF2
   
   Similarly repeat the above for 'GHI', 'JKL', 'MNO'.
   

   c. Drop the columns in the tables identified in step 4.b (all results).

   ALTER TABLE TB1
   DROP COLUMN 
   DEF,DEF1,DEF2,GHI,GHI1,JKL, JKL1,MNO,MNO1
   
   ALTER TABLE TB2
   DROP COLUMN 
   DEF,DEF1,DEF2,GHI,GHI1,JKL, JKL1,MNO,MNO1
   

Thanks in advance!!

Below is what I tried but it is not working. Please help.

DECLARE @cnt INT = 0 
DECLARE @tempcnt INT = 1
DECLARE @tempcname varchar(30)

/* Find the max number of count for which we need to check other tables */

SELECT @cnt = COUNT (*) FROM TblMaster WHERE flag = 1

/* Create a temporary table with Row Number and the Column that we need */

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS id, CName 
INTO #TempTable
FROM (SELECT * FROM TblMaster WHERE flag = 1) t;

/* Loop through the temporary table to process each CName */

WHILE @tempcnt <= @cnt
BEGIN
  SELECT @tempcname = CName 
  FROM #TempTable WHERE id = @tempcnt     
  /* PRINT @tempcname */ 

/* Identify the Columns that have CName string as Column Name in other 
   tables */
ALTER TABLE TB1
DROP COLUMN 
(
SELECT c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE %@tempcname% and t.name = TB1
)

ALTER TABLE TB2
DROP COLUMN 
(
SELECT c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE %@tempcname% and t.name = TB2
)
SET @tempcnt = @tempcnt + 1;
END;

DROP TABLE #TempTable;

Answer 1

Check if this procedure works as expected.

Have a look at fiddle, (you'll find a link at bottom).

I've used a double dynamic query.

First one returns a list of comma separated of similar columns.

SELECT @columns = STUFF((SELECT ',' + COLUMN_NAME 
                      FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE  (COLUMN_NAME LIKE '%DEF%' OR COLUMN_NAME LIKE '%GHI%' OR COLUMN_NAME LIKE '%JKL%' OR COLUMN_NAME LIKE '%MNO%') AND TABLE_NAME = 'TB1'
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Second query alter table by dropping columns:

ALTER TABLE TB1 DROP COLUMN DEF,DEF1,DEF2,GHI,GHI1,JKL,JKL1,MNO,MNO1

This is the function:

CREATE PROCEDURE dbo.DropColumns(@table_name NVARCHAR(128))
AS
BEGIN

    DECLARE @cmd NVARCHAR(MAX);
    DECLARE @colsOR NVARCHAR(MAX);
    DECLARE @cols NVARCHAR(MAX);

    -- filter used in WHERE clause (COLUMN_NAME LIKE '%REF%' OR ...)
    SET @colsOR = STUFF((SELECT ' OR ' + CONCAT('COLUMN_NAME LIKE ''%', CName, '%''') 
                         FROM TblMaster
                         WHERE FLAG = 1
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 4, '');

    -- columns returned separated by comma DEF,DEF1,DEF2,GHI,GHI1,JKL,JKL1,MNO,MNO1
    SET @cmd = 'SELECT @columns = STUFF((SELECT '','' + COLUMN_NAME 
                      FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE  (' + @colsOR + ') AND TABLE_NAME = ''' + @table_name + '''
                      FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''')'

    EXEC sp_executesql @cmd, N'@columns NVARCHAR(MAX) OUTPUT', @columns = @cols OUTPUT;

    SET @cmd = 'ALTER TABLE ' + @table_name + ' DROP COLUMN ' + @cols;

    EXEC sp_executesql @cmd;

    RETURN 0;

END
GO

✓

EXEC dbo.DropColumns @table_name = 'TB1';
GO

✓

--check results
SELECT TABLE_NAME, COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'TB1';
GO

TABLE_NAME | COLUMN_NAME
:--------- | :----------
TB1        | ABC        
TB1        | ABC1       
TB1        | ABC2       
TB1        | NOTEXIST   
TB1        | NOTEXIST1  
TB1        | NOTEXIST2  

dbfiddle here