March 3, 2018 at 10:15 am
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!!
March 3, 2018 at 11:42 am
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;
March 5, 2018 at 6:35 am
ALTER TABLE just doesn't work the way you might want it to, and if it did, would probably be far too dangerous. ALTER TABLE has to have the specific column name presented in the actual text of the command, and you need what's called "dynamic SQL" to do this kind of thing in an automated fashion. Given the example code you've provided, you would end up dropping any column that had any of the string values selected, anywhere within the column names, and that is a rather broad brush to be using to drop columns with. However, to answer the question, you need to start constructing the needed commands separately for each matching column that you find. To give yourself some protection against such broad criteria accidentally identifying a column that actually shouldn't be deleted, I'd recommend that you generate a separate row for each ALTER TABLE TBn DROP COLUMN coln that is needed, as a string containing the specific ALTER TABLE command to drop that specific column. You could then run the query that produces however many rows as there are columns to drop, and then copy and paste that data into an e-mail to a fellow developer to have them quickly review your commands to see that the proper list of columns has been generated, and only after such verification, paste the commands back into SSMS to be run.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 5, 2018 at 8:06 am
Completely agree with Steve
If you are looking for script you may use something like below
-- drop table #colstodrop
SELECT
DISTINCT '%' + CName +'%' cols INTO #colstodrop
FROM TblMaster WHERE flag = 1
SELECT 'Alter table ' + t.name + ' Drop column ' + c.name as script , identity (int) as idcol
INTO #tmp2
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN #colstodrop d ON c.name LIKE cols
WHERE t.name in ('TB1','TB2')
DECLARE
@cnt INT = (select max(idcol) from #tmp2)
DECLARE @tempcnt INT = 1
DECLARE
@sql NVARCHAR(400) = ''
WHILE
@tempcnt <= @cnt
BEGIN
SET
@SQL = (SELECT script FROM #tmp2 WHERE idcol = @tempcnt )
PRINT @SQL
--EXECUTE SP_EXECUTESQL @SQL -- this will drop the column
SET @SQL = ''
SET @tempcnt = @tempcnt+ 1
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply