Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    May 2009
    Posts
    8

    Unanswered: Run a SELECT query on 500+ tables

    Hi there,

    I have a MS SQL Server 2005 Express database with 500 or so tables in it. The field structure of each table is the same, including a timestamp field. I'm using EMS SQL Manager Lite for SQL Server to access the database. I would like to select all records in all tables that are older than a certain date. At the moment, I am doing something like:
    Code:
    SELECT * FROM table1 WHERE table1.tstamp <= '2007-12-31 23:59:59';
    to select the data from one table, but I can't see how to go through and get the data from all 500+ tables without typing out each table name. Surely there is a quicker way to do it!

    (NB: the table names are actually all quite long and descriptive - they aren't sequentially numbered like they are in this example.)

    Thanks for your help.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    select 'select * from ' + name + ' where blah blah ' from sys.objects where type='U'

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd throw in some square brackets just in case any of the tables are oddly named:

    select 'select * from [' + name + '] where blah blah ' from sys.objects where type='U'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that breaks if any of the names have square brackets in them.

    select 'select * from ' + quotename(name) + ' where blah blah ' from sys.objects where type='U'

  5. #5
    Join Date
    May 2009
    Posts
    8
    Hi all, thanks for your quick responses.

    Sorry for the wrong info, but it is actually an MSDE 2000 database, not MSSQL 2005 Express (I was offsite yesterday when I posted, so I wasn't able to check).

    It doesn't like sys.objects, error: Invalid object name 'sys.objects'.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    select 'select * from ' + quotename(name) + ' where blah blah ' from sysobjects where xtype='U'

  7. #7
    Join Date
    May 2009
    Posts
    8
    ah okay - thanks for your help!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by jezemine
    that breaks if any of the names have square brackets in them.

    select 'select * from ' + quotename(name) + ' where blah blah ' from sys.objects where type='U'
    Touche', but any idiot who includes square brackets as part of his naming convention deserves to have more than his database broken.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    lolz.


    asdf

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am suprised that you are assuming all of the tables are in the default schema. And that any other schemas (schemae?) would not have square brackets in their names. ;-)

  11. #11
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by shehaal
    .... At the moment, I am doing something like:
    Code:
    SELECT * FROM table1 WHERE table1.tstamp <= '2007-12-31 23:59:59';
    to select the data from one table, but I can't see how to go through and get the data from all 500+ tables without typing out each table name. Surely there is a quicker way to do it!

    (NB: the table names are actually all quite long and descriptive - they aren't sequentially numbered like they are in this example.)

    Thanks for your help.
    since you are trying to gather information from all tables how about this:
    exec sp_MSforeachtable "select * from ? where tstamp < '2008-01-01'"

    -- This is all just a Figment of my Imagination --

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    May I humbly recommend you use the full ISO syntax for your date times? reason - you run that on a British system (or one using British date format) and it will bomb.
    Code:
    '2007-12-31T23:59:59'
    You may not thank me today, or tomorrow, but one day it might save you a rather nasty bug
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2007
    Posts
    86
    for reusability

    use sysobjects to generate a union view .. use the view for selects

  14. #14
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    exec sp_MSforeachtable @command1=" select '?', * from ? where tstampcolumn<='2008blah'"

    as long as your timestamp columns are all the same name then this should work which was also recommended by tom i see now ugg, but i have already spent the effort giving this so i am posting it, ugg.

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally, I would avoid using sp_msforeachtable as it is undocumented. This means the method is not supported and could be dropped from future releases without notice.
    George
    Home | Blog

Posting Permissions

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