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

    Unanswered: list tables depending if column exists

    Hi there,

    Is there a quick way to list all the tables in a DB that contain a certain column name?

    Thanks

    S

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select name from sysobjects o
    where exists (select 1 from information_schema.columns c
    where o.name = c.table_name
    and column_name like '%column_name_patter%')
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2004
    Posts
    38
    Thanks for this!!!!!

  4. #4
    Join Date
    Jan 2004
    Posts
    38
    Thanks again!

    I've now got a list of all the tables containing a certain column name, but I'm trying to expand this further to narrow down the results to:

    all tables containing columnX and where columnX >= 10000 and <= 20000

    Any ideas how I can search on this criteria?

    Thanks I'd appreciate any input or advice.

    S

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll give you what you asked for... You'll have to decide if that is what you want (I suspect that it is not).
    Code:
    SELECT o.name
       FROM dbo.syscolumns AS c
       INNER JOIN dbo.sysobjects AS o
          ON (o.id = c.id)
       WHERE  c.name = 'whatever'
          AND c.name BETWEEN '10000' AND '20000'
    What I suspect that you really want is to check the values of the column in the tables themselves to see if there are rows in the table with column values between 10000 and 20000. That is a slightly more challenging request.

    -PatP

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select 'select * from ' + name + ' where ' + column_name + ' between 10000 and 20000' from sysobjects o inner join information_schema.columns c
    on o.name = c.table_name
    where column_name like '%column_name_patter%'

    Then you can execute the resulting series of SELECT statements. Or you can always put it into a cursor and do "EXEC ('...statement...)" on each row.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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