T-SQL Tuesday # 157: Having fun with T-SQL

April 11, 2023

I thank Reitse Eskens (Blog|Twitter) for hosting T-SQL Tuesday #161. The topic for this month is Having Fun with T-SQL. I also want to acknowledge Adam Machanic’s vision behind this project. I learned a lot from some T-SQL Tuesdays and participated in a few. Thank you, Steve Jones (Blog|Twitter), for coordinating this program. If you are interested to see previous topics, visit here.

Reitse asked us about our “most fun script”. Reitse gave two examples. One he wrote, and another one came from Brent Ozar. I have two queries that I have been carrying in my library since 2014. Unfortunately, I did not document the source. If any of the readers know the writer’s name please let me know via comment, and I will add where credit belongs.

The first script is called “who can fire you”. This gives a list of members of Server-level roles.

Who can fire you?

	SR.[name] AS role__name,
	[server_principals].name AS login__name
FROM sys.[server_role_members] 
INNER JOIN sys.[server_principals] 
ON [server_role_members].[member_principal_id] = [server_principals].[principal_id]
		FROM sys.[server_principals]
		WHERE [type_desc] = 'SERVER_ROLE'
  ) AS SR ON [server_role_members].[role_principal_id] = SR.[principal_id]
ORDER BY SR.name, [server_principals].[name];

This script is called “Abusing SSMS(Maybe)?”. Gives a list of request that is executing in SQL Server using SQL Server Management Studio excluding session running the script.

Abusing SSMS(Maybe)?

	SUBSTRING(ST.text, R.statement_start_offset / 2,
		CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)
      ELSE R.statement_end_offset
    END - R.statement_start_offset ) / 2) AS statement_executing,
    db_name(R.database_id) AS database__name
FROM sys.dm_exec_sessions S 
INNER JOIN sys.dm_exec_requests R ON S.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST
WHERE S.program_name LIKE 'Microsoft SQL Server Management Studio%' 
AND R.session_id <> @@SPID

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.