Post reply

Find all the rows from all tables in a DB for a specific column

  • March 28, 2014 at 4:15 am

    #283946

    Hello,

    First of all, I couldn't find any existing thread talking of my "problem", based on my search...

    Then, I have a DB with some tables and, on certain tables, i've a column named "ID_COMPUTER".

    I need to find all the rows where id_computer=<specific_value> from all the tables of my database, when column "ID_COMPUTER" exists.

    Pseudo-code would be: select * from db.* where id_computer=<specific_value>

    Has anyone any idea how to write this ?

    Thanks in advance.

    Have a nice day.

    Franck.

  • March 28, 2014 at 5:53 am

    #1701042

    You can query the [sys].[columns] table for the specific column name and translate the corresponding object_id to the table name. You can build a dynamic SQL statement inside that query. Finally you can copy/paste the generated dynamic SQL to a query window and execute it or you can build a cursor/loop around it to execute it automatically.

    select

    OBJECT_NAME(object_id) as table_name

    , 'SELECT * FROM [' + OBJECT_NAME(object_id) + '] WHERE [ID_Computer] = ''{value}'' ' as SQL_statement

    from sys.columns

    where name = 'ID_Computer'

    order by object_id

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • March 28, 2014 at 6:42 am

    #1701060

    Indeed, it was way simpler than what I was trying to write, haha 🙂

    Thank you !

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

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