Even the faintest ink beats the strongest memory.

  • Get the “Current Directory” of an Oracle SqlPlus Session

    I might be a very very late comer but I am starting to pick up Oracle and this is my first post about this platform. My current playground is my local win7 workstation with an Oracle Express Edition installed. And I’d like to know from which directory I launched my SqlPlus session. Here is the…

  • T-SQL Stored Procedure to Get the Last Job Run Time and Status

    [sourcecode language=”sql”] /* Stored procedure to run the last run time and run satus of a SQL job. SQL Version: 2005+ Last modified on 2013-07-03 by Nick Xu –Usage –returns the last successful run time exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status = N’success’ –returns the last failed runtime exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status =…

  • T-SQL Stored Procedure to Get the UNC Path of Last Database Backup Location

    [sourcecode language=”text” wraplines=”false”] /* Script to get the last backup file location in the format of a UNC path for specified DB’s Last modified by Nick Xu on 2013-06-17 Usage: exec sp__GetLastDatabackupLocation ‘MDP’ */ use master go if exists (select * from sysobjects where name = ‘sp__GetLastDataBackupLocation’ and type = ‘P’) begin drop proc sp__GetLastDataBackupLocation…

  • Powershell Function to Get the Last Boot Up Time of a Computer

    [sourcecode language=”powershell” wraplines=”false”] #Return the last reboot time of a computer #Usage: Get-LastBootUpTime [ServerName] Function Get-LastBootUpTime { param ([string]$computername=$env:computername) $computername = $computername.toupper() Get-WmiObject -ComputerName $computername win32_operatingsystem| select csname, @{LABEL=’LastBootUpTime’;EXPRESSION={$_.ConverttoDateTime($_.lastbootuptime)}} } [/sourcecode]

  • Powershell Function to Get the Windows Version of a Computer

    [sourcecode language=”powershell” wraplines=”false”] #Get Windows version on a specific computer/instance #Usage: Get-WindowsVersion [ServerName] Function Get-WindowsVersion { param ([string]$computername=$env:computername) $computername = $computername.toupper() (Get-WmiObject -ComputerName $computername -Class Win32_OperatingSystem).Caption } [/sourcecode]

  • Powershell Function to Get Statues of All Sql Related Services

    [sourcecode language=”powershell” wraplines=”false”] #Get SQL-related services’ statuses on a specific computer #Usage: Get-SqlServiceStatus [ServerName] Function Get-SQLServiceStatus { param ([string]$ComputerName=$env:computername) Get-Service -ComputerName $ComputerName -Name "*sql*" } [/sourcecode]

  • Powershell Script of Sending an HTML Email Report in a Formatted Table

    This is a re-post from Powershell expert “Ben Wilkinson“, the original ps function is here. I did have some trouble in making it work in my own script and found that I have to add opening and closing “style” tags in the css definition part to make it work. So here I am writing a memo about…

  • T-SQL Stored Procedure to Report the Last Uptime of an Instance

    /* Stored proc to report uptime of a sql server instance based on the initialization time of its tempdb. Version 2000+ */ use master go IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘sp__uptime’ AND type = ‘P’) DROP PROC sp__uptime go create proc sp__uptime AS begin select @@SERVERNAME as ServerName, crdate as LastUpTime…

  • T-SQL Script to Get Last Backup Dates for All Active DB’s

    /* Script to list the last full backup time for all active user DB’s. Version: 2005+ */ with tmp as ( select row_number() over (partition by BS.database_name order by BS.backup_set_id desc) as rowid, BS.database_name, BS.backup_finish_date from msdb.dbo.backupset BS inner join msdb.dbo.backupmediafamily BMF on BS.media_set_id = BMF.media_set_id where BS.type = ‘D’ and BS.database_name not in (‘master’,…

  • T-SQL Script to Check the ‘Auto Update Statistics’ Option for Each DB in an Instance

    [sourcecode language=”sql”] /* Script to check if AutoUpdateStats option is turned on for all the DB’s in an instance. Note: you have to take the output from the following script and run it again to get the final results. */ –SQL2000 select ‘select ”’ + name + ”’ as DB_Name, databasepropertyex(”’ + name + ”’,…