Two tables, like columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Two tables, like columns

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-31 : 13:32:21

I have two tables. They share a certain number of columns alike, exactly alike. but table b has columns that table a does not. Is there a way to script the dropping of all columns from table b that are not in table b, or do i have to do an Alter Table statement for EACH column i want to drop? If that is the case, is there a way to get SQL to tell me which columns exist in a that do not exist in b?

thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-08-31 : 13:47:40

I wouldn't execute this code, but you could use it to generate code that you can cut and paste and inspect.

select 'ALTER TABLE TABLEA ' + 'DROP COLUMN ' +COLUMN_NAME
from information_schema.columns a
where a.table_name = 'TableA'
and not exists

(select *
from information_schema.columns b
where b.table_name ='TableB'
and a.column_name = b.column_name
)

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-31 : 13:49:12

[code]SELECT column_name FROM INFORMATION_SCHEMA.[COLUMNS] c
WHERE c.TABLE_NAME = 'B'
EXCEPT
SELECT column_name FROM INFORMATION_SCHEMA.[COLUMNS] c
WHERE c.TABLE_NAME = 'A'[/code]To drop, run this and copy and execute the results of the query[code]
;WITH cte AS
(
SELECT column_name FROM INFORMATION_SCHEMA.[COLUMNS] c
WHERE c.TABLE_NAME = 'B'
EXCEPT
SELECT column_name FROM INFORMATION_SCHEMA.[COLUMNS] c
WHERE c.TABLE_NAME = 'A'
)
SELECT 'ALTER TABLE B DROP COLUMN '+ column_name FROM cte;[/code]

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-31 : 13:49:31

cool. i'll add it to my growing collection and give it a spin on a couple of temp tables. thanks

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-31 : 14:09:25

Sorry Jim, but Sunita wins this round. That was wicked-slick. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources