Post reply

Quering for tables based upon table and column name

  • October 25, 2007 at 10:57 pm

    #180615

    Is it possible in Query Analyzer to query a list within a database based upon criteria of table name and column name within tables? For example, if my tables all have names which include the year, say BRST2007 and within some of these tables there is a column called "native" can I query a list of those tables with 2007 in the table name and having a column called 'native'?

    Thanks all!

  • October 26, 2007 at 5:02 am

    #745640

    several ways, but information_schema is the recommended practice:

    select TABLE_NAME,COLUMN_NAME,* from

    INFORMATION_SCHEMA.COLUMNS

    WHERE COLUMN_NAME like '%native%'

    select OBJECT_NAME(id) as Table_Name,name as ColumnName from syscolumns where name like '%native%'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 26, 2007 at 7:10 am

    #745689

    Of course - once you HAVE the table name like Lowell pointed out - you're heading into Dynamic SQL land...

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 26, 2007 at 8:28 am

    #745747

    This worked great... thanks for sharing your expertise, and have a great weekend!

Viewing 4 posts - 1 through 4 (of 4 total)

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