Post reply

comparing table schemas - query to return columns

  • April 29, 2010 at 10:21 am

    #138726

    Hi.

    I need to write some sql to compare the columns in two tables in two databases and return a list I can use to later create a sql statement from each column list.

    So far I have this:

    SELECT Table1.[name] AS Table1Columns, Table2.[name] AS Table2Columns

    FROM DB1.dbo.syscolumns Table1

    FULL OUTER JOIN DB2.dbo.syscolumns Table2 ON Table1.[name] = Table2.[name]

    WHERE Table1.ID = (SELECT id FROM DB1.dbo.sysobjects WHERE name='MyTable')

    AND Table2.ID = (SELECT id FROM DB2.dbo.sysobjects WHERE name='MyTable')

    This runs, but returns a list where the columns appear in both tables. I need to have NULLs in Table1Columns where there is no matching column in Table2Columns, and vice-versa.

    Can anyone help?

    Thanks.

  • April 29, 2010 at 10:41 am

    #1159888

    Refactoring that query a little bit:

    ;WITH

    T1 (Table1Columns) AS

    (SELECT name FROM DB1.dbo.syscolumns WHERE OBJECT_NAME(id)='Table1'),

    T2 (Table2Columns) AS

    (SELECT name FROM DB2.dbo.syscolumns WHERE OBJECT_NAME(id)='Table2')

    SELECT *

    FROM T1

    FULL OUTER JOIN T2 ON T1.Table1Columns = T2.Table2Columns

    Note: If OBJECT_NAME doesn't work out because of the cross database stuff, you can use the method you were using before. The idea is to grab the column lists separately.

    You had criteria in the WHERE clause, which turned your full outer join into an inner join (thus eliminating the nulls).

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • April 30, 2010 at 1:38 am

    #1160247

    That worked great, thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply