SQL Snippets

03 Jun, 2025

Get all view definitions

When you come into a new data warehouse, you can rip out all of the view definitions from the SQL engine by running this:

SELECT definition, *
FROM sys.sql_modules

Note that these definitions will still preserve all of the linebreaks, indentation, and comments in the SQL definition. Save the output of this as .json. This is better because the definition will retain all of the linebreaks correctly. Then, run this PowerShell script to rip out all of the definitions into individual text files.

$data = cat '.\Source\Views.json' | ConvertFrom-Json

$pattern = "(?<=CREATE.*VIEW\s+).*?(?=\s+AS)"

$data |
    % {
        $definition = $_[0].definition 
        
        $definition -match $pattern
        
        $name = $matches[0].Replace("[", "").Replace("]", "")

        $parts = $name.Split('.')

        $schema = $parts[0]
        $dest = ".\Source\$schema"

        $viewName = $parts[1]

        if (!(Test-Path $dest)) { New-Item -ItemType Directory $dest}

        $definition > "$dest\$viewName.sql"

    }

Get all tables and columns

It's important to have a reference of all of the table columns when working with a data warehouse.

SELECT 
    distinct
    -- *
    TABLE_SCHEMA
    , TABLE_NAME
    , COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 1=1
-- ORDER BY TABLE_SCHEMA, TABLE_NAME , COLUMN_NAME

-- powershell
-- (cb) | ? { $_ -ne "PartitionId" } |  % { ", [" + $_ + "]" } | scb

Generate all option sets

Sometimes, option sets need to be joined on manually. This is especially true when working with Dynamics F&O outputs through the Synapse Link in Power Apps. Option set/enum values that were previously spelled out will not be. The following procedure will generate all of the table joins that need to be made to fully expand all of the options sets.

First, get all of the possible option set values

select 
    EntityName
    , OptionSetName
    , [Option]
    , LocalizedLabel
    , ExternalValue
from [<schema>].GlobalOptionsetMetadata
order by EntityName, OptionSetName, [Option]

Then, run this PowerShell script to compose all of the LEFT JOIN statements that need to be made on all of the tables.

using namespace System.Text; #for StringBuilder

$os = cat .\GlobalOptionsetMetadata.csv | convertfrom-csv -delim ","

# $os

$db = 'd365'
$schema = 'd365'
$source = 'GlobalOptionsetMetadata'
$attribute = 'LocalizedLabel'
# $attribute = 'ExternalValue'

$columns = [StringBuilder]::new()
$joins = [StringBuilder]::new()

$os |
    group EntityName |
    # select -first 1 |
    % {
        
        $entity = $_.Name
        # $entity
        # $_
        ($_.Group | group OptionSetName).Name |
            % {
                $_
                $os = $_ 
                $jointable = "os_$os"
                
                [void] $columns.AppendLine( "`t-- , $jointable.$attribute AS $($os)name")
                [void] $joins.AppendLine( "`t-- LEFT JOIN [$db].[dbo].[$source] AS $jointable ON base.$os = $jointable.[Option] AND $jointable.EntityName = '$entity' AND $jointable.OptionSetName = '$os'" )

            }
        
        $filename = ".\optionset\$entity.sql"
        $sql = "CREATE OR ALTER VIEW [$schema].[$entity]`n`tAS`nSELECT`n`t*`n$($columns.ToString())  FROM [$db].[$entity]`n$($joins.ToString())"

        $sql

        $sql > $filename
        [void] $columns.Clear()
        [void] $joins.Clear()
    }

Collation problems

Avoid collation clashes when joining tables by doing something like this:

CREATE VIEW thisView AS
SELECT * FROM [schema].[thisTable]
JOIN [schema].[secondTable]
ON [ColumnId] COLLATE DATABASE_DEFAULT = [ColumnId] COLLATE DATABASE_DEFAULT 
GO

#powershell #sql #synapse