kmoormann

/*============================================================================
   Script to report Memory usage details of a SQL Server instance
   Author: Sakthivel Chidambaram, Microsoft http://blogs.msdn.com/b/sqlsakthi 

   Date: June 2012
   Version: V2
   
   V1: Initial Release
   V2: Added PLE, Memory grants pending, Checkpoint, Lazy write,Free list counters

kmoormann

USE DER_CommProg;
GO

CREATE TABLE #functionInfo
(
  ID BIGINT NOT NULL
	,Name VARCHAR(200) NULL
	,[SchemaName] VARCHAR(200) NULL
	,[Description] VARCHAR(200) NULL
	,[Definition] VARCHAR(MAX) NULL

kmoormann

DECLARE @JOB_NAME SYSNAME = N'Daily update of indexes & statistics'; 
 
IF NOT EXISTS(     
        select 1 
        from msdb.dbo.sysjobs_view job  
        inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id 
        where  
            activity.run_Requested_date is not null  
        and activity.stop_execution_date is null  
        and job.name = @JOB_NAME 

kmoormann

#http://www.brangle.com/wordpress/2009/08/combine-join-two-text-files-using-powershell/

kmoormann

SET NOCOUNT ON
DECLARE @isRunning BIT
DECLARE @xp_results TABLE (job_id                UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,

kmoormann

USE msdb ;
GO

EXEC dbo.sp_start_job N'The name of the job here';
GO

kmoormann

--######## FOR STARTING WHEN STOPPED OR STOPPING ####################
--IF YOU NEED TO STOP THE SERVER FIRST TO TRUN THE LINE BELOW
--EXEC xp_servicecontrol N'STOP',N'SQLServerAGENT';
USE master;
GO

CREATE TABLE #SQLAgentStatus
(
Status varchar(50),
Timestamp datetime default (getdate())

kmoormann

IF NOT EXISTS 
(
    SELECT * FROM [information_schema].[columns] 
    WHERE   table_name = 'Customer' 
    AND table_schema = 'dbo'
    AND column_name = 'FavoriteColorId'
)
BEGIN
    ALTER TABLE [dbo].[Customer]
    ADD FavoriteColorId int

kmoormann

SELECT 
'INSERT INTO [dbo.SSISConfig] '
	+CHAR(10)+CHAR(13)+CHAR(9) + '([ConfigurationFilter]'
	+CHAR(13)+CHAR(10)+CHAR(9) + ',[ConfiguredValue]'
	+CHAR(13)+CHAR(10)+CHAR(9) + ',[PackagePath]'
	+CHAR(13)+CHAR(10)+CHAR(9) + ',[ConfiguredValueType])'
	+CHAR(13)+CHAR(10)+CHAR(9) + 'VALUES'
	+CHAR(13)+CHAR(10)+CHAR(9) + '(N''' + ConfigurationFilter + ''''
	+CHAR(13)+CHAR(10)+CHAR(9) + ',N'''+ConfiguredValue+''''
	+CHAR(13)+CHAR(10)+CHAR(9) + ',N'''+ConfiguredValueType+''''

kmoormann

SELECT
	s.name [Schema Name],
	tables.name [Table Name],
    c.name [Column Name],
	ISNULL(i.is_primary_key, 0) [Is Primary Key],
    t.Name [Data type],
    c.max_length [Max Length],
    c.precision ,
    c.scale ,
    c.is_nullable,