Skip to content

Instantly share code, notes, and snippets.

Aaron Nelson SQLvariant

Block or report user

Report or block SQLvariant

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View GetSQLBuffers_wProvider_PipeTo_Out-SquarifiedTreeMap.ps1
<# This is the code used to tell you
which databases are using up the RAM on
your SQL Server is great information to know
Queries are from https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/ #>
$SQLInstance = 'localhost\SQL2016'
Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
View GetLastNightsFailedSQLAgentJobs.ps1
<#1#>
Get-Command -Module SqlServer -Noun SqlAgent*
<#2#>
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed
<#3#>
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed |
Out-GridView
View Install_SQLServerPerformanceDashboardReporting.ps1
<# This script is intented to help automate Steps 2-7 for downloading & installing the SQL Server Performance Dashboard Reports
https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-dashboard-reports-unleashed-for-enterprise-monitoring/ #>
<# If the ReportingServicesTools is not present, download the ReportingServicesTools module from GitHib #>
try {Import-Module ReportingServicesTools -ErrorAction Stop} catch {Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)} finally {Import-Module ReportingServicesTools}
<# Setting our GitHub resources to variables #>
$ZipURL = "https://github.com/Microsoft/tigertoolbox/raw/master/SQL-performance-dashboard-reports/SQL%20Server%20Performance%20Dashboard%20Reporting%20Solution.zip"
$SQLURL = 'https://github.com/Microsoft/tigertoolbox/raw/master/SQL-performance-dashboard-reports/setup.sql'
View Get-DisksSpace.PS1
Function Get-DisksSpace
{
<#
.SYNOPSIS
Grabs Hard Drive & Mount Point space information.
.DESCRIPTION
Grabs Hard Drive & Mount Point space information.
.PARAMETER serverName
Accepte 1 or more servernames, up to 50 at once.
@SQLvariant
SQLvariant / 000_Downlading_SqlServer_module.ps1
Last active May 22, 2017
SQL PowerShell Demos for Spring of 2017
View 000_Downlading_SqlServer_module.ps1
Install-Module SqlServer
<# If you don't have admin #>
Install-Module SqlServer -Scope CurrentUser
<# Find the commands #>
Get-Command -Module SqlServer -CommandType Cmdlet |
Out-GridView
<# What if you need to install on a server behind a firewall #>
View 000_Setup
<#
Let's go shopping in the PowerShell Store
Ok, it is not actually called the 'store'
it is called the PowerShell Gallery
#>
Start-Process http://PowerShellGallery.com
<# Use the Search #>
<#
View Top15Palatte
{
"name": "Top15Palatte",
"dataColors": ["#887e6f","#295270","#e6942d","#d52c33","#9f9311","#9fc2dc","#ffc945","#665d3c","#54758D","#DD565C","#EBA957","#9f9f9f","#276B75","#b6749c","#9d9898"
],
"background":"#ffffff",
"foreground": "#003052",
"tableAccent": "#9fc2dc"
@SQLvariant
SQLvariant / ExcelSchemaAndData_to_PowerBI_DataSet.ps1
Created Mar 11, 2019
Use PowerShell to extract Excel Worksheey Schema and Data to create Power BI DataSet
View ExcelSchemaAndData_to_PowerBI_DataSet.ps1
<# Literally one of the oldest SQL PowerShell examples, and it now works on PSCore.
I'm only doing this step to generate data to place in an Excel spreadsheet.
IMPORTANT: If you already have an Excel spreadsheet, skip this section! #>
ls 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse |
WHERE {$_.Mode -ne 'd' } |
foreach {
Invoke-Sqlcmd -ServerInstance $_.Name -Database master -OutputAs DataTables -Query "
SELECT @@ServerName AS 'ServerName',
DB_NAME(dbid) AS 'DatabaseName',
name AS 'LogicalName',
@SQLvariant
SQLvariant / QuickSQLContainer_wAW2016.ps1
Created Mar 12, 2019
Create a SQL-on-Linux container, and restore the AdventureWorks2016 DB
View QuickSQLContainer_wAW2016.ps1
$BakURL = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak"
$BakFile = "$($Home)\Downloads\AdventureWorks2016.bak"
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile
mkdir C:\SQLData\Docker\SQLDev02
#dir C:\SQLData\Docker\SQLDev02 | OGV -PassThru | Remove-Item
Copy-Item -Path "$($Home)\Downloads\AdventureWorks2016.bak" -Destination C:\SQLData\Docker\SQLDev02
<# Create a Container with a volume mounted from the host #>
@SQLvariant
SQLvariant / AdventureWorksDW2017_ExportExcel.ps1
Last active Mar 14, 2019
Export every table in the AdventureWorksDW2017 db to Excel
View AdventureWorksDW2017_ExportExcel.ps1
#Requires -Modules SqlServer
#Requires -Modules ImportExcel
<# The AdventureWorksDW2017 only has 29 tables and they're all under 1 million rows.#>
cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables
<# Scenario #1 A) all Dimensions in a single file,
and B) each Fact table in their own file. #>
<# A) Every Dimension table in a worksheet named after the table, the same Excel file #>
dir | WHERE { $_.name -like 'dim*' } |
You can’t perform that action at this time.