Initialize-DatabaseNotebook.ps1

<#
.SYNOPSIS
Populates a new notebook with details about a database.

.FUNCTIONALITY
Database

.EXAMPLE
Initialize-DatabaseNotebook.ps1 -ServerInstance ServerName -DatabaseName AdventureWorks

Adds cells to the current Polyglot Notebook that generates a header, ER diagram, and table stats.
#>

#Requires -Version 7
#Requires -Modules dbatools
[CmdletBinding()] Param(
# The name of a server (and optional instance) to connect and use for the query.
[Parameter(Position=0,Mandatory=$true)][ValidatePattern('\A[^'']+\z')][string] $ServerInstance,
# The the database to connect to on the server.
[Parameter(Position=1,Mandatory=$true)][ValidatePattern('\A[^'']+\z')][string] $DatabaseName,
# By default, an encryped connection is used. This disables that for certain compatibility issues.
[switch] $DisableEncryption,
# By default, a read-only connection is used. This disables that and allows read/write operations,
# for certain compatibility issues or other needs.
[switch] $AllowWrites,
# The path to a static Markdown README.md file to create in parallel since the notebook readme
# isn't fully supported yet.
[string] $Readme
)

Import-CharConstants.ps1 NL -Scope Script

function Add-MainHeader
{
	@"
$DatabaseName database
======================

"@ |Add-NotebookCell.ps1
	if(!$Readme) {Write-Information 'Skipping generation of README.md'; return}
	@"
$DatabaseName database
======================

<!--
This content is generated by README.ipynb and copied here because otherwise the native GitHub Jupyter Notebook
renderer doesn't show it by default for the directory, doesn't render the Mermaid content, and doesn't fold the
input code cells away (making it very noisy). Hopefully one day GitHub will address these issues, and/or
Polyglot Notebooks will provide an export/CLI to generate flat CommonMark, or the much cleaner Polyglot .dib
Notebook type will include output cells when saved and rendered.

You can use this PowerShell command to convert a copied notebook output cell into a CommonMark table:
"`$(Get-Clipboard)" -replace '(?m)<style.*</style>' |HtmlToMarkdown\Convert-HtmlToMarkdown |Set-Clipboard
-->

"@ |Out-File $Readme utf8BOM
}

function Add-SqlSupport
{
	'#r "nuget: Microsoft.DotNet.Interactive.SqlServer, *-*"' |Add-NotebookCell.ps1 -Language csharp
}

function Add-ErDiagramGenerator
{
	@"
# generate erDiagram
Get-DbaDatabase -SqlInstance '$ServerInstance' -Database '$DatabaseName' |
	Get-DbaDbTable |
	Where-Object Name -NotIn dtproperties,__MigrationLog,__SchemaSnapshot |
	Export-MermaidER.ps1 |
	Add-NotebookCell.ps1 -Language mermaid
"Last updated `$(Get-Date)"
"@ |Add-NotebookCell.ps1 -Language pwsh
	if(!$Readme) {return}
	@"
``````mermaid
$(Get-DbaDatabase -SqlInstance $ServerInstance -Database $DatabaseName |
	Get-DbaDbTable |
	Where-Object Name -NotIn dtproperties,__MigrationLog,__SchemaSnapshot |
	Export-MermaidER.ps1)
``````

"@ |Out-File $Readme -Append
}

function Format-TableQuery([Parameter(Mandatory=$true,ValueFromPipeline=$true)][Microsoft.SqlServer.Management.Smo.Table] $Table)
{
	Begin {$i = 0}
	Process
	{
		$datecols = $Table.Columns |Where-Object {$_.DataType.Name -like '*date*'} |Select-Object -ExpandProperty Name
		if($i++ -eq 0)
		{
			if(!$datecols)
			{@"
select '$($Table.Schema -eq 'dbo' ? '' : $Table.Schema + '.')$($Table.Name)' [Table], count(*) [# Records], null [Oldest record], null [Newest record]
  from [$($Table.Schema)].[$($Table.Name)]
"@}
			$datecols |ForEach-Object {@"
select '$($Table.Schema -eq 'dbo' ? '' : $Table.Schema + '.')$($Table.Name)' [Table], count(*) [# Records],
       convert(varchar,min([$_]),111) [Oldest record], convert(varchar,max([$_]),111) [Newest record]
  from [$($Table.Schema)].[$($Table.Name)]
"@}
		}
		else
		{
			if(!$datecols)
			{@"
 union all
select '$($Table.Schema -eq 'dbo' ? '' : $Table.Schema + '.')$($Table.Name)', count(*), null, null
  from [$($Table.Schema)].[$($Table.Name)]
"@}
			$datecols |ForEach-Object {@"
 union all
select '$($Table.Schema -eq 'dbo' ? '' : $Table.Schema + '.')$($Table.Name)', count(*),
       convert(varchar,min([$_]),111), convert(varchar,max([$_]),111)
  from [$($Table.Schema)].[$($Table.Name)]
"@}
		}
	}
}

function Add-TableDetails
{
	$connstr = "Data Source=$ServerInstance; Initial Catalog=$DatabaseName; Encrypt=$($DisableEncryption ? 'False' : 'True'); ApplicationIntent=$($AllowWrites ? 'ReadWrite' : 'ReadOnly'); Integrated Security=True"
	@"
#!connect mssql --kernel-name $($DatabaseName -replace '\W+') "$connstr"
"@ |Add-NotebookCell.ps1 -Language csharp
	$Local:OFS = [Environment]::NewLine
	$query = @"
$(Get-DbaDatabase -SqlInstance $ServerInstance -Database $DatabaseName |
	Get-DbaDbTable |
	Where-Object Name -NotIn dtproperties,__MigrationLog,__SchemaSnapshot |
	Format-TableQuery);
"@
	@"
#!sql-$($DatabaseName -replace '\W+')
-- be sure to remove duplicates as needed
$query
"@ |Add-NotebookCell.ps1 -Language sql
	if(!$Readme) {return}
	@"
| Table | # Records | Oldest record | Newest record |
|-------|----------:|:-------------:|:-------------:|
$(Invoke-DbaQuery -Query $query -SqlInstance (Connect-DbaInstance -ConnectionString $connstr) -As DataRow |
	ForEach-Object {"| $($_.Table) | $($_.'# Records') | $($_.'Oldest record') | $($_.'Newest record') |"})
"@ |Out-File $Readme -Append
}

function Add-DatabaseDetails
{
	Add-MainHeader
	Add-SqlSupport
	Add-ErDiagramGenerator
	Add-TableDetails
}

Add-DatabaseDetails