Query to get Users from SQL Server 2008

CREATE TABLE #TEMP(
UserName nvarchar(100),
RoleName nvarchar(50),
LoginName nvarchar(50),
DefDBName nvarchar(50),
DefSchemaName nvarchar(50),
UserID nvarchar(10),
SID nvarchar(200))

DECLARE @ID INT, @LAST INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)

SET @ID = (SELECT MIN(DATABASE_ID) FROM sys.databases)
SET @LAST = (SELECT MAX(DATABASE_ID) FROM sys.databases)

WHILE @ID <= @LAST 
 BEGIN 
   SET @DB_NAME = (SELECT NAME 
                   FROM sys.databases 
                   WHERE database_id = @ID) 


     IF @DB_NAME IS NOT NULL 
       BEGIN 
         SET @SQL = 'INSERT INTO #TEMP 
         EXEC [' + @DB_NAME + '].[dbo].[sp_helpuser]' 
         EXEC (@SQL) 
       END 
   
     SET @ID = @ID + 1 
 END 


SELECT UserName 
FROM #TEMP 
WHERE UserName NOT IN 
  ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA') 
GROUP BY UserName 


DROP TABLE #TEMP

Query to Retrieve Job Info from msdb Database


SELECT
-- Format date
((SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 1,4) + '-' +
SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 5,2) + '-' +
SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 7,2)) + ' ' +

-- Format time
(
-- Hours
CASE
WHEN CONVERT(VARCHAR(20), H.run_time/10000) > 12 THEN CONVERT(VARCHAR(20), (H.run_time/10000) -12)
WHEN CONVERT(VARCHAR(20), H.run_time/10000) = 0 THEN '12'
ELSE CONVERT(VARCHAR(20), H.run_time/10000)
END + ':' +

-- Minutes
CASE LEN(CONVERT(VARCHAR(20), H.run_time/100%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_time/100%100)
ELSE CONVERT(VARCHAR(20), H.run_time/100%100)
END + ':' +

--Seconds
CASE LEN(CONVERT(VARCHAR(20), H.run_time%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_time%100)
ELSE CONVERT(VARCHAR(20), H.run_time%100)
END

+ ' ' +

-- AM or PM
CONVERT(VARCHAR(3),
CASE
WHEN CONVERT(VARCHAR(20), H.run_time/10000) > 12
THEN ' PM'
ELSE ' AM'
END

))) AS [Last Run Time],

S.NAME AS [Job Name], H.Message,

-- Format Status
CASE H.run_status
WHEN 1 THEN 'Success'
ELSE 'Failure'
END AS Status,

-- Format Run_Duration
(CASE LEN(CONVERT(VARCHAR(20), H.run_duration/10000))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration/10000)
ELSE CONVERT(VARCHAR(20), H.run_duration/10000)
END + ':' +

CASE LEN(CONVERT(VARCHAR(20), H.run_duration/100%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration/100%100)
ELSE CONVERT(VARCHAR(20), H.run_duration/100%100)
END + ':' +

CASE LEN(CONVERT(VARCHAR(20), H.run_duration%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration%100)
ELSE CONVERT(VARCHAR(20), H.run_duration%100)
END) AS [Run Duration]

FROM [msdb].[dbo].[sysjobhistory] AS H
INNER JOIN [msdb].[dbo].[sysjobs] AS S
ON S.job_id = H.job_id
WHERE S.enabled = 1
AND H.step_id = 0
ORDER BY H.RUN_DATE desc, H.RUN_TIME DESC


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'