Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18

    Unanswered: Query for Tables in a Database...

    Good Morning

    Shopping for help writing a query for my VB Program to execute against
    SQL Server. Here is what I have so far:

    SELECT name
    FROM sysobjects
    WHERE (xtype = 'U')

    Is there a way to add to that query to get the list of tables that have the properties COST and PARTNUMBER?

    Thanks,
    Ed

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SELECT * 
      FROM INFORMATION_SCHEMA.Tables t
     WHERE EXISTS (SELECT * 
    		 FROM INFORMATION_SCHEMA.Columns c
    		WHERE t.TABLE_NAME = c.TABLE_NAME
    		  AND c.COLUMN_NAME IN ('Parts','Cost'))
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18
    thanks...

    It took me a bit to translate your guidance into what SQL Server wanted.
    I ended up with:

    select name from sysobjects t where exists( select * from syscolumns c where t.id = c.id
    and c.name in ('PartNumber', 'Cost'))
    order by name

    Thanks,
    Ed

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you just try and cut and paste the code?

    Look up INFORMATION_SCHEMA in Books online...

    You want to use those over the system tables.

    These are views, which use the system tables...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18
    IC what you mean now...

    Here is what we have now.

    SELECT distinct A.TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
    WHERE A.TABLE_NAME = B.TABLE_NAME
    and b.column_name in ('Partnumber', 'Cost')
    ORDER BY A.TABLE_NAME

    Thanks,
    Ed

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Obviously you found out that the join will produce dupTablenames (because of the columns)...why didn't you just use what I gave you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2003
    Location
    Valley City, OH
    Posts
    18
    the first time I tried it, no results were given. Thought I had to do some
    heeby geeby to get it ready for use for my database. But then I noticed
    the Parts column reference instead of Partnumber. Changed that and now it runs and returne the proper results.

    Thanks,
    ED

Posting Permissions

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