Databases – Page 5 – techish.net

Show Jobs that Will Run in Microsoft SQL Server

I don’t remember the link I found this little tidbit at, but it’s useful. This will display the scheduled jobs on your Microsoft SQL Server instance that will be running in the future.

USE msdb
;WITH CTE AS (SELECT schedule_id, job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
FROM sysjobschedules)
SELECT A.name Job_Name,
'Will be running today at '+
SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At'
FROM sysjobs A ,CTE B
WHERE A.job_id = B.job_id
AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101)
AND (SUBSTRING( CONVERT(VARCHAR(10),
CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >
SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)

SQL Backup Jobs

Index Fragmentation Report in Microsoft SQL Server

I came across something that I found useful today when troubleshooting some query performance reports.
I won’t re-post the entire article, but here’s what I found useful and I tweaked it to my needs by ordering by avg_fragmentation_in_percent as well as only displaying results with IPS.page_counts greater than 1000 and avg_fragmentation_in_percent greater than 75%.

--To Find out fragmentation level of a given database
--This query will give DETAILED information
--CAUTION : It may take very long time, depending on the number of tables in the DB
USE foodb
GO
SELECT object_name(IPS.object_id) AS [TableName],
   SI.name AS [IndexName],
   IPS.Index_type_desc,
   IPS.avg_fragmentation_in_percent,
   IPS.avg_fragment_size_in_pages,
   IPS.avg_page_space_used_in_percent,
   IPS.record_count,
   IPS.ghost_record_count,
   IPS.fragment_count,
   IPS.avg_fragment_size_in_pages,
   IPS.page_count
FROM sys.dm_db_index_physical_stats(db_id(N'foodb'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 and IPS.page_count > 1000 and avg_fragmentation_in_percent > 75
ORDER BY avg_fragmentation_in_percent desc
GO

Display Data and Log File Paths for Microsoft SQL Server Instance

Find all the data and log file paths for all databases on Microsoft SQL Server instance.

SELECT name, physical_name AS current_file_location FROM sys.master_files

Calculate Number of Days between Two Dates in Informix

This is how I calculate the number of days between two dates.
Suppose I have a small table:

Customer
Name
LastLogon

select current year to day - extend(customer.lastlogon, year to day)

You can use MINUTE, HOUR, SECOND as well to get a little more granular.

Misys Vision Encounter Templates

Misys Vision Encounter (Superbill) Templates

SQL Query:

select tmpname,tmpdesc from formtmphdr where allow_edit = 'N' and tmptype = 7

tmpname       tmpdesc
CODESCAN1S    MICROSCAN ENCOUNTER 1 (8 LPI - STYLE 2)
CODESCAN3S    MICROSCAN ENCOUNTER 3 (8 LPI - STYLE 2)
ENC09         MEDIC ENCOUNTER FORM #9
ENC09LASER    MEDIC #9 LASER ENCOUNT (60 LPP STYLE 3)
ENC10         MEDIC ENCOUNTER FORM #10
ENC10LABEL    MEDIC LABEL ENCOUNTER FORM #10 (STYLE 3)
ENC10LASER    MEDIC #10 LASER ENCOUNT (60 LPP STYLE 3)
ENC11         MEDIC ENCOUNTER FORM #11
ENC11LASER    MEDIC #11 LASER ENCOUNT (60LPP STYLE 1)
ENCVIS        VISION STANDARD ENCOUNTER
ENCVISLASE    MEDIC VISION LASER ENC (60LPP STYLE 2)
MICROSCAN3    MICROSCAN ENCOUNTER (3RD VERS) 2.14.4

These are the non-editable templates. You must copy the template (tmpname) to a new template and modify the new template accordingly.