SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding the database


Finding the database

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)

Group: Administrators
Points: 620408 Visits: 21261
Comments posted to this topic are about the item Finding the database

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63475 Visits: 8218
Nice question, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
edwardwill
edwardwill
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3993 Visits: 550
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
Toreador
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10116 Visits: 8576
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
Sean Lange
SSC Guru
SSC Guru (259K reputation)SSC Guru (259K reputation)SSC Guru (259K reputation)SSC Guru (259K reputation)SSC Guru (259K reputation)SSC Guru (259K reputation)SSC Guru (259K reputation)SSC Guru (259K reputation)

Group: General Forum Members
Points: 259897 Visits: 19613
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Shayn Thomas
Shayn Thomas
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4278 Visits: 821
nice question
thanks

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
Steve Jones
Steve Jones
SSC Guru
SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)

Group: Administrators
Points: 620408 Visits: 21261
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ray Herring
Ray Herring
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4515 Visits: 720
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
Jeff Moden
SSC Guru
SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896593 Visits: 48275
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search