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