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...
>