Today at work I was looking at creating Database Snapshots in SQL Server. To do this successfully :> I needed a database's logical name and the path where it was. At this point, you might be asking, "logical name?" WTF? GOOD question! Don't worry, I'm getting to the extraction part in a sec.
Physical Name: the name of the file on the hard drive, the actual *.mdf or *.ldf file.
Logical Name: the unique name SQL Server uses to reference the db in t-sql scripts, please notice, this does not always match what you see in Management Studio's Database tree list.
Most of the time, these will be the same, but sometimes when you're moving dbs around, creating new ones for testing/debugging/development purposes, they get changed. The following illustrates this. There is a DB called ODNCStudyGroup and that's the name I see in Management Studio database list, but when t-sql requires the logical name, I need to use WebStudyGroup. Below is the sql and result sets after I run the sql.
Hhhmm there an easier way to get the Logical name? Yup. Look at the database properties.
Now that we know how to get the logical name in t-sql, we need to work on extracting the dbs filename and path. The trick to extracting the path and filename is to reverse the string, find the location of the slash, then doing some math manipulation and you're gold! That's it. Once I read the blog about using reverse, everything just fell into place. Next is the sql I used. I added in the filename for a bonus.
Why does this work? Magic! No, just kidding. The secret is in t-sql for string manipulation and getting the proper character indexes/positions to do the string math on. Once you reverse the string for the db path (since the table is a system table, you can pretty much trust the slash is a backwards slash and not have to do too much error detection/correction) taken together with the length of the string/field data, you have the index/position of the slash separating the path from the filename.
I hope you can use this little string manipulation trick. And if you have any others, please drop a comments! I would love to hear about. Now go grab a coffee and get coding! :>