Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Simple PowerShell script to break apart comment blocks from code blocks, of a .SQL file
$Path = 'c:\temp\sys_databases.sql'
$s = Get-Content -Raw ( Resolve-Path $Path )
#$s.GetType()
<# Doug's code for extracting the comment blocks. #>
$locations=@()
$pos=$s.IndexOf("/*")
while ($pos -ge 0) {
$locations+=[pscustomobject]@{startPos=$pos;endPos=$null}
$pos=$s.IndexOf("/*", $pos+2)
}
$count=0
$pos=$s.IndexOf("*/")
while ($pos -ge 0) {
$locations[$count].endPos=$pos
$pos=$s.IndexOf("*/", $pos+1)
$count++
}
<# My basic attempt #>
$PreviousLocation = $null
<# The line below cpits out a code block, in the event the file stars with code. #>
$s.Substring(0, ($locations[0].startPos))
foreach($location in $locations)
{
$start=$location.startPos
$length=$location.endPos-$location.startPos+2
<# The line below spits out the comment blocks #>
$s.Substring($start, $length)
<# The line below spits out the code blocks #>
$s.Substring($PreviousLocation.endPos, ($location.startPos - $PreviousLocation.endPos))
$PreviousLocation=$location
}
$s.Substring($location.endPos, ($s.Length-$location.endPos))
<# The line above grabs the last code block from the .SQL file. #>
<# When you need to debug, the list of comment-block locations is in the variable below #>
#$locations
SELECT SYSDATETIME()
/*
Created from: .\sys_databases.ipynb
Created by: Export-NotebookToSqlScript
Created on: 07/27/2020 18:20:25
*/
/* First, find out how many databases are on this instance. */
SELECT *
FROM sys.databases
/* Next check out all the processes currently running, using sys.processes. */
SELECT *
FROM sys.processes
/* Disk space subsection */
SET NOCOUNT ON;
SET ANSI_WARNINGS ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @sqlmajorver int
DECLARE @ErrorMessage NVARCHAR(4000)
SELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);
IF @sqlmajorver > 9
BEGIN
SELECT DISTINCT 'Information' AS [Category], 'Disk_Space' AS [Information], vs.logical_volume_name,
vs.volume_mount_point, vs.file_system_type, CONVERT(int,vs.total_bytes/1048576.0) AS TotalSpace_MB,
CONVERT(int,vs.available_bytes/1048576.0) AS FreeSpace_MB, vs.is_compressed
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) vs
ORDER BY FreeSpace_MB ASC
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.