MarkPryceMaherMSFT

create proc CreateStats @table varchar(128) as
begin


	declare   @create_type    tinyint -- 1 default, 2 Fullscan, 3 Sample
	declare  @sample_pct     tinyint


	IF @create_type IS NULL

MarkPryceMaherMSFT

 -- Is it a lakehouse or warehouse
 SELECT db_name() as db_name, DATABASEPROPERTYEX(db_name(), 'Edition') as [Type]

-- Whats the workspace id?
 SELECT DATABASEPROPERTYEX('master', 'workspaceid') AS workspaceid 
 
 -- What is the artifact id?
SELECT DATABASEPROPERTYEX(db_name(), 'ArtifactId') AS ArtifactId 

MarkPryceMaherMSFT

// use when you have workspace monitoring enabled

// when were tables last updated
MirroredDatabaseTableExecutionLogs
| summarize max(Timestamp), sum(ProcessedRows) by SourceSchemaName, SourceTableName

MarkPryceMaherMSFT

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

  SELECT db_name(), DATABASEPROPERTYEX(db_name(), 'Edition')

MarkPryceMaherMSFT

-- 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

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

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

########################################################################################################
# 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