T-SQL basic Query

  • Firstly, let me apologise for the question i'm about to ask!  It's soooo basic i'm sure it will be answered quickly.

    I have a SQL 2000 Server with approximately 40+ databases on it and it's in a right mess.  I would like to find out the physical location of both the .mdf & .ldf files as they would appear to have been installed on different drives.  So, i'm trying to tidy things up... the question is rather than input the following script for each server in Query Analyzer is there a quick way of doing this for all databases?

    use db1

    go

    sp_helpfile

    go

    use db2

    go

    sp_helpfile

    go

    etc, etc

    Thanks for any help you can give.

     

  • Mark

    This uses the undocumented (and unsupported) stored procedure sp_MSforeachdb:

    sp_MSforeachdb

    'use ?

    exec sp_helpfile'

    John

  • But it is pretty handy and it works on 2005 too

    By the way the syntax can be rewritten as :

    exec sp_MSforeachdb '?.dbo.sp_helpfile'


    * Noel

  • I've found the even Microsoft uses the "undocumented" features   Works for me!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks guys for all you help... this is exactly what I wanted.  Many Thanks!

    As a side issue how did you find out about these 'undocumented' features?

    Cheers,

    Mark

  • Mark

    By reading the posts on this site, I think!

    John

  • Mark,

    You say "I would like to find out the physical location of both the .mdf & .ldf files as tehy would appear to have been installed on different drives.  So, i'm trying to tidy things up..." 

    I would caution you and tell you that they most likely should be on different drives.  If possible you want to segregate your log and data files for both performance and recoverability.

    Just my two cents...

    Good luck.

  • Hi Okafor,

    I appreciate where you're coming from.  As a standard SQL server build we tend to split the data onto one mirrored pair and logs/backups onto another mirrored pair.  I was in a bit of a rush typing the initial forum post and made some obvious mistakes!  Although the majority of databases and log files are spilt onto different drives, the server in question has a number of databases and log files on the same drive (which goes against our standard SQL build).

    I just wanted to bring this particular SQL installation in line with our other servers.

    Sorry for the confusion.

     

     

  • Great to hear!  So many times I hear of data, logs and even backups on the same drives.  Makes me cringe!!! 

Viewing 9 posts - 1 through 8 (of 8 total)

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