April 17, 2015 at 11:25 am
Is there a way to just get the database physical names returned and not the full path without having to do string manipulation on the physical name using sys.database_files or sys.master_files? Appreciate it.
April 17, 2015 at 11:54 am
Nope, not that I am aware of. Not without using a sp_cmdshell or other non-tsql scripting tool
What's wrong with this?
SELECT
DbName = db.name,
FileType = CASE WHEN RIGHT(physical_name,3) IN ('mdf','ndf') THEN 'data file' ELSE 'log file' END,
physical_name = RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1)
FROM sys.databases db
JOIN sys.master_files mf ON db.database_id = mf.database_id
... or some variation of it?
-- Itzik Ben-Gan 2001
April 17, 2015 at 12:20 pm
Nothing wrong with it. This works too awesome thank you!
April 17, 2015 at 12:30 pm
Excellent, glad that worked for you.
-- Itzik Ben-Gan 2001
April 20, 2015 at 3:19 pm
Alan.B (4/17/2015)
Nope, not that I am aware of. Not without using a sp_cmdshell or other non-tsql scripting toolWhat's wrong with this?
SELECT
DbName = db.name,
FileType = CASE WHEN RIGHT(physical_name,3) IN ('mdf','ndf') THEN 'data file' ELSE 'log file' END,
physical_name = RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1)
FROM sys.databases db
JOIN sys.master_files mf ON db.database_id = mf.database_id
... or some variation of it?
One urgent suggestion/correction:
Use column mf.type_desc to determine the FileType, not assume based on the file name. While mdf and ndf are standard, they are not required; for all SQL cares, you could use .qqq as the file extension or not have a file extension (I've actually seen that used for data files).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 20, 2015 at 3:24 pm
ScottPletcher (4/20/2015)
Alan.B (4/17/2015)
Nope, not that I am aware of. Not without using a sp_cmdshell or other non-tsql scripting toolWhat's wrong with this?
SELECT
DbName = db.name,
FileType = CASE WHEN RIGHT(physical_name,3) IN ('mdf','ndf') THEN 'data file' ELSE 'log file' END,
physical_name = RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1)
FROM sys.databases db
JOIN sys.master_files mf ON db.database_id = mf.database_id
... or some variation of it?
One urgent suggestion/correction:
Use column mf.type_desc to determine the FileType, not assume based on the file name. While mdf and ndf are standard, they are not required; for all SQL cares, you could use .qqq as the file extension or not have a file extension (I've actually seen that used for data files).
Good catch. 😉
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply