T-SQL's REPLACE() Function

DECLARE @VAR AS VARCHAR(50)
SET @VAR = 'DEF LEPPARD IS THE GREATEST ROCK BAND EVER!'
SELECT @VAR
-- USE W/ SET
SET @VAR = REPLACE(@VAR, 'DEF LEPPARD', 'METALLICA')
SELECT @VAR
-- USE W/ SELECT
SELECT REPLACE(@VAR,'METALLICA','LED ZEPPELIN')

-- LOOKS LIKE REPLACE() CAN ONLY BE USED IN EITHER A
-- SELECT STATEMENT OR WHEN SETTING A VARIABLE
-- http://msdn.microsoft.com/en-us/library/ms186862.aspx

Escaping or Inserting Single Quotes into a String in T-SQL

Just in case anyone ever needs to do this. This gave me a headache for about 30 minutes this morning:


DECLARE @MYVAR AS VARCHAR(100)
DECLARE @ESCAPE AS VARCHAR(100)
SET @MYVAR = 'PLACING SINGLE QUOTES INTO A STRING'
SET @ESCAPE = 'Single quote''s are escaped with double single quotes like these: '''''
SELECT @MYVAR AS MYVAR
SET @MYVAR = '''' + @MYVAR + ''''
SELECT @MYVAR AS MYVAR
SELECT @ESCAPE AS VAR

Adding a Constraint to a SQL Server DB field to ensure that the field contains a particular string

  This was an interesting problem. What if, for some strange reason, you wanted to make sure that a field contained a certain string or it didn't get inserted? This can be handled pretty easily on the front end, but just for fun, let's try doing it on the back end:

-- Unrealistic, I'm sure. Let's just say that we ONLY want people
-- with Gmail email addresses adding their emails into our table.

ALTER TABLE myTable
WITH CHECK ADD
CONSTRAINT [ValidEmail]
CHECK
(
Email LIKE '%@gmail.com'
)
GO

Randomly Order a Result Set in SQL Server

SELECT *
FROM myTable
ORDER BY NEWID()

SQL Server sp for Starting a Job

EXEC msdb.dbo.sp_start_job 'My_Job'

Query to List Recovery Model for all DBs on a SQL Server

SELECT DBID, [name],databasepropertyex([name],'Recovery') as [Recovery]
FROM master.dbo.sysdatabases
ORDER BY [name];

Update Database Compatibility Level


-- This statement updates all databases that have a compatibility level
-- of 90 (SQL Server 2005) to 100 (SQL Server 2008)

DECLARE @ID INT
DECLARE @COUNT INT
DECLARE @DB_NAME NVARCHAR(200)
DECLARE @SQL NVARCHAR(200)

SET @ID = 1
SET @COUNT = (SELECT MAX(DBID) FROM sys.sysdatabases WHERE cmptlevel <> 100)

WHILE @ID <= @COUNT 
 BEGIN 
   SET @DB_NAME = (SELECT name FROM sys.sysdatabases 
                   WHERE cmptlevel = 90 AND dbid = @ID) 
                     IF @DB_NAME IS NOT NULL 
                       BEGIN 
                         SET @SQL = 'ALTER DATABASE 
                                     [' + @DB_NAME + '] 
                                     SET COMPATIBILITY_LEVEL 
                                       = 100' 
                         EXEC (@SQL) 
                     END
                     SET @ID = @ID + 1 
 END