To view the database physical file path

Method One: Use to query all database, apply to SQL2005 and above version.

Use [Master]

Go

/****** object: Table [dbo]. [Master] Script Date: 06/29/2009 11:11:01 ******/

--SA Login

Select a. [Name] Logical file name

, Case when charindex (' log ', B. [name]) >= 1

Then ' database log file-> ' + B. [Name]

Else ' database Master file-> ' + B. [Name] End physical file name

, B. Physical_name Physical path

From sys.databases A

Join Sys.master_files B on a. database_id= B. database_id

--the results of the drawings:

Mode two: Used to query all database, applicable to SQL2000 and above version.

/****** object: Table [dbo]. [Master] Script Date: 10/10/2010 10:11:01 ******/

--SA Login

Select

[dbid] Database ID

, case when Fileid = 1 THEN ' master data '

When Fileid = 2 THEN ' master log '

When Fileid between 2 and 65536 THEN ' log file ' + LTRIM (fileid-2)

End file Type

, Name logical file name

, [filename] physical path

, [status] database state

, Size database sizes

, the MAXSIZE database maximum value

, growth [File growth value/ratio]

From Sys. Sysaltfiles

--the results of the drawings:

Mode three: Used to query all database, applicable to SQL2005 and above version.

/****** object: Table [dbo]. [Master] Script Date: 10/23/2010 10:11:01 ******/

--SA Login

Select

database_id Database ID

, case when file_id = 1 THEN ' master data '

When file_id = 2 THEN ' master log '

When file_id between 2 and 65536 THEN ' log file ' + LTRIM (file_id-2)

When file_id = 65537 THEN ' file stream '

End file Type

, Name logical file name

, Physical_name Physical path

, State_desc the database state

, Size database sizes

, the Max_size database maximum value

, case when is_percent_growth = 1

Then LTRIM (growth) + '% '

else LTRIM (growth) + ' KB ' End file growth value

From Sys. Master_files

--the results of the drawings:

--Note: Definitions of related parameters in mode two and three

--max_size Database Maximum value parameter explanation:
0 = not allowed to grow;
-1 = file will grow until the disk becomes full;
268435456 = Log files will grow to a maximum size of 2 TB.
--If the upgraded database does not limit the log file size, the maximum size of the log file is reported to be-1.

Posted By:select left (' Claro ', 2) updated @09:26:10

Lable:sql