SQL Stuff!

OK kids, time for some stupid SQL tricks. Actually these are mostly things that I don't use that often so it's hard to keep them memorized when I do need them. I have these in a Sharepoint list but you don't have access to that, so here they are.

1. First, converting date formats. Sometimes you need to store the day without the time, and sometimes you need to return a string that does not contain the time.

This converts '1/1/2000 4:20 PM' into '1/1/2000 12:00 AM', which is the actual value of the date without the time:
Convert(datetime, Convert(varchar(15), getdate(), 101))

This converts '1/1/2000 4:20 PM' into '1/1/2000':
Convert(varchar(15), getdate(), 101)

2. Another useful command is when you suddenly discover that the log file has grown out of control because the default recovery model is Full and you're not using Backup Exec with the SQL Agent to backup your database. If your database is not mission-critical and you're OK with restoring from last night's backup in the event of a complete disk failure/database corruption/etc., you can set the recovery model to Simple. However the log file won't shrink until a full SQL-aware backup has been performed. If you don't care and just need to shrink it now, it's a two-step process.

backup log [dbname] with truncate_only
go
dbcc shrinkfile('[logname]', 63)
go

It's important to note that the '[logname]' is not the filename, nor is it the name of the database, it's the Logical Name listed in the Files section of the Database properties. Enclose it in single quotes since it is a string value, not an object. However [dbname] is the object name of the database without quotes just like "use [dbname]". This DBCC command shrinks it to about 64 MB. Just make sure you perform a full SQL backup before running this.

3. Cursors! Cursors should be avoided whenever possible, they are not efficient and produce a load on the server, etc, etc. But sometimes there is no other choice when you need to loop through a recordset. So obviously since we don't use cursors that often, it's hard to remember the exact syntax. Here is a little template to make it easier:

DECLARE @[var1] varchar(8)
DECLARE [cursorname] CURSOR
FOR
SELECT [column1] FROM [table]
OPEN [cursorname]
FETCH NEXT FROM [cursorname] INTO @[var1]
WHILE @@FETCH_STATUS = 0 BEGIN
[query]
FETCH NEXT FROM [cursorname] INTO @[var1]
END
CLOSE [cursorname]
DEALLOCATE [cursorname]

4. Ever have a large database with lots of stored procedures and you're trying to remember where that one awesome piece of code that you wrote last year was used? Well if you can remember some of the code you can search for it!

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

5. Migrated a bunch of databases to a new version of SQL and want to upgrade the databases themselves? Also want to switch them to Simple recovery mode? That's easy. First you can make a list of all databases and those settings:

SELECT name, compatibility_level, is_auto_shrink_on, recovery_model_desc FROM sys.databases
where is_auto_shrink_on=1 or recovery_model=1 or compatibility_level<90
(use compatibility_level<100 if you're on SQL 2008)

Then assuming you've tested them under the new system and everything is good to go, or maybe you're just one of those people that likes to upgrade first and fix issues later...

ALTER DATABASE [dbname] SET RECOVERY SIMPLE, AUTO_SHRINK OFF
EXEC dbo.sp_dbcmptlevel @dbname=N'[dbname]', @new_cmptlevel=90
(again, @new_cmptlevel=100 for SQL 2008)

6. And finally, if you need to know if a temp table exists:

IF OBJECT_ID('tempdb..#temptablename') IS NOT NULL