Determine physical file names of database

Hi,
How do I determine the physical file names of an SQL Server database
using a query?
For example, I'm looking for a query that returns the following:
Logical Name Physical Name
ABC_Data C:\MSSQL7\data\ABC_Data.MDF
ABC_Log C:\MSSQL7\data\ABC_Log.LDF
George
Hi
use pubs
exec sp_helpfile
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George
|||You can use sp_helpdb 'YourDatabase'
You could also query sysfiles:
select name, filename
from sysfiles
-Sue
On Tue, 24 May 2005 13:41:17 +0200, George
<gtog@._no___spam_myrealbox.com> wrote:

>Hi,
>How do I determine the physical file names of an SQL Server database
>using a query?
>For example, I'm looking for a query that returns the following:
>Logical Name Physical Name
>---
>ABC_Data C:\MSSQL7\data\ABC_Data.MDF
>ABC_Log C:\MSSQL7\data\ABC_Log.LDF
>George
|||SELECT NAME,FILENAME FROM SYSFILES
exec SP_HELPDB <db>
"George" wrote:

> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George
>
|||Hi,
Execute the below query from master database:-
select db_name(dbid) as Database_name , name,filename from
master..sysaltfiles
Thanks
Hari
SQL Server MVP
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database using
> a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF ABC_Log
> C:\MSSQL7\data\ABC_Log.LDF
> George

Determine physical file names of database

Hi,
How do I determine the physical file names of an SQL Server database
using a query?
For example, I'm looking for a query that returns the following:
Logical Name Physical Name
---
ABC_Data C:\MSSQL7\data\ABC_Data.MDF
ABC_Log C:\MSSQL7\data\ABC_Log.LDF
GeorgeHi
use pubs
exec sp_helpfile
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George|||You can use sp_helpdb 'YourDatabase'
You could also query sysfiles:
select name, filename
from sysfiles
-Sue
On Tue, 24 May 2005 13:41:17 +0200, George
<gtog@._no___spam_myrealbox.com> wrote:

>Hi,
>How do I determine the physical file names of an SQL Server database
>using a query?
>For example, I'm looking for a query that returns the following:
>Logical Name Physical Name
>---
>ABC_Data C:\MSSQL7\data\ABC_Data.MDF
>ABC_Log C:\MSSQL7\data\ABC_Log.LDF
>George|||SELECT NAME,FILENAME FROM SYSFILES
exec SP_HELPDB <db>
"George" wrote:

> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George
>|||Hi,
Execute the below query from master database:-
select db_name(dbid) as Database_name , name,filename from
master..sysaltfiles
Thanks
Hari
SQL Server MVP
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database using
> a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF ABC_Log
> C:\MSSQL7\data\ABC_Log.LDF
> Georgesql

Determine physical file names of database

Hi,
How do I determine the physical file names of an SQL Server database
using a query?
For example, I'm looking for a query that returns the following:
Logical Name Physical Name
---
ABC_Data C:\MSSQL7\data\ABC_Data.MDF
ABC_Log C:\MSSQL7\data\ABC_Log.LDF
GeorgeHi
use pubs
exec sp_helpfile
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George|||You can use sp_helpdb 'YourDatabase'
You could also query sysfiles:
select name, filename
from sysfiles
-Sue
On Tue, 24 May 2005 13:41:17 +0200, George
<gtog@._no___spam_myrealbox.com> wrote:
>Hi,
>How do I determine the physical file names of an SQL Server database
>using a query?
>For example, I'm looking for a query that returns the following:
>Logical Name Physical Name
>---
>ABC_Data C:\MSSQL7\data\ABC_Data.MDF
>ABC_Log C:\MSSQL7\data\ABC_Log.LDF
>George|||SELECT NAME,FILENAME FROM SYSFILES
exec SP_HELPDB <db>
"George" wrote:
> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George
>|||Hi,
Execute the below query from master database:-
select db_name(dbid) as Database_name , name,filename from
master..sysaltfiles
Thanks
Hari
SQL Server MVP
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database using
> a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF ABC_Log
> C:\MSSQL7\data\ABC_Log.LDF
> George

Detect Picture/File Presence

I am linking to photographs/pictures of employees. Some employees have a
picture.gif file while others do not have a physical file present (i.e. John
Doe has not "jdoe.gif" in the file share) depending on whether their picture
has been taken or not. Can I detect the presence/abscence of this picture
using vb code or a reporting services expression and change to a
"notavailable.gif" if the picture is not there. I'm trying to get rid of the
ugly X that appears when the file is not there.Scott:
This is what I've done to resolve this issue. I added a filename field to
the table to store the name of the image. I default it to none.gif which is
really a transparent nothing image. If I do have an image for an employee
then the name goes into the field and in the report I just display whatever
image is in the field.
HTH
Richard
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:3D75745E-4332-4D27-A9DA-41D0A33B4BF7@.microsoft.com...
>I am linking to photographs/pictures of employees. Some employees have a
> picture.gif file while others do not have a physical file present (i.e.
> John
> Doe has not "jdoe.gif" in the file share) depending on whether their
> picture
> has been taken or not. Can I detect the presence/abscence of this picture
> using vb code or a reporting services expression and change to a
> "notavailable.gif" if the picture is not there. I'm trying to get rid of
> the
> ugly X that appears when the file is not there.

Designing Primary Key and Clustered Index and Performance

I have several tables where the clustered index (the
physical way the data is stored) is different from the
Primary Key (which is just a unique number). It seems to
me that this will help me the most, as the clustered
index supports my SELECT statements, and the Primary Key
column will support my UPDATE and DELETE statements. I
am very new to SQL Server. My question is this. Is
designing the tables this way ok to do, or is this not
how it should be done?
Second part of my question is, If what I am doing is
fine, then does it make a difference (performance wise)
if I have the Primary Key column the first column table
or about the seventh column in?
Thanks so much for your help.Depends on who you ask. Zealots will say that you should never, ever use an
arbitrary unique integer as a row identifier. I disagree. However,
whenever possible, even if you are using a unique identifier you should
attempt to set a 'natural' primary key based on uniqueness in your data, and
use this for your primary key rather than the row identifier. You can still
use the ID to make life simpler (e.g. passing back lists of rows to client
applications for singleton selection), but the primary key will help
maintain and validate the table's data.
As for location within the column list, it makes no difference where
anything is. Don't rely on the ordering of your column list. Always
specify explicit column lists in the order you want them for selects and
inserts.
"Nancy" <anonymous@.discussions.microsoft.com> wrote in message
news:052b01c3aee5$09ea4e00$a301280a@.phx.gbl...
> I have several tables where the clustered index (the
> physical way the data is stored) is different from the
> Primary Key (which is just a unique number). It seems to
> me that this will help me the most, as the clustered
> index supports my SELECT statements, and the Primary Key
> column will support my UPDATE and DELETE statements. I
> am very new to SQL Server. My question is this. Is
> designing the tables this way ok to do, or is this not
> how it should be done?
> Second part of my question is, If what I am doing is
> fine, then does it make a difference (performance wise)
> if I have the Primary Key column the first column table
> or about the seventh column in?
> Thanks so much for your help.|||Placing the clustered index on a column other than the primary key is often
wise.
You only get ONE clustered index per table so use it wisely (Which is what I
believe you are doing).
If you are new to SQL Server, welcome to the community.
I recommend starting with these 3 books:
1. Inside SQL Server 2000 by Kalen Delaney
2. Professional SQL Server 2000 Programming By Robert Vieira
3. Transact-SQL Programming by Kline
Cheers
Greg Jackson
Portland, OR