Where can I find this in BOL?

Question

Post reply

Where can I find this in BOL?

Jeff Moden

SSC Guru

Points: 1004434
More actions
April 28, 2008 at 6:06 am

#189638

I'm still a newbie when it comes to some things in SQL Server 2005. I've recently come across a script with something strange in it... please see the highlighted code in the following...
SELECT es.Session_ID,
es.Host_Name,
es.Program_Name,
es.Client_Interface_Name,
es.Login_Name,
es.NT_Domain,
es.NT_User_Name,
ec.Client_Net_Address,
ec.Local_Net_Address,
ec.Connection_ID,
ec.Parent_Connection_ID,
ec.Most_Recent_SQL_Handle,
(SELECT Text FROM Master.sys.dm_Exec_SQL_Text(ec.Most_Recent_SQL_Handle)) AS SQLScript,
(SELECT DB_NAME(DBID) FROM Master.sys.dm_Exec_SQL_Text(ec.Most_Recent_SQL_Handle)) AS DatabaseName,
(SELECT OBJECT_NAME(ObjectID) FROM Master.sys.dm_Exec_SQL_Text(ec.Most_Recent_SQL_Handle)) AS ObjectName
FROM sys.dm_Exec_Sessions es
INNER JOIN sys.dm_Exec_Connections ec
ON ec.Session_ID = es.Session_ID
It would appear that the code in RED is acting as if it were part of a JOIN or WHERE clause. I've checked FROM, JOIN, WHERE, and Correlated SubQuery in BOL and can find no reference to this method... maybe I'm just missing it...
I'm looking for the BOL reference for this type of correlated sub-query where the correlation is done simply by including the correlation column in parenthesis after the table name in the FROM clause.
Thanks folks...
--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)

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

Never mind...

Master.sys.dm_Exec_SQL_Text is a system table-valued function... I thought it was a system view.

:blush:

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

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 2

Doggone it! I was getting excited that I might be able to tell you something rather than ask you something.

😛

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

Heh... thanks Grant... I'm just a wee bit embarrased that I didn't pick up on that before I posted the question. 😛 More COFFEE!:hehe:

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