Object_name of object in one database while running in another..

It seems for this to work, I need to be running in the context of an
existing database to get the object name or else it returns null.
select object_name(s2.objectid) session_current_procedure_name
from sys.dm_exec_requests req
cross apply sys.dm_exec_sql_text(req.sql_handle) s2
So how can I get the object name of the object irrespective of which
database I run this query from ?
Hassan
Since you can have the same object_id in two different databases, SQL Server
needs to know which database to look in.
If you are running SQL 2005 SP2, you can add a second parameter to
object_name which provides the database_id:
SELECT object_name(object_id, db_id)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
> It seems for this to work, I need to be running in the context of an
> existing database to get the object name or else it returns null.
> select object_name(s2.objectid) session_current_procedure_name
> from sys.dm_exec_requests req
> cross apply sys.dm_exec_sql_text(req.sql_handle) s2
> So how can I get the object name of the object irrespective of which
> database I run this query from ?
>
|||Nice..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
> Hassan
> Since you can have the same object_id in two different databases, SQL
> Server needs to know which database to look in.
> If you are running SQL 2005 SP2, you can add a second parameter to
> object_name which provides the database_id:
> SELECT object_name(object_id, db_id)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OfAV7vcTIHA.3940@.TK2MSFTNGP05.phx.gbl...
>
|||I thought so... I've been asking for this feature for years, since Sybase
added it in their System 10. Finally, I made an official request through
Connect, and Microsoft responded to that. So I am happy to take all the
credit for this one!
;-)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Nice..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTO8$1cTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>
|||I am sure there's a LOT more in the sql server world for which you can take
credit!! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I thought so... I've been asking for this feature for years, since Sybase
>added it in their System 10. Finally, I made an official request through
>Connect, and Microsoft responded to that. So I am happy to take all the
>credit for this one!
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eQdytAdTIHA.3916@.TK2MSFTNGP02.phx.gbl...
>
|||The question is what happens when Kalen retires ? We all know she cant do
this forever..
Kalen have you ever thought about how long you are going to do this ?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqohil8ca9sdb@.corp.supernews.com...
>I am sure there's a LOT more in the sql server world for which you can take
>credit!! :-)
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uP2ELyjTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||There are so many terrific people answering questions here now, no one would
even realize it if I disappeared quietly into the sunset...
A Mexican beach sounds nice, right about now...
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:eP6syqyTIHA.1212@.TK2MSFTNGP05.phx.gbl...
> The question is what happens when Kalen retires ? We all know she cant do
> this forever..
> Kalen have you ever thought about how long you are going to do this ?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqohil8ca9sdb@.corp.supernews.com...
>
|||It kinda sounds good but it's still nice to see you around here ;)
Ekrem nsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OEe%23TC0TIHA.4360@.TK2MSFTNGP06.phx.gbl...
> There are so many terrific people answering questions here now, no one
> would even realize it if I disappeared quietly into the sunset...
> A Mexican beach sounds nice, right about now...
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eP6syqyTIHA.1212@.TK2MSFTNGP05.phx.gbl...
>