Friday, May 28, 2010

Get jobs and their schedules

SELECT t1.name AS [Job Name]
  , t1.description AS [Job Description]
  , CASE t1.enabled WHEN 1 THEN 'Yes' ELSE 'No' END AS [Enabled]
  , CASE t3.freq_type
    WHEN  1 THEN 'Once'
    WHEN  4 THEN 'Daily'
    WHEN  8 THEN 'Weekly'
    WHEN 16 THEN 'Monthly'
    WHEN 32 THEN 'Monthly relative'
    WHEN 64 THEN 'When SQL Server Agent starts'
    WHEN 128 THEN 'Start whenever the CPU(s) become idle' END as Occurs
  , t3.active_start_time StartTime
  , t1.date_created AS [Date Created]
  , t1.date_modified AS [Date Modified]  
FROM msdb.dbo.sysjobs t1
 INNER JOIN msdb.dbo.sysjobschedules t2 ON t1.job_id = t2.job_id
 INNER JOIN msdb.dbo.sysschedules t3 ON t2.schedule_id = t3.schedule_id

Sunday, May 23, 2010

Convert columns to rows

Sometimes you need to convert columns to rows to get data you need.
For example, this query is used to get which customers buy which services.

DECLARE @CustomerService TABLE(CustomerID INT
       , Service1 CHAR(1)
       , Service2 CHAR(1)
       , Service3 CHAR(1)
       )
INSERT INTO @CustomerService VALUES(1,'Y','Y','N')
INSERT INTO @CustomerService VALUES(2,'N','N','Y')
INSERT INTO @CustomerService VALUES(3,'Y','N','N')

SELECT CustomerID, [Service], ServiceValue
FROM 
   (SELECT CustomerID
   , Service1
   , Service2
   , Service3
 FROM @CustomerService) pvt
UNPIVOT
   (ServiceValue FOR [Service] IN 
   (Service1
  , Service2
  , Service3)
) A
WHERE ServiceValue = 'Y'

Get permissions of columns

SELECT *
FROM DBA_COL_PRIVS

SELECT *
FROM USER_COL_PRIVS

SELECT *
FROM ALL_COL_PRIVS