Last active
July 28, 2020 18:02
-
-
Save SQLvariant/d9d5759317332f7281c644c97a6bc4b7 to your computer and use it in GitHub Desktop.
Simple PowerShell script to break apart comment blocks from code blocks, of a .SQL file
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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