CODEWITHSUNDEEP
sql
Aron Schor
Aron Schor

Reputation: 27

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

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!

Upvotes: 0

Views: 855

Answers (2)

Gen Wan
Gen Wan

Reputation: 2009

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.

Upvotes: 1

wenzzzel
wenzzzel

Reputation: 682

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.

Upvotes: 1

Related Questions