Get Physical File Path of Any Database

I am trying to get the path without the filename from any database. I have this so far:

declare @db_name varchar (50)

SELECT @db_name = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'master') AND type_desc = 'ROWS'

set @db_name = REVERSE(RIGHT(REVERSE(@db_name),(LEN(@db_name)-CHARINDEX('\', REVERSE(@db_name),1))+1))

print @db_name

It works when I check my ordinary databases but when I tried it on the mas开发者_StackOverflowter database. I get this:

C:\Program Files\Microsoft SQL Server\

However, the correct path is:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\

Why did it miss out the rest of the path?

Thanks all for any help?

Your variable datatype is too small.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ is 72 characters.

Try declare @db_name varchar (200)

because @db_name is declared with a length of 50, so anything beyound that gets truncated.

Increase the size of @db_name and the problem is solved.

Your code should take into account that another filegroups of the same database could not be in the same path. So i propose that you add the checking:

...AND (data_space_id = 1)

for gathering the file path of the PRIMARY file group. The code would end this way:

CREATE FUNCTION DB_PATH ( @database_name NVARCHAR(100) )
RETURNS NVARCHAR(500)
AS
BEGIN
  DECLARE @database_path NVARCHAR(500) = ''
  DECLARE @database_dir  NVARCHAR(500) = ''

  SELECT @database_path = physical_name FROM sys.master_files 
    WHERE database_id = DB_ID(@database_name) AND (data_space_id = 1) AND type_desc = 'ROWS'

  SET @database_dir = REVERSE(RIGHT(REVERSE(@database_path),(LEN(@database_path)-CHARINDEX('\', REVERSE(@database_path),1))+1))

    RETURN @database_dir
END
GO

And you can use it this way:

SELECT DB_PATH(N'master')
GO