MSSQL Tutorial: Search code inside of stored proceedures

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = ‘expiration_date =’, @notcontain = ”

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case
when sysobjects.xtype = ‘P’ then ‘Stored Proc’
when sysobjects.xtype = ‘TF’ then ‘Function’
when sysobjects.xtype = ‘TR’ then ‘Trigger’
end as [Object Type]

FROM sysobjects,syscomments

WHERE sysobjects.id = syscomments.id AND sysobjects.type in (‘P’,’TF’,’TR’) AND sysobjects.category = 0 AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0 AND ((CHARINDEX(@notcontain,syscomments.text)=0 or CHARINDEX(@notcontain,syscomments.text)<>0))

Sorry, this little piece of handiwork isn’t my own. I got it from select-sql.com but I thought I would share the love. You can use this to search through the code inside of your stored proceedures to find whatever it is you’re looking for.

Ten Reasons Not To Use ASP!

10. I have to write my own isNull function?

9. No double equals = retarded language

8. You can’t response.write across multiple lines unless you _ & fix them. Can’t concatenate across multiple lines unless you _ & fix them.

7. WHERE THE HELL IS ++ or – – or -= or += or all the others? I think they made it so we had to do count = count + 1 just to watch us all suffer and tear our hair out.

6. Comments are apostrophes. So you know, when you’re typing up strings you have extra fun trying to read things like “and he said, “”don”t mess wit me ”sucka”””” ‘isn’t that just funny! you can’t tell where my strings end and my comments begin!

5. No block comments. Enough said. WTF?

if 1=0 then

put my comment crap here if I want to type more than one line or if my comment is so long that I want to put a paragraph return so it doesn’t run outside the 80 line view of my text editor. Pure GENIUS!

end if

4. If you have a table like this:

id –  int
comment – text
active – bool
category – int
date – timestamp

And you pull select * from this table into a dataset then the fields of type text overwrite the values all the fields underneath it: active, category, date, etc all come up blank even though they really have a value.

3. I heart Linux

2. No associative arrays from datasets unless you switch scripts and hack in some Javascript or create a scripting dictionary. Instead I have to loop through the dataset each time, what a waste of resources. Who in their right god-forsaken mind decided this wasn’t a must have? Obviously someone who didn’t do a lot of ASP with databases….

1.  Subjectivity XKCD:

Error FIX: Crystal Report Won’t Stop Loading

Problem: I noticed if you have really large strings in the crystal report, instead of having them as type text (aka memo in Crystal) the report won’t ever stop loading. For instance,  a field called question that’s string(8000) instead of type text.

Solution: What you can do is cast the string as a text in the SQL before it’s pulled into Crystal. That seems to fix the problem. Something along the lines of:

mysql> SELECT (CAST question AS TEXT) FROM quiz_questions WHERE qid = 1