LitKnd

<# 
    More examples of PowerShell commands for Continuous Integration:
        https://documentation.red-gate.com/sca3/automating-database-changes/continuous-integration
#>

param
(
      [string]$project = 'C:\git\StackOverflow\StackOverflow',
      [string]$packageVersion = '0.0.001',
      [string]$packageID = 'SO',

LitKnd

	USE [master];
	GO

	IF DB_ID('AdventureWorks2017') IS NOT NULL
	BEGIN
		ALTER DATABASE AdventureWorks2017 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE AdventureWorks2017;
	END;
	RESTORE DATABASE [AdventureWorks2017]
	FROM DISK = N'S:\MSSQL\BACKUP\AdventureWorks2017.bak'

LitKnd

SELECT TOP 20
	(SELECT CAST(SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) AS NVARCHAR(MAX)) FOR XML PATH(''),TYPE) AS [TSQL],
    qs.execution_count AS [#],
    CAST(qs.total_worker_time/1000./1000. AS numeric(30,1)) AS [cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE

LitKnd

/*****************************************************************************
Copyright (c) 2018 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: help@sqlworkbooks.com
*****************************************************************************/

USE master;
GO

CREATE OR ALTER PROCEDURE dbo.sp_indexdetails (

LitKnd

/*****************************************************************************
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: help@sqlworkbooks.com
Copyright (c) 2018 SQL Workbooks LLC
Permission is hereby granted, free of charge, to any person 
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without 
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or
sell copies of the Software, and to permit persons to whom 

LitKnd

/*****************************************************************************
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: help@sqlworkbooks.com

Copyright (c) 2018 SQL Workbooks LLC

Permission is hereby granted, free of charge, to any person 
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without 
restriction, including without limitation the rights to use,

LitKnd

SET NOCOUNT ON;
GO

USE master;
GO

IF DB_ID('QueryStoreTest') IS NOT NULL
BEGIN
    ALTER DATABASE QueryStoreTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE QueryStoreTest;

LitKnd

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
Get WideWorldImporters-Full.bak from Microsoft at:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
******************************************************************************/

USE master;
GO

LitKnd

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php


Get WideWorldImporters-Full.bak from Microsoft at:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
******************************************************************************/

USE master;

LitKnd

/***********************************************************************
Copyright 2017, SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/

/* Doorstop*/
RAISERROR('Did you mean to run the whole thing?', 20,1) WITH LOG;
GO