Get Physical File Path of Any Database

Question

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 master 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?

Answer 1

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)

Answer 2

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.