November 1, 2018 at 4:32 pm
I wrote this for a user on another forum. I figured I'd drop it here too in case anyone might find it useful.
SELECT
o.name,
o.object_id,
o.type_desc,
px.parameters,
returns = ISNULL(rx.returns, 'table'),
tc.table_cols
FROM
sys.objects o WITH (NOLOCK)
CROSS APPLY (
SELECT parameters = STUFF((
SELECT
CONCAT(', ', p.name, ' ', TYPE_NAME(p.user_type_id), ' ', psl.precision_scale_len)
FROM
sys.parameters p WITH (NOLOCK)
CROSS APPLY ( VALUES (CASE
WHEN p.user_type_id IN (34,35,36,40,48,52,56,58,59,60,61,62,98,99,104,122,127,128,129,130,189,241,256) THEN ''
WHEN p.user_type_id IN (106,108) THEN '(' + CONVERT(VARCHAR(10), p.precision) + ',' + CONVERT(VARCHAR(10), p.scale) + ')'
WHEN p.user_type_id IN (41,42,43) THEN '(' + CONVERT(VARCHAR(10), p.scale) + ')'
WHEN p.user_type_id IN (165,167,173,175) THEN '(' + CASE WHEN p.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), p.max_length) END + ')'
WHEN p.user_type_id IN (231,239) THEN '(' + CASE WHEN p.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), p.max_length / 2) END + ')'
END) ) psl (precision_scale_len)
WHERE
o.object_id = p.object_id
AND p.is_output = 0
FOR XML PATH ('')
), 1, 2, '')
) px (parameters)
OUTER APPLY (
SELECT
CONCAT(r.name, ' ', TYPE_NAME(r.user_type_id), ' ', psl.precision_scale_len)
FROM
sys.parameters r WITH (NOLOCK)
CROSS APPLY ( VALUES (CASE
WHEN r.user_type_id IN (34,35,36,40,48,52,56,58,59,60,61,62,98,99,104,122,127,128,129,130,189,241,256) THEN ''
WHEN r.user_type_id IN (106,108) THEN '(' + CONVERT(VARCHAR(10), r.precision) + ',' + CONVERT(VARCHAR(10), r.scale) + ')'
WHEN r.user_type_id IN (41,42,43) THEN '(' + CONVERT(VARCHAR(10), r.scale) + ')'
WHEN r.user_type_id IN (165,167,173,175) THEN '(' + CASE WHEN r.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), r.max_length) END + ')'
WHEN r.user_type_id IN (231,239) THEN '(' + CASE WHEN r.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), r.max_length / 2) END + ')'
END) ) psl (precision_scale_len)
WHERE
o.object_id = r.object_id
AND r.is_output = 1
) rx ([returns])
CROSS APPLY (
SELECT STUFF((
SELECT
CONCAT(', ', c.name, ' ', TYPE_NAME(c.user_type_id), ' ', psl.precision_scale_len)
FROM
sys.columns c WITH (NOLOCK)
CROSS APPLY ( VALUES (CASE
WHEN c.user_type_id IN (34,35,36,40,48,52,56,58,59,60,61,62,98,99,104,122,127,128,129,130,189,241,256) THEN ''
WHEN c.user_type_id IN (106,108) THEN '(' + CONVERT(VARCHAR(10), c.precision) + ',' + CONVERT(VARCHAR(10), c.scale) + ')'
WHEN c.user_type_id IN (41,42,43) THEN '(' + CONVERT(VARCHAR(10), c.scale) + ')'
WHEN c.user_type_id IN (165,167,173,175) THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), c.max_length) END + ')'
WHEN c.user_type_id IN (231,239) THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR(10), c.max_length / 2) END + ')'
END) ) psl (precision_scale_len)
WHERE
o.object_id = c.object_id
FOR XML PATH('')), 1, 2, '')
) tc (table_cols)
WHERE
o.type IN ('AF','FN','FS','FT','IF');
November 1, 2018 at 4:42 pm
Pretty cool. I just wish it could figure out who wrote the objects returned so that I can add their names to a pork chop list. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2018 at 4:53 pm
Jeff Moden - Thursday, November 1, 2018 4:42 PMPretty cool. I just wish it could figure out who wrote the objects returned so that I can add their names to a pork chop list. 😀
Assuming your artists sign their work in the comments... 😀
SELECT
sm.definition
FROM
sys.sql_modules sm;
November 1, 2018 at 4:57 pm
Jason A. Long - Thursday, November 1, 2018 4:53 PMJeff Moden - Thursday, November 1, 2018 4:42 PMPretty cool. I just wish it could figure out who wrote the objects returned so that I can add their names to a pork chop list. 😀Assuming your artists sign their work in the comments... 😀
SELECT
sm.definition
FROM
sys.sql_modules sm;
Heh... yeah, no... apparently only a few are actually proud of their work.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2018 at 5:57 pm
Jeff Moden - Thursday, November 1, 2018 4:57 PMJason A. Long - Thursday, November 1, 2018 4:53 PMJeff Moden - Thursday, November 1, 2018 4:42 PMPretty cool. I just wish it could figure out who wrote the objects returned so that I can add their names to a pork chop list. 😀Assuming your artists sign their work in the comments... 😀
SELECT
sm.definition
FROM
sys.sql_modules sm;Heh... yeah, no... apparently only a few are actually proud of their work.
Given the verbosity of your own comments, I figured that signing your work would be a bare minimum, don't pass go, don't collect $200 in your shop.
November 1, 2018 at 7:48 pm
Jason A. Long - Thursday, November 1, 2018 5:57 PMJeff Moden - Thursday, November 1, 2018 4:57 PMJason A. Long - Thursday, November 1, 2018 4:53 PMJeff Moden - Thursday, November 1, 2018 4:42 PMPretty cool. I just wish it could figure out who wrote the objects returned so that I can add their names to a pork chop list. 😀Assuming your artists sign their work in the comments... 😀
SELECT
sm.definition
FROM
sys.sql_modules sm;Heh... yeah, no... apparently only a few are actually proud of their work.
Given the verbosity of your own comments, I figured that signing your work would be a bare minimum, don't pass go, don't collect $200 in your shop.
I always have a nice flower box with purpose, usage notes, and revision history by author, etc. Ironically, I don't write such things for other people. I write them for me so I don't have to remember so much. 😀
To your point, though, I do find that a whole lot of people just write code. Some with zero or nearly zero comments and no sign of who wrote it when. It's a real shame. Some of the code like that is actually pretty good. It would be GREAT code if some documentation were included, especially some notes on usage (and that's a hint ;-)).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2018 at 8:26 pm
Jeff Moden - Thursday, November 1, 2018 7:48 PMI always have a nice flower box with purpose, usage notes, and revision history by author, etc. Ironically, I don't write such things for other people. I write them for me so I don't have to remember so much. 😀
To your point, though, I do find that a whole lot of people just write code. Some with zero or nearly zero comments and no sign of who wrote it when. It's a real shame. Some of the code like that is actually pretty good. It would be GREAT code if some documentation were included, especially some notes on usage (and that's a hint ;-)).
I'd like to say I fairly good comments but nothing on your level. Basic information... When it was created, who created it, why it was created. Followed by a revision history that includes date, dev initials, any applicable ticket numer(s) and a brief explination of the specific changes that were made. I also try to add inline comments around code that may not be immediatly obvious or breaks a "normal convention".
That said, there are still days that I have to look at some of my old code and have to spend 20 mins just trying to figure out wheather I was haveing a stroke of genious when I wrote it... of just a stroke... So I suppose there's always room for improvement.
November 2, 2018 at 12:25 pm
People have mentioned about formatting and casing consistency in code for readability. To me readability starts with knowing what each piece is doing to check for logic and not just syntax. That to me means comments are more important, not to diminish the value of proper formatting / indenting. Just that I have seen very pretty code without comments and would take me a while to comprehend the intention. Which begs the question of how readable is it compared to having important comments 🙂 ?
----------------------------------------------------
November 2, 2018 at 3:16 pm
MMartin1 - Friday, November 2, 2018 12:25 PMPeople have mentioned about formatting and casing consistency in code for readability. To me readability starts with knowing what each piece is doing to check for logic and not just syntax. That to me means comments are more important, not to diminish the value of proper formatting / indenting. Just that I have seen very pretty code without comments and would take me a while to comprehend the intention. Which begs the question of how readable is it compared to having important comments 🙂 ?
My self-imposed rule is that if you were to remove all of the code, the comments could be used to build a functional flow chart that someone else could write the code from.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2018 at 3:40 pm
Jeff Moden - Friday, November 2, 2018 3:16 PMMMartin1 - Friday, November 2, 2018 12:25 PMPeople have mentioned about formatting and casing consistency in code for readability. To me readability starts with knowing what each piece is doing to check for logic and not just syntax. That to me means comments are more important, not to diminish the value of proper formatting / indenting. Just that I have seen very pretty code without comments and would take me a while to comprehend the intention. Which begs the question of how readable is it compared to having important comments 🙂 ?My self-imposed rule is that if you were to remove all of the code, the comments could be used to build a functional flow chart that someone else could write the code from.
That's a great way to approach a task. Nicely packaged in a simple rule of thumb . Thanks Jeff.
----------------------------------------------------
November 2, 2018 at 4:29 pm
MMartin1 - Friday, November 2, 2018 3:40 PMJeff Moden - Friday, November 2, 2018 3:16 PMMMartin1 - Friday, November 2, 2018 12:25 PMPeople have mentioned about formatting and casing consistency in code for readability. To me readability starts with knowing what each piece is doing to check for logic and not just syntax. That to me means comments are more important, not to diminish the value of proper formatting / indenting. Just that I have seen very pretty code without comments and would take me a while to comprehend the intention. Which begs the question of how readable is it compared to having important comments 🙂 ?My self-imposed rule is that if you were to remove all of the code, the comments could be used to build a functional flow chart that someone else could write the code from.
That's a great way to approach a task. Nicely packaged in a simple rule of thumb . Thanks Jeff.
Yes, good advice, I'm just not there yet.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply