How do I delete Columns from a table if not another table using SQL?

Question

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 T
       INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON
             T.TABLE_NAME=C.TABLE_NAME
    WHERE    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!

Possible duplicate of [How do I drop a table column in SQL Server 2012?](https://stackoverflow.com/questions/13491554/how-do-i-drop-a-table-column-in-sql-server-2012) — demo, Apr 22 '19 at 16:19
If there are no outer dependencies, why not just use `ALTER TABLE "Table1" DROP "column1";` ? — Dortimer, Apr 22 '19 at 16:20
I know how to drop a column, but there are actually about 150 columns in one table and 175 in the other which makes this a more complex situation. If I could get a list where they don't match that would help. — Aron Schor, Apr 22 '19 at 16:20

Gen Wan · Answer 1 · 2019-04-22T16:54:12.610

1

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 want

declare @sql nvarchar(max)
set @sql = 'alter table Table1 drop column ' + @column
execute (@sql)

Let me know if you have any questions.

edited Apr 22 '19 at 16:54

answered Apr 22 '19 at 16:34

Gen Wan

1,979
2
12
19

Thanks Gen Wat...what do I put for the (Select....) I tried: SELECT c.Name as Field_Name FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id WHERE O.Name = 'Table2' But I get error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – Aron Schor Apr 22 '19 at 16:48
@AronSchor You mentioned you can get the column name. So it should be like 'select top 1 column_name from table join ......'. Please notice that you can only select one column_name in this case. Use the top clause. If you need more help, please post the select statement which you can get the column name. – Gen Wan Apr 22 '19 at 16:50
@AronSchor The reason for your error is you can only store one string into the variable. This is not a table variable. So like I said, your select statement should only return a 1*1 table which only includes your table name. – Gen Wan Apr 22 '19 at 16:57
Thanks Gen Wan...This might be a bit advanced, I'll use the other method to get a list of where they don't match. Appreciate the help! – Aron Schor Apr 22 '19 at 17:32

score 1 · Accepted Answer · answered Apr 22 '19 at 16:49

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 ALL
    select [object_id], name from sys.all_columns where [object_id] = (select [object_id] from sys.tables where name = 'Table2')
) o
group by o.name
having count([object_id]) < 2

You can use the data from this table to make a separate "drop column" query.

How do I delete Columns from a table if not another table using SQL?

2 Answers2