Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115

    Unanswered: Comparing two different databases

    Hi all,
    Sql server 7

    i have two databases best and books.Most of the tables present in best are in books except some.
    i have been given a task to list out the tables and columns of which are present in best database and not in books database.

    Pls suggest me the simplest and quickest way to do this.
    this is very urgent.

    waiting for reply.
    TIA
    Adil

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd just run selects from sysobjects and syscolumns, which are SQL Server's method of storing object definitions. Others on this forum will probably suggest selecting from the SCHEMA tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check LEFT OUTER JOIN in BOL, this should be very trivial (hint: ...no, t's too trivial )

  4. #4
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115
    hi

    thnks for reply

    can u tell me as to how i can select columns of a particular table using syscolumns


    thansks once again

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by aadil
    hi

    thnks for reply

    can u tell me as to how i can select columns of a particular table using syscolumns


    thansks once again
    You could use linked servers. Query below will return list of tables from server1 if these are no the same column(s) on server2. Checking for object owner also is included.

    select 'User table ['+su.name+'.'+so.name+'] does not have column ['+sc.name+']'
    from server1.dbo.sysobjects so
    join server1.dbo.syscolumns sc on sc.id=so.id
    join server1.dbo.sysusers su on su.uid=so.uid
    where so.xtype='U'
    and exists(select 'ok' from server2.dbo.sysobjects r
    join server2.dbo.sysusers sur on sur.uid=r.uid and sur.name=su.name
    where xtype='U' and r.name=so.name)
    and not exists(select 'ok' from server2.dbo.sysobjects sor
    join server2.dbo.sysusers sul on sul.uid=sor.uid and sul.name=su.name
    join server2.dbo.syscolumns scr on scr.id=sor.id
    where sor.xtype='U' and sor.name=so.name and scr.name=sc.name)'

Posting Permissions

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