I am a DBA and thus I grab a lot of query text out of various dmv's to inspect the problem queries. I use Poor Man T-SQL formatter currently. The issue I have is when pulling query text from a dmv like this code chunk:
SQL server version 2012 or above btw.
SELECT P.spid,
P.STATUS,
P.hostname,
P.loginame,
P.cpu,
R.start_time,
R.command,
P.program_name,
TEXT
FROM sys.dm_exec_requests R
CROSS JOIN master.dbo.sysprocesses P
CROSS APPLY sys.dm_exec_sql_text(P.sql_handle)
WHERE 1 = 1
and P.STATUS NOT IN ('sleeping', 'background')
AND R.session_id = P.spid
AND P.spid <> @@spid --ignore this query
When I grab a value from the TEXT column and paste to a new window I often end up with terrible adhoc queries where it looks something like this nonsensical query i just wrote
SELECT X, Y --OPTIONAL, Z FROM ALPHABET as abc --FOR SPECIAL CASES LEFT JOIN AGGREGATECOUNT as A ON A.id = abc.id where 4>3 --to deal with non english AND 1=1
when formatting with the tools I have tried such as poorman t-sql formattor I cannot get this out of its 'single line' state without manually skipping over the comments. Is there any formatter (plugin for ssms or notepad++ is a plus.) that handles this situation? or should I be pulling the text out differently to avoid this problem of all the newlines missing? For example formatting the above with my tools yields
SELECT
X
,Y --OPTIONAL, Z FROM ALPHABET as abc --FOR SPECIAL CASES LEFT JOIN AGGREGATECOUNT as A ON A.id = abc.id where 4>3 --to deal with non english AND 1=1
If no good tool exists, any tips to improve my current process that involves manually adding a new line after "obvious" comments?
What I'm aiming for:
SELECT
X
,Y --OPTIONAL
, Z
FROM ALPHABET as abc
--FOR SPECIAL CASES
LEFT JOIN AGGREGATECOUNT as A ON A.id = abc.id
WHERE 4>3
--to deal with non english
AND 1=1
