T-SQL basic Query

Question

Post reply

T-SQL basic Query

Shabba

SSCertifiable

Points: 5007
More actions
September 14, 2006 at 8:58 am

#116690

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.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Mark

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

sp_MSforeachdb

'use ?

exec sp_helpfile'

John

noeld SSC Guru Points: 96590 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

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)

Shabba SSCertifiable Points: 5007 More actions · Answer 4

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

John Mitchell-245523

SSC Guru

Points: 148809

September 15, 2006 at 2:55 am

#660628

Mark

By reading the posts on this site, I think!

John

Okafor Mr or Mrs. 500 Points: 596 More actions · Answer 6

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.

Shabba SSCertifiable Points: 5007 More actions · Answer 7

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.

Okafor Mr or Mrs. 500 Points: 596 More actions · Answer 8

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