SQL – Server Search

What do you do if you need to find a certain string used in a stored procedure? What if you need to be completely certain that you can remove the object and there are no dependencies within the server itself? There are a number of 3rd party tools that allow searching within the SQL Server database schemas, but some are slow due to precaching and some are simply not powerful enough due to lack of options. SQL itself comes to the rescue!

The query below can be as simple and as advanced as you want it to be, but no matter what- it is always fast and easy to use:

SELECT
OBJECT_NAME([id]) AS 'ObjectName',
MAX(CASE WHEN OBJECTPROPERTY([id], 'IsProcedure') = 1 THEN 'Procedure'
WHEN OBJECTPROPERTY([id], 'IsScalarFunction') = 1 THEN 'Scalar Function'
WHEN OBJECTPROPERTY([id], 'IsTableFunction') = 1 THEN 'Table Function'
WHEN OBJECTPROPERTY([id], 'IsTrigger') = 1 THEN 'Trigger'
END) AS 'ObjectType'
FROM
[syscomments]
WHERE



LIKE '%SEARCHNEEDLE%'
AND (
OBJECTPROPERTY([id], 'IsProcedure') = 1
OR OBJECTPROPERTY([id], 'IsScalarFunction') = 1
OR OBJECTPROPERTY([id], 'IsTableFunction') = 1
OR OBJECTPROPERTY([id], 'IsTrigger') = 1
)
GROUP BY
OBJECT_NAME([id])
4/2/2012: Updated with more advanced SQL example

SQL – The Danger of NULLs

Following up on the previous article SQL – The Power of NULLs, here are some of the things to keep in mind when you do decide to use NULL in your work.

By far, one of the most common mistakes is to assume that <> (not equal) or even = (equal) logic covers NULLs. That is completely incorrect in both cases; NULL is neither equal to anything nor is it not equal to anything, including itself. Consider the following code:

SELECT CASE WHEN 1<>NULL THEN 1 WHEN NULL=NULL THEN 0 END

Of course the end result will be neither- NULL 🙂

What about this one: will WHEN help us be able to do this comparison on the fly?

SELECT CASE NULL WHEN NULL THEN 1 ELSE 0 END

Just a little- the ELSE logic will kick in covering “all other” possibilities, but the CASE logic still relies on = and <> just the same way- NULL is still nothing, it still never rings TRUE.

So what are we left with? Well, you can always try using ISNULL (the great counterpart of NULLIF) and COALESCE in more advanced cases. Either way, remember- NULLs can help you do really interesting things with your DB and your queries, you just need to be careful, that’s all.

Textpattern Dynamic Date

Textpattern does not seem to support dynamic dates intrinsically, so here is a workaround I use. In this case, it will show the full date if it’s not in the current year, otherwise skip the year part. This approach can certainly be used for more advanced styling as well.

if(posted(array('format'=>'%Y'))<date("Y"))
{
echo posted(array('format'=>'%b %d %Y, %R %p'));
}else
{
echo posted();
}

 

SQL – The Power of NULLs

Some people despise NULLs for their unusual behavior and apparent complexity, while some remain suspicious simply because of a lack of experience with them. Although at times it is not a good idea to use them in database design, they often come in handy in queries alone. They let us easily test for the “third case”- complete lack of data. Assume you have a bit column that only allows 0 and 1, so you can distinguish if something is “on” or “off”, what better way than the NULL is there for you to test for the row’s presence, or vice versa?..

SELECT *
 FROM
 BitTable
 WHERE
 BitColumn IS NOT NULL

This approach can also be easily extended to filter vast chunks of data. Suppose you have a big table of users and a table of their last logins (i.e. audit). By left joining the latter and checking for the absent rows, you can say which users haven?t logged-in in the last 3 weeks (or never logged-in for that matter):

SELECT *
 FROM
 Users
 LEFT JOIN AuditUsers
 ON AuditUsers.UserID = Users.ID
 AND ChangeDate > DATEADD(week, -3, GETDATE())
 WHERE
 AuditUsers.ID IS NULL

As you can see, the possibilities are really endless. Here is another neat trick that probably deserves an article just by itself. I actually use this more often than I would like, but it does allow me to easily set “preferences” in the data output (or filter it altogether) based on seemingly unrelated data fields. All of this is done by simply having a specific CASE statement. Here is an example derived from the one above. This approach is best when used against multiple tables and columns, but this should suffice just to show how to use it. In this example we will first see users that never logged in at all, then all users who logged in over 3 weeks ago, and then all the rest. Note that you can also use TOP to limit just to the first couple of rows that come out, very handy:

SELECT *
 FROM
 Users
 LEFT JOIN AuditUsers
 ON AuditUsers.UserID = Users.ID
 ORDER BY
 CASE WHEN AuditUsers.ID IS NULL THEN 0
 WHEN ChangeDate < DATEADD THEN 1
 ELSE 2
 END ASC

Edited by Katya Pupko