#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8

    Search all columns in whole database


    Hi all I was wondering if it's possible to search all tables and all columns in a database. And return a table holding the tableName the column name and the cellValue

    So Im tyring something like this:
    SELECT tableName, colName, cellValue FROM eqdb_insite.dbo.ALL_TABLES WHERE cellValue LIKE "%x287%"

    Thanks!! (happy new year to all and r937!)
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by pg300
    Hi all I was wondering if it's possible to search all tables and all columns in a database.
    only with application logic (e.g. in php or asp, etc.)

    first, you need to query the information_schema views to pull out the table and column names

    then, your application code will use these to construct SELECT statements and execute them

    by the way, if you're looking for x287 with LIKE, there's no point in looking at numeric columns, right? so actually you don't want to search "all" columns, just some of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Ah I see. I actually had some trouble with the information schema when i tried in past. Can you please give me some examples of infomration schema queries.

    Yep in the case above I dont care about numeric. In some cases i only care about numeric. Does this make a difference?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by pg300
    Can you please give me some examples of infomration schema queries.
    soiteny!! nyuk nyuk

    hold on a sec ... *googles a bit* ... ah, here we are...
    The following command gives the list of columns of every table in the database, including their data types and widths. You can also use a WHERE clause, if you would like to deal with only one table.
    Code:
    select table_name,column_name, data_type, character_maximum_length as width 
    from information_schema.columns 
    order by table_name,ordinal_position
    source: http://www.aspfree.com/c/a/MS-SQL-Se...L-Server-2000/
    there's lots more where i found this, i.e. google

    Originally Posted by pg300
    Yep in the case above I dont care about numeric. In some cases i only care about numeric. Does this make a difference?
    yes, it does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Ah thanks so much so sorry I need to get better with google lol

    Ill play around with some application code like php. Thanks again r!

IMN logo majestic logo threadwatch logo seochat tools logo