Menu
I have a table from which I need to drop multiple columns that follow a pattern. Say columns names in my table are (A1, A2, A3, B1, B2, B3) and I need to drop all columns that starts with a B (B1, B2, B3). I was able to pull those in a separate query but still not able to run a drop statement on the output. I really appreciate the help. Please note that I am not allowed to deploy scripts with cursors. So pure SQL is preferable.
Thanks,
926
You can't delete a column that has PRIMARY KEY or FOREIGN KEY constraints or other dependencies except when using the Table Designer in SSMS. When using Object Explorer or Transact-SQL, you must first remove all dependencies on the column.
Right-click on the table and go to Design. It shows all column of a particular table. Right-click on the left-hand side of a column and you get option Delete Column. Click on it to delete a column.
The information_schema. COLUMNS table holds all information about the columns in your MySQL tables. To exclude columns, you use the REPLACE() and GROUP_CONCAT() functions to generate the column names you wish to include in your SELECT statement later.
Try this. Use Dynamic SQL to generate Drop scripts of columns which is following specific pattern
DECLARE @sql NVARCHAR(max)=''
SELECT @sql += ' Alter table ' + TABLE_NAME + ' DROP COLUMN ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename'
AND TABLE_SCHEMA = 'SchemaName'
AND TABLE_CATALOG = 'DatabaseName'
AND COLUMN_NAME LIKE 'B%'
--print @sql
EXEC Sp_executesql @sql
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
