Get database name and file location in SQL Server 2005


 

SELECT

sd

.[name] AS [Database Name] 

, smf.[physical_name] AS [MDF Filename]

FROM

 

sys.master_files smf 

JOIN sys.databases sd ON sd.[database_Id] = smf.[database_Id]

WHERE

smf

.[physical_name] LIKE

‘%send%’

 

———————————————————————————————————————————

/* Get database name and file location in SQL Server 2005 with size */

DECLARE

@Files TABLE

 

(  

Name SYSNAME,  

FileName VARCHAR(256),  

Size BIGINT  

)  

INSERT

@Files

EXEC

 sp_msforeachdb ‘SELECT name, filename, size FROM ?.sys.sysfiles’

SELECT

 *

FROM

@Files

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: