Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Unanswered: selecting tables that meet certain criteria

    Hi there,

    Is there a quick way to select all the tables in the DB that don't have certain column in the table, so for example getting a list of all tables that don't have columns: A, B or C?

    Thanks

    S

  2. #2
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    It's easy... just cross the sysobject table and the syscolumns table of your database.

    Paulo

  3. #3
    Join Date
    Jan 2004
    Posts
    38
    Sorry, I'm quite new to SQL, could you explain further?

    Thanks!!!

  4. #4
    Join Date
    Feb 2002
    Location
    Assam, India
    Posts
    55
    If you want names of the tables that do not have the
    columns say "author_name" or "city" then you could
    try the following piece of code

    select distinct a.name as table_name
    from sysobjects a, syscolumns b
    where a.xtype= 'U'
    and a.id = b.id
    and b.name not in ( 'author_name', 'city')


    Roshmi Choudhury

  5. #5
    Join Date
    Jan 2004
    Posts
    38
    Thank you, that was very helpful!!!!

Posting Permissions

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