Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2014
    Posts
    1

    Red face Unanswered: Checking columns in two similar tables

    Can u help me with this query?

    For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
    There is only one column extra in _dup tables i.e.,idn column.
    Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

    Thanks in advance..,

    Gautham

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Create a temp table to hold results of a query on syscolumns for both tables. Loop through each temp table using t-sql query and compare columns. Produce results of missing columns.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd start with something like:
    Code:
    SELECT a.COLUMN_NAME
       FROM            INFORMATION_SCHEMA.COLUMNS AS a
       FULL OUTER JOIN INFORMATION_SCHEMA.COLUMNS AS b
          ON (b.TABLE_CATALOG = a.TABLE_CATALOG
          AND b.TABLE_SCHEMA  = a.TABLE_SCHEMA
          AND b.TABLE_NAME    = a.TABLE_NAME + 'hx'
          AND b.COLUMN_NAME   = a.COLUMN_NAME)
       WHERE  b.COLUMN_NAME IS NULL
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Do you know that the purpose of databases, even before RDBMS, was to remove redundancy in our data. Why are you putting back at great expense?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Celko View Post
    Do you know that the purpose of databases, even before RDBMS, was to remove redundancy in our data. Why are you putting back at great expense?
    I have to admit that I had exactly the same thought! I'm going to guess that the second table is an "archive" for history, and that the added column is a date... At least I can rationalize that usage!

    I meant to call you at New Years. Since I didn't get a call from you either, I'll assume that you didn't need bail this year.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I meant to call you at New Years. Since I didn't get a call from you either, I'll assume that you didn't need bail this year.
    Nah! I had credits left over on my account from last year

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I normally always go to EXISTS/NOT EXISTS for these type questions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •