Get the database

SQL Server Administration
1

Hello, I have the following which gives me what I need minus the database name. I know how to get it when I am looking for blocking but that is not what I want. I just am looking for the database a session is connected to.

SELECT Con.session_id AS SessionID , Cses.host_name AS HostName , Cses.login_name AS LoginName , Cses.program_name AS ProgramName , (-- Query gets text as XML for all queries SELECT [text] AS [text()] FROM sys.dm_exec_sql_text(Con.most_recent_sql_handle) FOR XML PATH(''), TYPE ) AS CodeText , Con.connect_time AS ConnectTime , Con.last_read AS LastRead , 'connection database code' AS DatabaseName FROM sys.dm_exec_connections Con INNER JOIN sys.dm_exec_sessions Cses ON Con.session_id = Cses.session_id ORDER BY LoginName ;
Thank you,
DJJ

2

have you tried sp_whoisactive? (google it)

3

I've found that the old view sys.sysprocesses does a better job of identifying the current db vs the newer views. Thus I will often left join from session_id in the exec view to spid in the sysprocesses view just to get the dbid from the older view.

1 Like
4

I tried sp_whoisactive and did not get the expected result. (only got one session listed)

5

You are probably familiar with sp_whoisactive, but in case not there are lots of Command Parameters you can provide, to see different things, so you might need to twiddle with them?

6

Thank you everyone.

I like to see what I can come up with so I learn something. (until I forget it in a couple of weeks :smiley: )

Thanks @ScottPletcher, that view did it.

@gbritton, @Kristen, Thank you for the suggestions. I will need to work with the sp_whoisactive to see what parameters need tweaked. I did not spend too much time on it, but it is a bit overwhelming to me.

7

Me too! I have a small set of Commands that I am familiar with, and use. Like other similar things, my requirement is infrequent use, so I don't learn the tool inside-out ... unfortunately.

There might be a series of suggested Command-parameter-sets on the web somewhere?

Or perhaps folk could suggest the ones that they use - and we could Pool that knowledge?