T-SQL Tuesday #025 – Invitation to Share Your Tricks

This is my first response to a TSQL Tuesday.

There are lot of things in TSQL that can be done to make DBA’s life easier. We have a large collection of scripts. These are some I have used over the years at multiple jobs – there are several more but I don’t particularly want to write a very long post. Also a disclaimer that I did not write all of them.  I wrote some,  I tweaked some I found on sites like SQLServerCentral.com that have come in very handy. I apologize ahead for not offering credit since I have not kept track of the authors.

–Find the last server start date(2005)
SELECT crdate AS Datetime_Started FROM sysdatabases WHERE name = ‘tempdb’
 
1 –Find Blank passwords
SELECT name FROM sys.syslogins WHERE pwdcompare(””, password) = 1

2 –Find members of a role (for example, public)
SELECT a.[name] + ‘ ‘ + v.[name] + ‘ ON ‘ + QuoteName(oo.[name]) + ‘.’ + QuoteName(o.[name]) + ‘ TO ‘ + QuoteName(u.[name])
FROM dbo.sysprotects AS p JOIN master.dbo.spt_values AS a
ON (a.number = p.protecttype AND ‘T’ = a.type) JOIN master.dbo.spt_values
AS v ON (v.number = p.action AND ‘T’ = v.type) JOIN dbo.sysobjects AS o ON (o.id = p.id)
JOIN dbo.sysusers AS oo ON (oo.uid = o.uid) JOIN dbo.sysusers AS u ON (u.uid = p.uid) WHERE ‘public’ = u.name

3 –Find number of times a stored procedure was used since last reboot
SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = ‘proc’
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid),
   OBJECT_NAME(objectid,st.dbid)
 order by max(cp.usecounts)

4 –Stick in anything to be changed across all dbs (just used sp_dboption with read only here)
SET NOCOUNT ON

— Get the name of all databases
DECLARE UserDatabases CURSOR FOR

SELECT name FROM master..sysdatabases
   where name not in (‘master’,’tempdb’, ‘model’, ‘msdb’, ‘distribution’)

— Open Cursor
OPEN UserDatabases

— Define variables needed
DECLARE @DBase NVARCHAR(128)
DECLARE @COMMAND NVARCHAR(128)

— Get First database
FETCH NEXT FROM USerDatabases INTO @DBase

— Process until no more databases
WHILE (@@FETCH_STATUS = 0)
BEGIN

 set @command =’master..sp_dboption @dbname=”’ + @dbase +
                ”’,@optname=”Read ONLY”, @optvalue=”True”’

— Print command to be processed
  print @command

— Process Command
  exec (@command)

— Get next database
  FETCH NEXT FROM UserDatabases INTO @DBase

END

— Close and Deallocate Cursor
CLOSE AllDatabases
DEALLOCATE AllDatabases

5 —-My favorite, lists job calender

USE MSDB

DECLARE

@JOB_ID VARCHAR(200),

@SCHED_ID VARCHAR(200),

@FREQ_TYPE INT,

@FREQ_INTERVAL INT,

@FREQ_SUBDAY_TYPE INT,

@FREQ_SUBDAY_INTERVAL INT,

@FREQ_RELATIVE_INTERVAL INT,

@FREQ_RECURRENCE_FACTOR INT,

@ACTIVE_START_DATE INT,

@SCHEDULE VARCHAR(1000),

@SCHEDULE_DAY VARCHAR(200),

@START_TIME VARCHAR(10),

@END_TIME VARCHAR(10)

CREATE TABLE #SCHEDULES

(JOB_ID VARCHAR(200),

SCHED_ID VARCHAR(200),

JOB_NAME SYSNAME,

[STATUS] INT,

SCHEDULED INT NULL,

schedule VARCHAR(1000) NULL,

FREQ_TYPE INT NULL,

FREQ_INTERVAL INT NULL,

FREQ_SUBDAY_TYPE INT NULL,

FREQ_SUBDAY_INTERVAL INT NULL,

FREQ_RELATIVE_INTERVAL INT NULL,

FREQ_RECURRENCE_FACTOR INT NULL,

ACTIVE_START_DATE INT NULL,

ACTIVE_END_DATE INT NULL,

ACTIVE_START_TIME INT NULL,

ACTIVE_END_TIME INT NULL,

DATE_CREATED DATETIME NULL)

INSERT INTO #SCHEDULES (

job_id,

sched_id ,

job_name ,

[status] ,

Scheduled ,

schedule ,

freq_type,

freq_interval,

freq_subday_type,

freq_subday_interval,

freq_relative_interval,

freq_recurrence_factor,

active_start_date,

active_end_date,

active_start_time,

active_end_time,

date_created)

SELECT

j.job_id,

sched.schedule_id,

j.name ,

j.enabled,

sched.enabled,

NULL,

sched.freq_type,

sched.freq_interval,

sched.freq_subday_type,

sched.freq_subday_interval,

sched.freq_relative_interval,

sched.freq_recurrence_factor,

sched.active_start_date,

sched.active_end_date,

sched.active_start_time,

sched.active_end_time,

j.date_created

FROM sysjobs j

inner join

sysjobschedules s

ON j.job_id=s.job_id

INNER JOIN dbo.sysschedules sched

ON s.schedule_id = sched.schedule_id

WHILE 1=1

BEGIN

SET @SCHEDULE = ”

IF (SELECT COUNT(*) FROM #SCHEDULES WHERE scheduled=1 and schedule is null) = 0

BREAK

ELSE

BEGIN

SELECT

@job_id=job_id,

@sched_id=sched_id,

@freq_type=freq_type,

@Freq_Interval=freq_interval,

@freq_subday_type=freq_subday_type,

@freq_subday_interval=freq_subday_interval,

@freq_relative_interval=freq_relative_interval,

@freq_recurrence_factor=freq_recurrence_factor,

@active_start_date = active_start_date,

@start_time =

CASE

WHEN

LEFT(active_start_time, 2) IN (22, 23) AND len(active_start_time) = 6

THEN

convert(varchar(2), left(active_start_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ P.M’

WHEN

left(active_start_time, 2) = (12) AND len(active_start_time) = 6

THEN

cast(LEFT(active_start_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_start_time, 2) BETWEEN 13 AND 24 AND len(active_start_time) = 6

THEN

convert(varchar(2), left(active_start_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_start_time, 2) IN (10, 11) AND len(active_start_time) = 6

THEN

cast(LEFT(active_start_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ A.M.’

WHEN

active_start_time = 0

THEN

’12:00 A.M.’

WHEN

LEN(active_start_time) = 4

THEN

’12:’ + convert(varchar(2), left(active_start_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_start_time) = 3

THEN

’12:0′ + convert(varchar(2), left(active_start_time, 1) ) + ‘ A.M.’

WHEN

LEN(active_start_time) = 2

THEN

’12:00:’ + convert(varchar(2), left(active_start_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_start_time) = 1

THEN

’12:00:0′ + convert(varchar(2), left(active_start_time, 2) ) + ‘ A.M.’

ELSE

cast(LEFT(active_start_time,1) as char(1))

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),2, 2) + ‘ A.M.’

END,

@END_TIME= CASE

WHEN

left(active_end_time, 2) IN (22, 23) AND len(active_end_time) = 6

THEN

convert(varchar(2), left(active_end_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ P.M’

WHEN

left(active_end_time, 2) = (12) AND len(active_end_time) = 6

THEN

cast(LEFT(active_end_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_end_time, 2) BETWEEN 13 AND 24 AND len(active_end_time) = 6

THEN

convert(varchar(2), left(active_end_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_end_time, 2) IN (10, 11) AND len(active_end_time) = 6

THEN

cast(LEFT(active_end_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ A.M.’

WHEN

active_end_time = 0

THEN

’12:00 A.M.’

WHEN

LEN(active_end_time) = 4

THEN

’12:’ + convert(varchar(2), left(active_end_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_end_time) = 3

THEN

’12:0′ + convert(varchar(2), left(active_end_time, 1) ) + ‘ A.M.’

WHEN

LEN(active_end_time) = 2

THEN

’12:00:’ + convert(varchar(2), left(active_end_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_end_time) = 1

THEN

’12:00:0′ + convert(varchar(2), left(active_end_time, 2) ) + ‘ A.M.’

ELSE

cast(LEFT(active_end_time,1) as char(1))

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),2, 2) + ‘ A.M.’

END

FROM #SCHEDULES

WHERE schedule is null

AND scheduled=1

 
IF EXISTS(SELECT @freq_type WHERE @freq_type in (1,64))

BEGIN

SELECT @SCHEDULE = CASE @freq_type

WHEN 1 THEN ‘Occurs Once, On ‘+cast(@active_start_date as varchar(8))+’, At ‘+@start_time

WHEN 64 THEN ‘Occurs When SQL Server Agent Starts’

END

END

ELSE

BEGIN

IF @freq_type=4

BEGIN

SELECT @SCHEDULE = ‘Occurs Every ‘+cast(@freq_interval as varchar(10))+’ Day(s)’

END

IF @freq_type=8

BEGIN

SELECT @SCHEDULE = ‘Occurs Every ‘+cast(@freq_recurrence_factor as varchar(3))+’ Week(s)’

SELECT @schedule_day=”

IF (SELECT (convert(int,(@freq_interval/1)) % 2)) = 1

select @schedule_day = @schedule_day+’Sun’

IF (SELECT (convert(int,(@freq_interval/2)) % 2)) = 1

select @schedule_day = @schedule_day+’Mon’

IF (SELECT (convert(int,(@freq_interval/4)) % 2)) = 1

select @schedule_day = @schedule_day+’Tue’

IF (SELECT (convert(int,(@freq_interval/8)) % 2)) = 1

select @schedule_day = @schedule_day+’Wed’

IF (SELECT (convert(int,(@freq_interval/16)) % 2)) = 1

select @schedule_day = @schedule_day+’Thu’

IF (SELECT (convert(int,(@freq_interval/32)) % 2)) = 1

select @schedule_day = @schedule_day+’Fri’

IF (SELECT (convert(int,(@freq_interval/64)) % 2)) = 1

select @schedule_day = @schedule_day+’Sat’

SELECT @SCHEDULE = @SCHEDULE+’, On ‘+@schedule_day

END

IF @freq_type=16

BEGIN

SELECT @SCHEDULE = ‘Occurs Every ‘+cast(@freq_recurrence_factor as varchar(3))+’ Month(s) on Day ‘+cast(@freq_interval as varchar(3))+’ of that Month’

END

IF @freq_type=32

BEGIN

SELECT @SCHEDULE = CASE @freq_relative_interval

WHEN 1 THEN ‘First’

WHEN 2 THEN ‘Second’

WHEN 4 THEN ‘Third’

WHEN 8 THEN ‘Fourth’

WHEN 16 THEN ‘Last’

ELSE ‘Not Applicable’

END

SELECT @SCHEDULE =

CASE @freq_interval

WHEN 1 THEN ‘Occurs Every ‘+@SCHEDULE+’ Sunday of the Month’

WHEN 2 THEN ‘Occurs Every ‘+@SCHEDULE+’ Monday of the Month’

WHEN 3 THEN ‘Occurs Every ‘+@SCHEDULE+’ Tueday of the Month’

WHEN 4 THEN ‘Occurs Every ‘+@SCHEDULE+’ Wednesday of the Month’

WHEN 5 THEN ‘Occurs Every ‘+@SCHEDULE+’ Thursday of the Month’

WHEN 6 THEN ‘Occurs Every ‘+@SCHEDULE+’ Friday of the Month’

WHEN 7 THEN ‘Occurs Every ‘+@SCHEDULE+’ Saturday of the Month’

WHEN 8 THEN ‘Occurs Every ‘+@SCHEDULE+’ Day of the Month’

WHEN 9 THEN ‘Occurs Every ‘+@SCHEDULE+’ Weekday of the Month’

WHEN 10 THEN ‘Occurs Every ‘+@SCHEDULE+’ Weekend Day of the Month’

END

END

SELECT @SCHEDULE =

CASE @freq_subday_type

WHEN 1 THEN @SCHEDULE+’, At ‘+@start_time

WHEN 2 THEN @SCHEDULE+’, every ‘+cast(@freq_subday_interval as varchar(3))+’ Second(s) Between ‘+@start_time+’ and ‘+@END_TIME

WHEN 4 THEN @SCHEDULE+’, every ‘+cast(@freq_subday_interval as varchar(3))+’ Minute(s) Between ‘+@start_time+’ and ‘+@END_TIME

WHEN 8 THEN @SCHEDULE+’, every ‘+cast(@freq_subday_interval as varchar(3))+’ Hour(s) Between ‘+@start_time+’ and ‘+@END_TIME

END

END

END

UPDATE #SCHEDULES

SET schedule=@SCHEDULE

WHERE job_id=@job_id

AND sched_id=@sched_Id

END

SELECT job_name ,

[status] = CASE STATUS

WHEN 1 THEN ‘ENABLED’

WHEN 0 THEN ‘DISABLED’

ELSE ‘ ‘

END,

scheduled= case scheduled

when 1 then ‘Yes’

when 0 then ‘No’

else ‘ ‘

end,

schedule as ‘Frequency’ ,

convert(datetime, convert(varchar,active_start_date, 101)) AS schedule_start_date,

convert(datetime, convert(varchar,active_end_date, 101)) AS schedule_end_date,

date_created

FROM #schedules

WHERE scheduled=1

ORDER BY job_name

DROP TABLE #schedules

I hope you all find something useful. And thank you to Allen White for hosting.

Advertisements

1 Comment (+add yours?)

  1. Trackback: Allen White : T-SQL Tuesday #25 Followup - Just in Time for the Holidays

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: