Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSSQL 2K8 - Selecting columns from table a not in table b

Posted on 2015-02-19
5
Medium Priority
?
58 Views
Last Modified: 2015-02-19
Good Morning,

I'm trying to find column names in Table A that don't exist in Table B.  Just the names of the columns.

Thanks!
0
Comment
Question by:ttist25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40619129
<knee-jerk reaction>
SELECT a.name
FROM (
   SELECT name
   FROM sys.columns
   WHERE OBJECT_NAME(object_id) = 'Table A') a
LEFT JOIN (
   SELECT name
   FROM sys.columns
   WHERE OBJECT_NAME(object_id) = 'Table B') b ON a.name = b.name
WHERE b.name IS NULL

Open in new window

0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40619131
You can use either the SYS tables or INFORMATION_SCHEMA views to retrieve the column names.
If you run a query for both tables, you can use the EXCEPT keyword to exclude columns from your first query that are in your second query.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40619193
   SELECT name
   FROM sys.columns
   WHERE OBJECT_NAME(object_id) = 'Table A'
   EXCEPT
   SELECT name
   FROM sys.columns
   WHERE OBJECT_NAME(object_id) = 'Table B'

Open in new window

0
 
LVL 1

Author Closing Comment

by:ttist25
ID: 40619222
Thanks Jim!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40619226
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question