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?
SELECT 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] INNER JOIN ( SELECT [principal_id], name 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.
SELECT 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, S.session_id, S.login_time, R.start_time, S.host_name, S.login_name, 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.