Skip to content

Instantly share code, notes, and snippets.

View MarkPryceMaherMSFT's full-sized avatar

Mark Pryce-Maher MarkPryceMaherMSFT

39 followers · 8 following
View GitHub Profile
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / findingwarehouses.sql
Created April 17, 2025 10:18
script to show if the databases are for a lakehouse or warehouse
DECLARE @command varchar(8000)
declare @i int =1;
declare @d int =0;
select @d = max(database_id) from sys.databases;
declare @dbname nvarchar(128);
set @command = '';
while(@i < @d)
begin
set @dbname = '';
select @dbname = name from sys.databases where database_id = @i
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / fabricdatabasetype.sql
Created April 15, 2025 08:36
Get database type
SELECT db_name(), DATABASEPROPERTYEX(db_name(), 'Edition')
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / datetime with timezone example.sql
Created March 19, 2025 08:40
Some code to show how to add a timezone to the datetime2 in Fabric
-- Create test table
create table datetest (d varchar(50), dt DATETIME2(6));
insert into datetest SELECT 'Central European Standard Time', CONVERT(DATETIME2(6), '2022-03-27T01:01:00', 126)
AT TIME ZONE 'Central European Standard Time';
insert into datetest SELECT 'Pacific Standard Time' , CONVERT(DATETIME2(6), '2022-03-27T01:01:00', 126)
AT TIME ZONE 'Pacific Standard Time';
select d as TimeZone, dt as DateTime_without_TimeZone,
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / collation_views.sql
Created February 24, 2025 14:26
Script to create views so a custom collation can be added to varchar columns
SELECT
SchName,
tblName,
'CREATE view [' + SchName + '].[vw_' + tblName + '] as SELECT ' + STRING_AGG(colname + ' '+
case coltype
when 'varchar' then ' COLLATE Latin1_General_100_CI_AI_SC as ' + colname
else ''
end
, ', ') + ' from [' + SchName + '].[' + tblName + '];' AS DDLScript
FROM (
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / MirroredDatabase.ipynb
Created January 22, 2025 13:32
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / lakehouse and sql endpoint.py
Created January 9, 2025 14:57
lakehouse and sql endpoint details
import json
import time
import struct
import sqlalchemy
import pyodbc
import notebookutils
import pandas as pd
from pyspark.sql import functions as fn
from datetime import datetime
import sempy.fabric as fabric
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / Refresh Tables in SQL Endpoint.py
Last active April 19, 2025 18:32
Code to refresh the tables in the SQL Endpoint, after they have been updated in the lakehouse. Cut and paste this code into a cell in a notebook
########################################################################################################
# Sample script to call the syncronisation between the Fabric Lakehouse and the SQL Endpoint
#
## THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
#
# This script is a workaround until the documented API is released: https://learn.microsoft.com/en-us/fabric/release-plan/data-warehouse#refresh-sql-analytics-endpoint-rest-api
#
#sempy version 0.4.0 or higher
!pip install semantic-link --q
import json
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / create-list-delete-warehouses.ipynb
Created November 30, 2024 12:17
Script showing how to create, list and delete Fabric warehouses using the REST API
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / workspace size.ipynb
Last active December 6, 2024 16:02
This script can be used to find the size of a workspace, lakehouse , warehouse or table
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@MarkPryceMaherMSFT
MarkPryceMaherMSFT / create_many_tables.py
Created October 31, 2024 17:40
use with the create table scruot
create_new_table("control", False, False, False, False, False, False, False, False, False )
create_new_table("control", True, False, False, False, False, False, False, False, False )
create_new_table("control", False, True, False, False, False, False, False, False, False )
create_new_table("control", True, True, False, False, False, False, False, False, False )
create_new_table("control", False, False, True, False, False, False, False, False, False )
create_new_table("control", True, False, True, False, False, False, False, False, False )
create_new_table("control", False, True, True, False, False, False, False, False, False )
create_new_table("control", True, True, True, False, False, False, False, False, False )
create_new_table("control", False, False, False, True, False, False, False, False, False )
create_new_table("control", True, False, False, True, False, False, False, False, False )