none
Comparing tables RRS feed

  • Question

  • Hi

    How can I get a list of columns that are in table A but are not in table B? 

    Thanks

    Regards


    • Edited by Y a h y a Saturday, August 26, 2017 3:25 PM
    Saturday, August 26, 2017 3:25 PM

Answers

  • SELECT a.*
    FROM   sys.columns a
    WHERE  object_id object_id('tblA')
      AND  NOT EXISTS (SELECT *
                       FROM   sys.columns b
                       WHERE  a.name = b.name
                         AND  b.object_id = object_id('tblB'))

    Here I am only considering the name of the columns, so if there is a column XYZ in both tables with completely different data types, it will not be listed.

    • Marked as answer by Y a h y a Saturday, September 23, 2017 10:56 PM
    Saturday, August 26, 2017 7:28 PM

All replies

  • refer following

    https://stackoverflow.com/questions/25978882/select-combination-of-columns-from-table-a-not-in-table-b


    Mssql installation on Centos

    Saturday, August 26, 2017 3:30 PM
  • SELECT a.*
    FROM   sys.columns a
    WHERE  object_id object_id('tblA')
      AND  NOT EXISTS (SELECT *
                       FROM   sys.columns b
                       WHERE  a.name = b.name
                         AND  b.object_id = object_id('tblB'))

    Here I am only considering the name of the columns, so if there is a column XYZ in both tables with completely different data types, it will not be listed.

    • Marked as answer by Y a h y a Saturday, September 23, 2017 10:56 PM
    Saturday, August 26, 2017 7:28 PM
  • Hi,

    Check this out:

    create table #test1 
    (
    Item int
    )
    
    insert into #test1 values (1),(2),(3)
    
    create table #test2
    (
    Item int
    )
    
    insert into #test2 values (1),(4)
    
    --Method1:
    select * from #test1
    except
    select * from #test2
    
    --Method2:
    select * from #test1 
    where Item not in (select Item from #test2)

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Xi Jin Thursday, August 31, 2017 8:08 AM
    • Marked as answer by Y a h y a Friday, September 22, 2017 6:18 AM
    • Unmarked as answer by Y a h y a Saturday, September 23, 2017 10:55 PM
    Monday, August 28, 2017 1:35 AM