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.

DB has many tables.
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);

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)

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;

Please tag your DBMS (version included)

McNets
– McNets

2018-03-03 17:12:42 +00:00
Commented Mar 3, 2018 at 17:12 — McNets
– McNets, Commented Mar 3, 2018 at 17:12

McNets · Accepted Answer · 2018-03-03 20:09:03Z

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

Thanks a lot... it worked! Appreciate your help! Additionally I want to delete the Columns in table TB1/TB2 that have NULL in all rows and the Column Name is not in TblMaster. Is there a quick way to do this? So I want to delete NOTEXIST, NOTEXIST1 and NOTEXIST2 because they are all NULL and they do not exist in TblMaster. This could be a separate query and does not have to be in the above solution. Thanks again!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related