Finding the database

Question

Finding the database

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
December 5, 2018 at 8:59 pm

#77292

Comments posted to this topic are about the item Finding the database

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72498 More actions · Answer 1

Nice question, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

edwardwill SSCertifiable Points: 5790 More actions · Answer 2

I hadn't come across this function, but I confess that I struggle to understand the utility of it. If you amend the SELECT statement thus

SELECTPARSENAME('MyDB.dbo.OrderLine', 3)

it returns MyDB. But then you knew that already, because you'd typed it in! So in what circumstances might this return something you didn't already know ...

Toreador SSChampion Points: 11358 More actions · Answer 3

it also makes no attempt to validate the string you pass in, so
SELECT PARSENAME('x.y.z', 3)
will always return 'x'
ie it's a simple string-parsing method.

Surely if you want to know the database name you just run
SELECT DB_NAME()

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

edwardwill - Thursday, December 6, 2018 2:14 AM
I hadn't come across this function, but I confess that I struggle to understand the utility of it. If you amend the SELECT statement thus
SELECTPARSENAME('MyDB.dbo.OrderLine', 3)
it returns MyDB. But then you knew that already, because you'd typed it in! So in what circumstances might this return something you didn't already know ...

The intended usage is a bit questionable to me as well. In fact I have used it for the intended purposes exactly never. It is however useful. You can leverage it as a very limited string splitter. I have used it to parse emails into multiple components. You can do something similar for URLs or other strings you need to parse. The limitation is a max number of 4 elements.

Here is an example.

declare @Email varchar(100) = 'sean.lange@somewhere.com'

--First we replace any existing periods with a character sequence not found in (any reasonable) email address because PARSENAME uses the period as the delimiter SET @Email = replace(replace(@Email, '.', '^%^'), '@', '.')

--We need to undo the replace to restablish the original periods select UserName = replace(PARSENAME(@Email, 2), '^%^', '.') , DomainName = replace(PARSENAME(@Email, 1), '^%^', '.')

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 5

nice question
thanks

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 6

I'm not sure what this function is useful for, but I'm sure someone found a use. The best use so far is splitting IP addresses.

Ray Herring SSCertifiable Points: 5533 More actions · Answer 7

BOL does not help much either.
It implies that the function looks the "object" up (like searching sys.objects) and the really does parse and object name when
actually all it does is parse a string with up to 4 parts using '.' as the separator.
It's a nice that you can use a variable/column for parameter 1.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

MS has many system stored procedures that us it so you can pass in 'somenondboschemaname.sometablename'. For example, sp_rename uses it and you can pass in up to a 4 part name. I've used it in a similar manner. I've also used it for splitting IP addresses and CSVs/TSVs with up to 4 elements.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)