Skip to content

Instantly share code, notes, and snippets.

@ClassyCircuit

ClassyCircuit/fdwqueries.sql

Last active May 31, 2018 14:24
Show Gist options
  • Save ClassyCircuit/4d17ce725cb5d0f8b70acf1251b9cb3b to your computer and use it in GitHub Desktop.
Save ClassyCircuit/4d17ce725cb5d0f8b70acf1251b9cb3b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ClassyCircuit revised this gist May 31, 2018. 1 changed file with 21 additions and 1 deletion.
    22 changes: 21 additions & 1 deletion fdwqueries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -42,4 +42,24 @@ SELECT *
    SELECT col.Name as "TargetColName" ,cm.*
    FROM [AISFIDA_FDW_CFG_dev].[BIML].[ColumnMapping] cm
    JOIN [BIML].[Column] col ON cm.TargetColumnID = col.ColumnID
    where cm.TargetObjectID = '6
    where cm.TargetObjectID = '6
    --get transformation rules for Dim Loads
    SELECT TOP (1000) [ID]
    ,[Name]
    ,[TargetObject_Name]
    ,[TargetColumn_Name]
    ,[SourceObject_Name]
    ,[SourceColumn_Name]
    ,[JOIN-condition]
    ,[Transformation]
    ,[BusinessRule]
    ,[Audit]
    ,[LookUpObject]
    ,[LookUpColumn]
    ,[LookUpCondition]
    ,[Comments]
    FROM [AISFIDA_MDS_dev].[mdm].[vAISFIDA_BIML_ColumnMapping]
    where TargetObject_Name like '%Dim.LossExposure%'
  2. ClassyCircuit revised this gist May 23, 2018. 1 changed file with 13 additions and 1 deletion.
    14 changes: 13 additions & 1 deletion fdwqueries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -30,4 +30,16 @@ ORDER BY o.NAME
    --update source last load date in CFG db
    update [AISFIDA_FDW_CFG_itest].[dbo].[SourceLastLoad]
    set SourceLastLoadValue = '2000-01-01'
    where objectid = 205
    where objectid = 205

    --get column mapping from MDM Db
    SELECT *
    FROM [AISFIDA_MDS_dev].[mdm].[vAISFIDA_BIML_ColumnMapping]
    --where TargetObject_Name like '%Dim.LossExposure%'
    where TargetObject_Name like 'PSA_WP.LE_LossExposure_ExpView'

    --join columnmapping from BIML db
    SELECT col.Name as "TargetColName" ,cm.*
    FROM [AISFIDA_FDW_CFG_dev].[BIML].[ColumnMapping] cm
    JOIN [BIML].[Column] col ON cm.TargetColumnID = col.ColumnID
    where cm.TargetObjectID = '6
  3. ClassyCircuit revised this gist May 18, 2018. 1 changed file with 6 additions and 1 deletion.
    7 changes: 6 additions & 1 deletion fdwqueries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -25,4 +25,9 @@ FROM sys.indexes AS i
    AND i.index_id = ddps.index_id
    WHERE i.index_id < 2 AND o.is_ms_shipped = 0
    AND o.name not like '%Archive%'
    ORDER BY o.NAME
    ORDER BY o.NAME

    --update source last load date in CFG db
    update [AISFIDA_FDW_CFG_itest].[dbo].[SourceLastLoad]
    set SourceLastLoadValue = '2000-01-01'
    where objectid = 205
  4. ClassyCircuit revised this gist May 18, 2018. No changes.
  5. ClassyCircuit revised this gist May 11, 2018. 1 changed file with 24 additions and 1 deletion.
    25 changes: 24 additions & 1 deletion fdwqueries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -2,4 +2,27 @@
    select *
    FROM [AISFIDA_MDS_dev].[mdm].[vAISFIDA_BIML_Column]
    where [OverrideWithValue] IS NOT NULL
    group by [OBJECT_NAME]
    group by [OBJECT_NAME]

    --returns how many rows are in all tables
    --old version
    USE atest_waypointclaims
    SELECT o.NAME,
    i.rowcnt
    FROM sysindexes AS i
    INNER JOIN sysobjects AS o ON i.id = o.id
    WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
    --and o.name like '%%'
    ORDER BY o.NAME

    --new version
    USE [AISFIDA_FDW_STG_atest]
    SELECT o.name,
    ddps.row_count
    FROM sys.indexes AS i
    INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
    INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
    AND i.index_id = ddps.index_id
    WHERE i.index_id < 2 AND o.is_ms_shipped = 0
    AND o.name not like '%Archive%'
    ORDER BY o.NAME
  6. ClassyCircuit revised this gist May 11, 2018. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions fdwqueries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@
    --returns all columns which should be GDPRed
    select *
    FROM [AISFIDA_MDS_dev].[mdm].[vAISFIDA_BIML_Column]
    where [OverrideWithValue] IS NOT NULL
  7. ClassyCircuit created this gist May 11, 2018.
    4 changes: 4 additions & 0 deletions fdwqueries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,4 @@
    select *
    FROM [AISFIDA_MDS_dev].[mdm].[vAISFIDA_BIML_Column]
    where [OverrideWithValue] IS NOT NULL
    group by [OBJECT_NAME]