Let's say
Table1 has columns: Column1 Column2 Column3
Table2 has columns: Column2 Column3 Column4
I want Column1 to be deleted because it's not in Table2.
I am guessing I need to a JOIN and then delete from that. I did some searching and found this article:How can I get column names from a table in SQL Server?
I tried:
SELECT T.TABLE_NAME AS 'TABLE NAME',C.COLUMN_NAME AS 'COLUMN NAME'FROM INFORMATION_SCHEMA.TABLES TINNER JOIN INFORMATION_SCHEMA.COLUMNS C ONT.TABLE_NAME=C.TABLE_NAMEWHERE T.TABLE_TYPE='BASE TABLE'AND T.TABLE_NAME LIKE 'T'but I can only get the Column names to show for one Table. I tried modifying it with no luck, and of course I need to delete as well. Even if I could get a list of columns that don't match would help. I am no SQL expert but that's as far as I got. Any help would be appreciated. Thanks!
Best Answer
I've made a simple query that checks what column names both tables are containing and then counts the number of occurences of each name. It then shows the columns that appear less than two times i.e. the ones that only appears in one of the two tables.
select name from (select [object_id], name from sys.all_columns where [object_id] = (select [object_id] from sys.tables where name = 'Table1')UNION ALLselect [object_id], name from sys.all_columns where [object_id] = (select [object_id] from sys.tables where name = 'Table2')) ogroup by o.namehaving count([object_id]) < 2You can use the data from this table to make a separate "drop column" query.
You need a dynamic query in this case because you build your drop statement while you are running the select statement to get the column name.
declare @column varchar(max)set @column = (select............)-- Print @column -- Use this to check if the column name is what you wantdeclare @sql nvarchar(max)set @sql = 'alter table Table1 drop column ' + @columnexecute (@sql)Let me know if you have any questions.