Finding Empty ntext and varchar Fields in SQL

Just the other day, I ran into another one of those little ‘slips your mind until you need it’ items.

In SQL, when checking for empty (no, NOT null, empty) fields with data types of ntext or varchar, querying for >’’ is not valid.

So, how can I find the records with empty fields? The test here would be to check for ANY string.

Well, LIKE ‘_%’ would be the way to do it.

For example, looking for records with an empty 'description' field (data type ntext) in a table with 200 entries:

SELECT * FROM widgets WHERE description IS NULL OR description NOT LIKE ‘_%’ 

would return the 17 records with empty or null description fields.

Conversely,

SELECT * FROM widgets WHERE description LIKE ‘_%’

would return the 183 records where there is something in the description field.

Continue reading →

Finding Occurrences of a Word in MSSQL Stored Procedures

Here's a little trick that slipped my mind until I needed to search through about 2400 SPs looking for a specific word.

The query is very simple, and it returns a listing of all of the SPs with the word you're looking for.

It's just another one of those 'you don't know what you've forgotten until you need it' things.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%foobar%' 
    AND ROUTINE_TYPE='PROCEDURE'

Continue reading →

NTEXT and Truncation Errors on Delete

A quick note to the MS SQL users here with a reminder to help you avoid a little gotcha. The NTEXT data type was deprecated in MSSQL 2005. So, if you've migrated a db up to SQL 2005 or 2008 and, if you try to delete records in a table with an NTEXT field from the data grid view, you'll get an error telling you that the data will be truncated, and the delete won't work. You gotta delete through a query.

Funny what you don't recall if you don't use it often.

Continue reading →