December 25, 2014 at 8:43 pm
Comments posted to this topic are about the item Checking meta data
December 26, 2014 at 4:14 am
I picked the least incorrect answer and got my points. But the answer is still wrong. The more correct answer would be that it finds all stored procedures that start with sp_ but do not have the letter sequence "diagram" anywhere in their name and are not defined in the tSQLt schema.
And the really correct answer would add that the code uses a string concatenation method that is undocumented, unsupported, and known to be unreliable. This code should be rewritten using the FOR XML method of string concatenation.
December 26, 2014 at 9:05 am
Just wondering. What's the reason for excluding those with diagram in the name and those in the schema tSQLt?
December 26, 2014 at 9:13 am
Answer is incorrect:
create function dbo.sp_test() returns int
as
begin
return 1
end
go
SELECT SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + Char(13) + Char(10)
From INFORMATION_SCHEMA.ROUTINES
Where SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE 'sp[_]%'
And SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT LIKE '%diagram%'
AND ROUTINE_SCHEMA <> 'tSQLt'
Order By SPECIFIC_SCHEMA,SPECIFIC_NAME
GO
drop function dbo.sp_test
GO
December 26, 2014 at 9:13 am
For the literalist , the missing definition of the variable @output means the code doesn't run at all. But that's jus nit picking
Gerald Britton, Pluralsight courses
December 26, 2014 at 9:59 am
marcia.j.wilson (12/26/2014)
Just wondering. What's the reason for excluding those with diagram in the name and those in the schema tSQLt?
I will do this to omit some of the noise. Procs with diagram in the name or system procs that are related specifically to database diagrams in management studio are mostly noise for me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 26, 2014 at 10:08 am
Hugo Kornelis (12/26/2014)
I picked the least incorrect answer and got my points. But the answer is still wrong. The more correct answer would be that it finds all stored procedures that start with sp_ but do not have the letter sequence "diagram" anywhere in their name and are not defined in the tSQLt schema.And the really correct answer would add that the code uses a string concatenation method that is undocumented, unsupported, and known to be unreliable. This code should be rewritten using the FOR XML method of string concatenation.
Further on this, the query brings back both stored procedures and user defined functions which name starts with 'sp_' etc..
😎
December 26, 2014 at 11:39 am
For the literalist, even if you have declared the variable @Output it still doesn't work unless you also set it equal to an empty string ('').
December 26, 2014 at 4:52 pm
For the nitp^H^H^H^H literelist, the question does not ask what other code needs to be present in order for this to work, just what it does.
(And technically, when the variable in declared but not initialised, the code will still "find" the stored procedures and functions with a name starting with sp_ - it may not concatenate to the string as expected, but they will still be found)
Bottom line: given the popularity of prefixing stored procs with sp_, combined with the risks of that habit, it IS very useful to monitor or periodically check for such objects. This query is just one way to do it. I really dislike the sting concatenation method used, but otherwise the query is quite valuable - showing how to find the offending objects, and also how to exclude known exceptions.
December 26, 2014 at 5:13 pm
Hugo Kornelis (12/26/2014)
...(And technically, when the variable in declared but not initialised, the code will still "find" the stored procedures and functions with a name starting with sp_ - it may not concatenate to the string as expected, but they will still be found)
...
Really?
Without declaring the @Output variable:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Output".
With the @Output declared but not initialized:
NULL
The fact that the result is NULL would tell me that none of the answers is correct, even if it did find them all, if you are a literalist. Simple reason, how do you know if it worked, literally speaking.
Also, I wouldn't do it that way any way. I prefer the FOR XML PATH method of generating a list.
December 27, 2014 at 8:22 am
Lynn Pettis (12/26/2014)
Hugo Kornelis (12/26/2014)
...(And technically, when the variable in declared but not initialised, the code will still "find" the stored procedures and functions with a name starting with sp_ - it may not concatenate to the string as expected, but they will still be found)
...
Really?
Without declaring the @Output variable:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Output".
With the @Output declared but not initialized:
NULL
The fact that the result is NULL would tell me that none of the answers is correct, even if it did find them all, if you are a literalist. Simple reason, how do you know if it worked, literally speaking.
Also, I wouldn't do it that way any way. I prefer the FOR XML PATH method of generating a list.
Check the execution plan for the "declared but not initialised" version. You'll see that SQL Server does actually access the underlying system objects to find the relevant procedures. It will even actually concatenate them to the string variable. Due to the rule that concatenating any string to NULL will result in NULL, the data is lost when it is concatenated - but SQL Server does actually "find" the required data.
Yes, this is nitpicking. I already indicated that in my original message.
December 28, 2014 at 9:56 am
Apologies. Points have been awarded back.
This was a snippet from the stored procedure, so I didn't think about the output variable when looking at it.
December 29, 2014 at 5:39 am
Guessed it right 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
December 29, 2014 at 7:48 am
SQLRNNR (12/26/2014)
marcia.j.wilson (12/26/2014)
Just wondering. What's the reason for excluding those with diagram in the name and those in the schema tSQLt?I will do this to omit some of the noise. Procs with diagram in the name or system procs that are related specifically to database diagrams in management studio are mostly noise for me.
Thanks for the explanation.
December 29, 2014 at 8:45 am
I'm getting to this one a day late, but as written it now returns NULL:
DECLARE @OUTPUT VARCHAR(max);
SELECT @Output = @Output + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + Char(13) + Char(10)
From INFORMATION_SCHEMA.ROUTINES
Where SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE 'sp[_]%'
And SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT LIKE '%diagram%'
AND ROUTINE_SCHEMA <> 'tSQLt'
Order By SPECIFIC_SCHEMA,SPECIFIC_NAME
Be still, and know that I am God - Psalm 46:10
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply