Red Meta Queries

RedMeta.sql

----------------------------------------------------------------------------------------------------
-- <<< system tables >>>
----------------------------------------------------------------------------------------------------
 
-- get RED meta data tables
SELECT  *
FROM    sys.objects
WHERE   (name LIKE 'ws_%' or name LIKE 'dss_%ac%' )
        AND type = 'U'
ORDER BY 1
 
SELECT  *
FROM    sys.objects
WHERE   type = 'U'
        AND [name] LIKE 'ws%'
ORDER BY 1
 
-- find tables with specified column
SELECT DISTINCT o.name
FROM    sys.columns c
JOIN    sys.objects o
    ON o.object_id = c.object_id
WHERE   c.NAME LIKE 'xxx'
 
 
-- get columns in table
SELECT o.name, c.name FROM sys.columns c
JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.NAME = 'ws_load_col_v'  
 
 
-- get columns in table, including type, size, is_nullable
SELECT  o.name TableName,
        c.name ColumnName,
        t.name DataType,
        c.max_length, c.is_nullable, c.* FROM sys.columns c
JOIN sys.objects o ON o.object_id = c.object_id
JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.NAME = 'ws_load_col_v' ORDER BY column_id
 
 
-- get indexes on table
SELECT o.name, i.* FROM sys.indexes i
JOIN  sys.objects o ON o.object_id = i.object_id
WHERE o.NAME = 'xxx'
 
 
-- system processes (sp_who2)
SELECT * FROM sys.sysprocesses
WHERE 
	loginame like 'domain\user%'
	AND program_name NOT LIKE 'Microsoft SQL Server Management Studio%'  -- exclude ssms

 
-- find stored procs that contains specified text
SELECT DISTINCT OBJECT_SCHEMA_NAME(id), OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%xxx%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1
    AND [text] LIKE '%xxx%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1
    AND OBJECT_NAME(id) NOT LIKE 'update%'
ORDER BY 1
 
 
-- get stored proc text (code)
EXEC sp_helptext 'xxx.yyy'
 
 
-- insert stored proc text into table variable (also works for temp table and real table)
DECLARE @procText TABLE (Text VARCHAR(MAX))
INSERT INTO @procText(Text) EXEC sp_helpText 'xxx.yyy'
SELECT * FROM @procText
 
-- get SQL Server Version
SELECT @@version

 
----------------------------------------------------------------------------------------------------
-- <<< all objects >>>
----------------------------------------------------------------------------------------------------
SELECT COUNT(*) FROM ws_obj_object
SELECT * FROM ws_obj_object
SELECT TOP 1000 * FROM ws_obj_object
WHERE oo_name LIKE '%xxx'
 
 
-- object types
SELECT * FROM ws_obj_type
/* The more common object types:
ot_type_key ot_description
1         Procedure
2         Kpi Fact Table
3         Host Script
4         Template
5         Fact Table
6         Dimension
7         Stage Table
8         Load Table
9         Aggregate
10         Index
11         Connection
12         Dimension View
13         Export
14         Group
15         Cube
16         Cube Virtual
17         Report
18         View
*/
 
----------------------------------------------------------------------------------------------------
-- <<< checkouts >>>
----------------------------------------------------------------------------------------------------
-- objects checked out
SELECT  o.oo_name, c.* FROM ws_obj_checkout c
JOIN    ws_obj_object o
    ON  o.oo_obj_key = c.oc_obj_key
WHERE   o.oo_name LIKE 'stage_customeradd%'

-- objects checked out by user
SELECT  o.oo_name, c.* FROM ws_obj_checkout c
JOIN      ws_obj_object o
    ON    o.oo_obj_key = c.oc_obj_key
WHERE   oc_checkout_by = 'user name'
ORDER BY oc_checkout_by
ORDER BY oc_checkout_till DESC  -- order by checkout date (latest first) assuming checkout_till is default
 
 
-- objects checked out by object name
SELECT  o.oo_name, c.* FROM ws_obj_checkout c
JOIN    ws_obj_object o
    ON o.oo_obj_key = c.oc_obj_key
WHERE   o.oo_name LIKE 'dim%date%'
 
 
-- objects checkout status by project
SELECT  oo_name             ObjectName,
        oc_checkout_by      CheckedOutBy,
        op_description      Project,
        oc_checkout_reason  CheckOutReason
        -- ,ws_obj_object.*
        -- ,ws_obj_checkout.*
FROM    ws_obj_object
LEFT JOIN ws_obj_checkout ON  oo_obj_key = oc_obj_key
JOIN    ws_obj_pro_map ON opm_obj_key = oo_obj_key
JOIN    ws_obj_project ON op_project_key = opm_project_key
WHERE   op_description LIKE 'xxx%'
        -- AND oo_name LIKE 'stage%'
        AND oo_type_key NOT IN (1,10)  -- exclude procedures (1) and indexes (10)
ORDER BY 1
 
SELECT * FROM ws_obj_checkout_history -- 0 rows
 
----------------------------------------------------------------------------------------------------
-- <<< Connections >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_dbc_connect
--WHERE dc_type = 'D'       -- Database Connections
--WHERE dc_type = 'W'       -- Windows Connections
WHERE dc_type = 'O'         -- ODBC Connections
--WHERE dc_type = 'M'       -- SSAS 2005+ Connections
--WHERE dc_type = 'U' ??    -- Unix Connections
--WHERE dc_type = 'A' ??    -- SSAS 2000 Connections
SELECT DISTINCT dc_type FROM ws_dbc_connect
 
 
----------------------------------------------------------------------------------------------------
-- <<< Load tables >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_load_tab ORDER BY 2
SELECT lt_table_name, lt_tablespace FROM ws_load_tab WHERE lt_table_name LIKE '%xxx%'
 
 
-- load types
SELECT DISTINCT lt_type FROM ws_load_tab
SELECT * FROM ws_load_tab WHERE lt_type = 'N'
SELECT lt_type, * FROM ws_load_tab WHERE lt_table_name LIKE 'Load%'
/*
O = ODBC
N = Native ODBC
E = Externally Loaded
F = File Load
D = Datebase Link Load
S = Script Bases Load
*/
 
 
-- Find load tables with columns named xxx
SELECT  lt_table_name,
        lc_col_name
FROM    ws_load_col
JOIN    ws_load_tab
    ON  lt_obj_key = lc_obj_key
WHERE   lc_col_name LIKE '%xxx%'
ORDER BY lc_order
 
 
-- Load table columns
SELECT  TOP 100 * FROM ws_load_col
SELECT  lt_table_name,
        lc_col_name,
        lc_data_type
FROM    ws_load_col
JOIN    ws_load_tab
        ON lt_obj_key = lc_obj_key
WHERE   lt_table_name LIKE 'xxx'
ORDER BY lc_order
 
 
-- load table data types
SELECT  lt_table_name,
        lc_col_name,
        lc_data_type
FROM    ws_load_col
JOIN    ws_load_tab
        ON lt_obj_key = lc_obj_key
        AND lt_table_name LIKE 'xxx'
WHERE   1=1
        -- AND lc_col_name LIKE '%amount'
        AND lc_data_type LIKE 'money'
ORDER BY 2    
-- load table trigger file
SELECT lt_trig_name, * FROM ws_load_tab
WHERE 1=1
    -- AND lt_table_name LIKE 'load_dmrt_acc%'
    AND lt_trig_name LIKE '%dat'
    AND (lt_trig_name_rename <> '' OR lt_trig_path_rename <> '')
   
SELECT lt_trig_name, * FROM ws_load_tab
--WHERE lt_table_name LIKE 'load_%man%'
WHERE lt_trig_name IS NOT NULL
ORDER BY 2
 
 
----------------------------------------------------------------------------------------------------
-- <<< Stage tables >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_stage_tab ORDER BY 2
 
-- stage table by name
SELECT * FROM ws_stage_tab
WHERE st_table_name LIKE '%xxx%'
 
 
-- stage table by short name
SELECT * FROM ws_stage_tab
WHERE st_short_name LIKE 'xxx%'
 
 
-- stage tables ordered by create date
SELECT * FROM ws_stage_tab ORDER BY st_created DESC
 
 
-- stage tables tablespace
SELECT st_table_name, st_tablespace FROM ws_stage_tab WHERE st_table_name LIKE 'xxx'
 
 
-- stage tables containing specific text in the where clause
SELECT  st_where, * FROM ws_stage_tab
WHERE   st_where LIKE '%xxx%' 
    AND st_table_name LIKE 'Stage_%'
ORDER BY 3
 
 
-- stage table types
SELECT DISTINCT st_type_ind FROM ws_stage_tab
SELECT  *
FROM    ws_stage_tab
WHERE   st_type_ind = 'P'
 
 
-- Stage table columns
SELECT  TOP 100 * FROM ws_stage_col
 
-- Stage table columns by table name
SELECT  ws_stage_col.*
FROM    ws_stage_col
JOIN    ws_stage_tab ON st_obj_key = sc_obj_key
WHERE   st_table_name LIKE 'xxx'
ORDER BY sc_order
 
 
-- Stage table columns by table and column name
SELECT  st_table_name,              -- table name
        sc_col_name,                -- column name
        sc_data_type,               -- data type
        sc_transform_code           -- transformation
FROM    ws_stage_col
JOIN    ws_stage_tab
        ON st_obj_key = sc_obj_key
-- WHERE   sc_col_name IN ('xxx')
WHERE   sc_col_name LIKE '%source%'
--        AND st_table_name LIKE 'xxx%'
ORDER BY 3
 
 
-- stage table data types
SELECT  sc_col_name,
        sc_data_type
FROM    ws_stage_col
JOIN    ws_stage_tab
        ON st_obj_key = sc_obj_key
        AND st_table_name LIKE 'xxx'
WHERE   1=1
        -- AND sc_col_name LIKE '%amount'
        AND sc_data_type LIKE 'numeric%'
       
SELECT  st_table_name,
        sc_col_name,
        sc_data_type
FROM    ws_stage_col
JOIN    ws_stage_tab
        ON st_obj_key = sc_obj_key
        AND st_table_name LIKE 'xxx'
WHERE   1=1
        -- AND sc_col_name LIKE '%amount'
        AND sc_data_type LIKE 'money'
ORDER BY 2
 
-- source columns for stage tables
SELECT  st_table_name 'Stage Table',
        sc_col_name   'Stage Table Column',
        sc_src_table  'Source Table',
        sc_src_column 'Source Column',
        ws_stage_col.*
FROM    ws_stage_col
JOIN    ws_stage_tab
        ON st_obj_key = sc_obj_key
--WHERE   sc_col_name LIKE '%amount'                    -- by source column
WHERE   st_table_name = 'xxx'       -- by stage table
ORDER BY sc_order
 
 
-- stage table where clause
SELECT st_where FROM ws_stage_tab
WHERE st_table_name LIKE 'xxx'
 
 
-- stage tables in a project
SELECT  st_table_name, st_post_fix, ws_stage_tab.*
FROM    ws_obj_project
JOIN    ws_obj_pro_map
        ON op_project_key = opm_project_key
JOIN    ws_obj_object
        ON opm_obj_key = oo_obj_key
JOIN    ws_stage_tab
        ON st_obj_key = oo_obj_key
WHERE   op_description LIKE 'xxx%'
        AND oo_type_key = 7
ORDER BY 1
 
-- get the update and custom procedures for stage tables
SELECT st_table_name TableName,
    u.oo_name UpdateProc,
    c.oo_name CustomProc
FROM ws_stage_tab s
left join ws_obj_object u on u.oo_obj_key = s.st_update_key
left join ws_obj_object c on c.oo_obj_key = s.st_build_key


-- get parameters used by stage tables
SELECT 
    st_table_name, 
    ta_text_1                                    -- comma seperated parameter list
FROM ws_stage_tab
JOIN ws_table_attributes 
    ON st_obj_key = ta_obj_key
WHERE 
    st_table_name LIKE 'stageXXX%'                -- stage table name
    AND ta_type = 'P'                            -- P = parameter

-- stage tables containing specific text in the where clause
SELECT  st_where, * FROM ws_stage_tab
WHERE   st_where LIKE '%xxx%' 
    AND st_table_name LIKE 'Stage_%'
ORDER BY 3
 
 
----------------------------------------------------------------------------------------------------
-- <<< Dimension tables >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_dim_tab WHERE dt_table_name LIKE 'Dim_%'

SELECT  dt_table_name, dt_type_ind, *
FROM    ws_dim_tab t
WHERE   t.dt_table_name LIKE '%xxx%'
        -- AND dt_type_ind IN ('D', 'V', 'C')   
ORDER BY t.dt_table_name
-- dim table types
SELECT dt_type_ind, COUNT(*) FROM ws_dim_tab GROUP BY dt_type_ind
 
/*
C = Changing dim
D = Dimension
F = Fact view
M = Mapping table
O = Other table view
R = Ranged
T = Time
U = User defined view
V = Dimension view
*/
 
SELECT * FROM ws_dim_tab WHERE dt_type_ind = 'F'

SELECT * FROM ws_dim_col
 
-- Dimension table columns
SELECT  dt_table_name,
        dc_col_name
FROM    ws_dim_col
JOIN    ws_dim_tab ON dt_obj_key = dc_obj_key
WHERE   dt_table_name LIKE '%xxx%'
        AND dc_col_name LIKE '%zzz%'  
ORDER BY 1       

-- dimension table all columns
SELECT ws_dim_col.* 
FROM ws_dim_col
JOIN ws_dim_tab 
	ON ws_dim_col.dc_obj_key = ws_dim_tab.dt_obj_key
WHERE dt_table_name LIKR 'Dim_xxx%'
ORDER BY dc_order

-- get all fact tables that reference a dimension
SELECT DISTINCT ws_fact_tab.ft_table_name 
FROM ws_fact_tab
JOIN ws_fact_col 
	ON ft_obj_key = fc_obj_key
WHERE fc_src_table LIKE 'xxx'

-- get all stage tables that reference a dimension
SELECT DISTINCT ws_stage_tab.st_table_name 
FROM ws_stage_tab
JOIN ws_stage_col 
	ON st_obj_key = sc_obj_key
WHERE sc_src_table LIKE 'xxx'

-- get all dimensions that reference a dimension
SELECT DISTINCT ws_dim_tab.dt_table_name 
FROM ws_dim_tab
JOIN ws_dim_col 
	ON dt_obj_key = dc_obj_key
WHERE dc_src_table LIKE 'xxx'
-- ORDER BY dc_order


----------------------------------------------------------------------------------------------------
-- << Data Store >> 
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_ods_tab 
-- WHERE ot_table_name LIKE 'Dim_CQC%'
ORDER BY 2


 
----------------------------------------------------------------------------------------------------
-- << Fact tables >> 
----------------------------------------------------------------------------------------------------
-- all fact tables
SELECT * FROM ws_fact_tab
SELECT * FROM dss_fact_table
 
-- fact tables by name
SELECT * FROM ws_fact_tab WHERE ft_table_name LIKE '%Fact_%'
 
-- data types
SELECT  ft_table_name,
        fc_col_name,
        fc_data_type
FROM    ws_fact_col
JOIN    ws_fact_tab
        ON ft_obj_key = fc_obj_key
        -- AND ft_table_name LIKE 'Fact_xxx_%'
WHERE   1=1
        AND fc_col_name LIKE '%xxx'
        -- AND fc_data_type LIKE 'money'
 
-- Fact table columns by table/column name
SELECT  ft_table_name,
        fc_col_name,
        ws_fact_col.*
FROM    ws_fact_col
JOIN    ws_fact_tab ON ft_obj_key = fc_obj_key
WHERE   ft_table_name LIKE '%'
        AND fc_col_name LIKE 'xxx%'
ORDER BY fc_order
 
-- Fact table columns by source table
SELECT  ft_table_name,
        fc_col_name,
        fc_src_table,
        fc_src_column
FROM    ws_fact_col
JOIN    ws_fact_tab ON ft_obj_key = fc_obj_key
WHERE   fc_src_table LIKE 'dss%'
        -- AND fc_src_column IN ('xx', 'zz')       
ORDER BY fc_src_table, fc_src_column
 
 
-- Fact table types
SELECT ft_type_ind, COUNT(*) FROM ws_fact_tab GROUP BY ft_type_ind
/*
   D: Detail fact table
   E: Exchange partition
   P: Detail partitioned
   R: Rollup fact table
*/
 
 
-- Fact tables by type
SELECT * FROM ws_fact_tab
WHERE ft_type_ind = 'P'
ORDER BY ft_created DESC
 
 
-- fact tables in a project
SELECT  ft_table_name, ft_post_fix, ws_fact_tab.*
FROM    ws_obj_project
JOIN    ws_obj_pro_map
        ON op_project_key = opm_project_key
JOIN    ws_obj_object
        ON opm_obj_key = oo_obj_key
JOIN    ws_fact_tab
        ON ft_obj_key = oo_obj_key
WHERE   op_description LIKE 'xxx%'
        AND oo_type_key = 5
ORDER BY 1
 
 
----------------------------------------------------------------------------------------------------
-- <<< Indexes >>>
----------------------------------------------------------------------------------------------------
-- all indexes
SELECT * FROM ws_index_header
 
 
-- indexes in a project
SELECT  ih_index_name,
        ih_table,
        ih_tablespace,
        ih_clustered_ind,
        ws_index_header.*
FROM    ws_obj_project
JOIN    ws_obj_pro_map
        ON op_project_key = opm_project_key
JOIN    ws_obj_object
        ON opm_obj_key = oo_obj_key
JOIN    ws_index_header
        ON ih_obj_key = oo_obj_key
WHERE   op_description LIKE 'xxx%'
ORDER BY 3
 
-- actual indexes on table
SELECT o.name, i.* FROM sys.indexes i
JOIN  sys.objects o ON o.object_id = i.object_id
WHERE o.NAME = 'xxx'
 
 
----------------------------------------------------------------------------------------------------
-- <<< Dependancies >>>
----------------------------------------------------------------------------------------------------
 
-- find tables that depend directly on a specified table
DECLARE @tableName VARCHAR(64)
SET @tableName = 'xxx'
SELECT  DISTINCT oo_name 'Table'
FROM    ws_obj_object
LEFT JOIN ws_agg_col    ON ac_obj_key = oo_obj_key AND ac_src_table = @tableName
LEFT JOIN ws_dim_col    ON dc_obj_key = oo_obj_key AND dc_src_table = @tableName
LEFT JOIN ws_export_col ON ec_obj_key = oo_obj_key AND ec_src_table = @tableName
LEFT JOIN ws_fact_col   ON fc_obj_key = oo_obj_key AND fc_src_table = @tableName
LEFT JOIN ws_join_col   ON jc_obj_key = oo_obj_key AND jc_src_table = @tableName
LEFT JOIN ws_load_col   ON lc_obj_key = oo_obj_key AND lc_src_table = @tableName
LEFT JOIN ws_normal_col ON nc_obj_key = oo_obj_key AND nc_src_table = @tableName
LEFT JOIN ws_ods_col    ON oc_obj_key = oo_obj_key AND oc_src_table = @tableName
LEFT JOIN ws_stage_col  ON sc_obj_key = oo_obj_key AND sc_src_table = @tableName
LEFT JOIN ws_view_col   ON vc_obj_key = oo_obj_key AND vc_src_table = @tableName
WHERE COALESCE( sc_obj_key, lc_obj_key, ac_obj_key, dc_obj_key, ec_obj_key, fc_obj_key,
                jc_obj_key, nc_obj_key, oc_obj_key, vc_obj_key) IS NOT NULL
 
 
-- find tables that a specified table depends on directly
DECLARE @tableName VARCHAR(64)
SET @tableName = 'xxx'
;WITH X AS
(
    SELECT  DISTINCT
        COALESCE(sc_src_table, dc_src_table, ac_src_table, fc_src_table, oc_src_table, vc_src_table, lc_src_table) SourceTable
    FROM ws_obj_object
    LEFT JOIN ws_stage_col  ON sc_obj_key = oo_obj_key AND oo_name = @tableName AND sc_src_table <> ''
    LEFT JOIN ws_dim_col    ON dc_obj_key = oo_obj_key AND oo_name = @tableName AND dc_src_table <> ''
    LEFT JOIN ws_agg_col    ON ac_obj_key = oo_obj_key AND oo_name = @tableName AND ac_src_table <> ''
    LEFT JOIN ws_fact_col   ON fc_obj_key = oo_obj_key AND oo_name = @tableName AND fc_src_table <> ''
    LEFT JOIN ws_ods_col    ON oc_obj_key = oo_obj_key AND oo_name = @tableName AND oc_src_table <> ''
    LEFT JOIN ws_view_col   ON vc_obj_key = oo_obj_key AND oo_name = @tableName AND vc_src_table <> ''
    LEFT JOIN ws_load_col   ON lc_obj_key = oo_obj_key AND oo_name = @tableName AND lc_src_table <> ''
)
SELECT * FROM X WHERE SourceTable IS NOT NULL
 
 
----------------------------------------------------------------------------------------------------
-- <<< Versions >>>
----------------------------------------------------------------------------------------------------
 
-- all version tables
SELECT  * FROM sys.objects
WHERE   name LIKE '%[_]v'
        AND type = 'U'
ORDER BY 1
 
 
-- object versions by object name
SELECT * FROM ws_obj_versions
WHERE ov_obj_name LIKE 'xxx'
ORDER BY 1 DESC
 
 
-- load tables versions by table name
SELECT  lt_table_name, lt_version_no, lt_created, lt_updated, lt_dbcreated_dt, lt_dbmodified_dt, *
FROM    ws_load_tab_v
WHERE   lt_table_name LIKE 'Load_xxx'
ORDER BY lt_version_no
SELECT TOP 100 *
FROM ws_load_tab_v
WHERE lt_table_name LIKE 'xxx' ORDER BY 1 DESC
 
 
-- compare columns of 2 versioned load tables
SELECT * FROM ws_load_col_v 
WHERE lc_version_no IN (9, 6)
ORDER BY lc_col_key, lc_version_no
-- compare columns of 2 versioned load tables --> show changed columns only first table only
-- !!!!!!!!!! NEEDS SOME MORE TESTING WITH NULLS ETC. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
    -- lc_version_no,
    lc_obj_key,
    lc_col_key,
    lc_col_name,
    lc_display_name,
    lc_data_type,
    lc_nulls_flag,
    lc_numeric_flag,
    lc_additive_flag,
    lc_attribute_flag,
    lc_format,
    lc_src_table,
    lc_src_column,
    CONVERT( VARCHAR(MAX),lc_src_strategy) lc_src_strategy,
    lc_transform_type,
    CONVERT( VARCHAR(MAX),lc_transform_model) lc_transform_model,
    CONVERT( VARCHAR(MAX),lc_transform_code)  lc_transform_code,
    lc_order,
    CONVERT( VARCHAR(MAX),lc_attributes   ) lc_attributes ,
    CONVERT( VARCHAR(MAX),lc_min_value    ) lc_min_value  ,
    CONVERT( VARCHAR(MAX),lc_max_value    ) lc_max_value  ,
    CONVERT( VARCHAR(MAX),lc_value_range  ) lc_value_range,
    CONVERT( VARCHAR(MAX),lc_exception    ) lc_exception  ,
    CONVERT( VARCHAR(MAX),lc_doc_1        ) lc_doc_1      ,
    CONVERT( VARCHAR(MAX),lc_doc_2        ) lc_doc_2      ,
    CONVERT( VARCHAR(MAX),lc_doc_3        ) lc_doc_3      ,
    CONVERT( VARCHAR(MAX),lc_doc_4        ) lc_doc_4      ,
    CONVERT( VARCHAR(MAX),lc_doc_5        ) lc_doc_5      ,
    CONVERT( VARCHAR(MAX),lc_doc_6        ) lc_doc_6      ,
    CONVERT( VARCHAR(MAX),lc_doc_7        ) lc_doc_7      ,
    CONVERT( VARCHAR(MAX),lc_doc_8        ) lc_doc_8      ,
    CONVERT( VARCHAR(MAX),lc_doc_9        ) lc_doc_9      ,
    CONVERT( VARCHAR(MAX),lc_doc_10       ) lc_doc_10     ,
    CONVERT( VARCHAR(MAX),lc_doc_11       ) lc_doc_11     ,
    CONVERT( VARCHAR(MAX),lc_doc_12       ) lc_doc_12     ,
    CONVERT( VARCHAR(MAX),lc_doc_13       ) lc_doc_13     ,
    CONVERT( VARCHAR(MAX),lc_doc_14       ) lc_doc_14     ,
    CONVERT( VARCHAR(MAX),lc_doc_15       ) lc_doc_15     ,
    CONVERT( VARCHAR(MAX),lc_doc_16       ) lc_doc_16     ,
    lc_primary_index_ind,
    lc_default_value,
    lc_case_flag,
    lc_title,
    lc_compress_flag,
    CONVERT( VARCHAR(MAX),lc_compress_value) lc_compress_value,
    lc_comments,
    lc_update_flag,
    lc_display_type
FROM ws_load_col_v  WHERE lc_version_no = ???
EXCEPT
SELECT
    -- lc_version_no,
    lc_obj_key,
    lc_col_key,
    lc_col_name,
    lc_display_name,
    lc_data_type,
    lc_nulls_flag,
    lc_numeric_flag,
    lc_additive_flag,
    lc_attribute_flag,
    lc_format,
    lc_src_table,
    lc_src_column,
    CONVERT( VARCHAR(MAX),lc_src_strategy) lc_src_strategy,
    lc_transform_type,
    CONVERT( VARCHAR(MAX),lc_transform_model) lc_transform_model,
    CONVERT( VARCHAR(MAX),lc_transform_code)  lc_transform_code,
    lc_order,
    CONVERT( VARCHAR(MAX),lc_attributes   ) lc_attributes ,
    CONVERT( VARCHAR(MAX),lc_min_value    ) lc_min_value  ,
    CONVERT( VARCHAR(MAX),lc_max_value    ) lc_max_value  ,
    CONVERT( VARCHAR(MAX),lc_value_range  ) lc_value_range,
    CONVERT( VARCHAR(MAX),lc_exception    ) lc_exception  ,
    CONVERT( VARCHAR(MAX),lc_doc_1        ) lc_doc_1      ,
    CONVERT( VARCHAR(MAX),lc_doc_2        ) lc_doc_2      ,
    CONVERT( VARCHAR(MAX),lc_doc_3        ) lc_doc_3      ,
    CONVERT( VARCHAR(MAX),lc_doc_4        ) lc_doc_4      ,
    CONVERT( VARCHAR(MAX),lc_doc_5        ) lc_doc_5      ,
    CONVERT( VARCHAR(MAX),lc_doc_6        ) lc_doc_6      ,
    CONVERT( VARCHAR(MAX),lc_doc_7        ) lc_doc_7      ,
    CONVERT( VARCHAR(MAX),lc_doc_8        ) lc_doc_8      ,
    CONVERT( VARCHAR(MAX),lc_doc_9        ) lc_doc_9      ,
    CONVERT( VARCHAR(MAX),lc_doc_10       ) lc_doc_10     ,
    CONVERT( VARCHAR(MAX),lc_doc_11       ) lc_doc_11     ,
    CONVERT( VARCHAR(MAX),lc_doc_12       ) lc_doc_12     ,
    CONVERT( VARCHAR(MAX),lc_doc_13       ) lc_doc_13     ,
    CONVERT( VARCHAR(MAX),lc_doc_14       ) lc_doc_14     ,
    CONVERT( VARCHAR(MAX),lc_doc_15       ) lc_doc_15     ,
    CONVERT( VARCHAR(MAX),lc_doc_16       ) lc_doc_16     ,
    lc_primary_index_ind,
    lc_default_value,
    lc_case_flag,
    lc_title,
    lc_compress_flag,
    CONVERT( VARCHAR(MAX),lc_compress_value) lc_compress_value,
    lc_comments,
    lc_update_flag,
    lc_display_type
FROM ws_load_col_v  WHERE lc_version_no = ???
 
 
-- stage table version columns by column name and source table
SELECT  *
FROM    ws_stage_col_v
WHERE   sc_col_name LIKE '%'
        AND sc_src_table = '???'
ORDER BY 1
 
-- stage table version columns by column name and source table
SELECT  st_table_name,
        sc_col_name,
        sc_data_type
FROM    ws_stage_col_v
JOIN    ws_stage_tab
        ON st_obj_key = sc_obj_key
WHERE   sc_col_name LIKE '%1%'
-- WHERE   sc_col_name LIKE '%identifier%'
        AND st_table_name LIKE '???'
ORDER BY sc_order
 
 
-- stage table versions
SELECT TOP 1000 * FROM ws_stage_tab_v ORDER BY 2
SELECT COUNT(*) FROM ws_stage_tab_v 
 
-- stage table versions by name
SELECT  st_table_name, st_version_no, st_created, st_updated, st_dbcreated_dt, st_dbmodified_dt , * 
FROM    ws_stage_tab_v
WHERE   st_table_name LIKE 'Stage_xxx'
ORDER BY ws_stage_tab_v.st_version_no
 
 
-- Stage table column versions by version number and column name
SELECT  * FROM ws_stage_col_v
WHERE   sc_version_no IN (123, 456)
        AND sc_col_name = 'xxx'
 
 
-- Stage table column versions by column key
SELECT  * FROM ws_stage_col_v
WHERE   sc_col_key = 123
ORDER BY sc_version_no
 
 
-- Stage table column versions by column name
SELECT DISTINCT sc_version_no,  st_table_name, sc_col_name, sc_display_name, sc_data_type, sc_nulls_flag, sc_numeric_flag,
 sc_additive_flag, sc_attribute_flag, sc_format, sc_src_table, sc_src_column, sc_order, sc_key_type,
 sc_join_flag, sc_business_key_ind, sc_transform_type
FROM    ws_stage_col_v
JOIN    ws_stage_tab_v ON ws_stage_col_v.sc_obj_key = ws_stage_tab_v.st_obj_key
WHERE   -- st_table_name LIKE 'stage_xxx'
        --AND sc_col_name LIKE '%Int%'
        --AND
        --sc_src_table LIKE '%xxx'
        sc_src_column LIKE '%xxx'
ORDER BY
    st_table_name,
    sc_src_table,
    sc_version_no
 
-- procedures versions
SELECT *
FROM    ws_pro_header_v
WHERE ph_name = 'update_fact_xxx'
ORDER BY ph_version_no DESC
 
 
-- get the procedure lines of a specific procedure, by version number or not
SELECT  *
FROM    ws_pro_line_v
JOIN    ws_pro_header_v
        ON ph_version_no = pl_version_no AND ph_obj_key = pl_obj_key
        AND ph_name = 'update_factxxx'
ORDER BY pl_version_no DESC, pl_line_no
WHERE   pl_version_no =
       
 
 
-- Dependancies
 
-- find tables that depend directly on a specified table
-- !! uses meta data only, does not look at the stored procs
GO
DECLARE @tableName VARCHAR(64)
SET @tableName = 'xxx'
SELECT  DISTINCT oo_name 'Table'
FROM    ws_obj_object
LEFT JOIN ws_agg_col    ON ac_obj_key = oo_obj_key AND ac_src_table = @tableName
LEFT JOIN ws_dim_col    ON dc_obj_key = oo_obj_key AND dc_src_table = @tableName
LEFT JOIN ws_export_col ON ec_obj_key = oo_obj_key AND ec_src_table = @tableName
LEFT JOIN ws_fact_col   ON fc_obj_key = oo_obj_key AND fc_src_table = @tableName
LEFT JOIN ws_join_col   ON jc_obj_key = oo_obj_key AND jc_src_table = @tableName
LEFT JOIN ws_load_col   ON lc_obj_key = oo_obj_key AND lc_src_table = @tableName
LEFT JOIN ws_normal_col ON nc_obj_key = oo_obj_key AND nc_src_table = @tableName
LEFT JOIN ws_ods_col    ON oc_obj_key = oo_obj_key AND oc_src_table = @tableName
LEFT JOIN ws_stage_col  ON sc_obj_key = oo_obj_key AND sc_src_table = @tableName
LEFT JOIN ws_view_col   ON vc_obj_key = oo_obj_key AND vc_src_table = @tableName
WHERE COALESCE( sc_obj_key, lc_obj_key, ac_obj_key, dc_obj_key, ec_obj_key, fc_obj_key,
                jc_obj_key, nc_obj_key, oc_obj_key, vc_obj_key) IS NOT NULL
 
 
-- find tables that a specified table depends on directly
GO
DECLARE @tableName VARCHAR(64)
--SET @tableName = 'Stage_xxx'
--SET @tableName = 'fact_xxx'
;WITH X AS
(
    SELECT  DISTINCT
        COALESCE(sc_src_table, dc_src_table, ac_src_table, fc_src_table, oc_src_table, vc_src_table, lc_src_table) SourceTable
    FROM ws_obj_object
    LEFT JOIN ws_stage_col  ON sc_obj_key = oo_obj_key AND oo_name = @tableName AND sc_src_table <> ''
    LEFT JOIN ws_dim_col    ON dc_obj_key = oo_obj_key AND oo_name = @tableName AND dc_src_table <> ''
    LEFT JOIN ws_agg_col    ON ac_obj_key = oo_obj_key AND oo_name = @tableName AND ac_src_table <> ''
    LEFT JOIN ws_fact_col   ON fc_obj_key = oo_obj_key AND oo_name = @tableName AND fc_src_table <> ''
    LEFT JOIN ws_ods_col    ON oc_obj_key = oo_obj_key AND oo_name = @tableName AND oc_src_table <> ''
    LEFT JOIN ws_view_col   ON vc_obj_key = oo_obj_key AND oo_name = @tableName AND vc_src_table <> ''
    LEFT JOIN ws_load_col   ON lc_obj_key = oo_obj_key AND oo_name = @tableName AND lc_src_table <> ''
)
SELECT * FROM X WHERE SourceTable IS NOT NULL
 
 
/*
2 X Kpi Fact Table 
5 F Fact Table
6 D Dimension
7 S Stage Table
8 L Load Table
9 A Aggregate
12 V Dimension View
18 W View
*/
 
-- find all dimensions (including views) that depends on a specified table
SELECT DISTINCT oo_name
FROM    ws_obj_object
LEFT JOIN ws_dim_col   
    ON dc_obj_key = oo_obj_key
    AND dc_src_table = 'xxx'
WHERE   dc_obj_key IS NOT NULL
 
-- find all fact tables that uses specified dimensions
SELECT  DISTINCT oo_name
FROM    ws_obj_object
JOIN    ws_fact_col   
    ON fc_obj_key = oo_obj_key
    AND fc_src_table IN ('xxx','yyy','zzz')
 
 
----------------------------------------------------------------------------------------------------
-- <<< Groups and Projects >>>
----------------------------------------------------------------------------------------------------

-- all groups
SELECT * FROM ws_obj_group
 
-- all projects
SELECT * FROM ws_obj_project ORDER BY op_create_date DESC
 
SELECT op_description, op_purpose FROM ws_obj_project ORDER BY 1
SELECT op_description, op_purpose, * FROM ws_obj_project WHERE op_description LIKE 'zzz%' ORDER BY 1
 
 
-- project and create user
SELECT op_description, ua_name, ws_obj_project.*
FROM    ws_obj_project
LEFT JOIN    ws_user_adm ON ua_user_key = op_create_user_key
-- WHERE   op_description LIKE 'zzz%'
ORDER BY op_create_date DESC
 

-- get the groups and projects that an object belongs to
SELECT 
	  ws_obj_group.og_description 'Group'
	, ws_obj_project.op_description 'Project'
	, oo_name 'Name'
FROM ws_obj_object
JOIN ws_obj_pro_map 
	ON ws_obj_pro_map.opm_obj_key = ws_obj_object.oo_obj_key
JOIN ws_obj_project
	ON ws_obj_pro_map.opm_project_key = ws_obj_project.op_project_key
JOIN ws_pro_gro_map
	ON ws_pro_gro_map.pgm_project_key = ws_obj_project.op_project_key
JOIN ws_obj_group
	ON ws_obj_group.og_group_key = ws_pro_gro_map.pgm_group_key
WHERE 
	oo_name LIKE '%xxx%'
	-- oo_name LIKE '%\_TAS\_%' ESCAPE '\'
	--oo_name = 'Dim_LBC_AccountNumber'
	AND oo_type_key IN (5,6,7,8,12,13,18,23,24,26)  -- tables and views
ORDER BY 1,2,3
 
-- all projects that an object is in
SELECT oo_name, op_description
FROM ws_obj_project
JOIN ws_obj_pro_map ON op_project_key = opm_project_key
JOIN ws_obj_object ON opm_obj_key = oo_obj_key
WHERE oo_name IN ('zzz')
ORDER BY 1,2
 
 
-- all objects in a project
SELECT oo_name, op_description, *
FROM ws_obj_project
JOIN ws_obj_pro_map ON op_project_key = opm_project_key
JOIN ws_obj_object ON opm_obj_key = oo_obj_key
WHERE op_description LIKE 'xxx%'
ORDER BY 1
 
-- groups, projects and objects
-- provide either the object/project/group name
SELECT
	og_description, -- group name
	op_description, -- project name
	oo_name -- object name
FROM ws_obj_project
JOIN ws_obj_pro_map ON op_project_key = opm_project_key
JOIN ws_obj_object ON opm_obj_key = oo_obj_key
JOIN ws_pro_gro_map ON pgm_project_key = op_project_key
JOIN ws_obj_group ON pgm_group_key = og_group_key
WHERE op_description LIKE 'xxx%' --- project name
-- WHERE og_description LIKE 'xxx%' --- group name
-- WHERE oo_name LIKE 'xxx%' --- object name
ORDER BY 1,2,3
 
-- main object types
1	Procedure
3	Host Script
5	Fact Table
6	Dimension
7	Stage Table
8	Load Table
9	Aggregate
11	Connection
12	Dimension View
13	Export
18	View
23	OLAP Cube
24	OLAP Dimension
26	Data Store
50	Job
51	Task
 
----------------------------------------------------------------------------------------------------
-- <<< procedures and scripts >>>
----------------------------------------------------------------------------------------------------
SELECT TOP 1000 * FROM ws_pro_header
SELECT TOP 1000 * FROM ws_pro_line

-- find the update and custom procs for stage tables
SELECT st_table_name TableName,
u.oo_name UpdateProc,
c.oo_name CustomProc
FROM ws_stage_tab s
left join ws_obj_object u on u.oo_obj_key = s.st_update_key
left join ws_obj_object c on c.oo_obj_key = s.st_build_key

-- find all procs that contains specified text 
SELECT ph_name, pl_line_no, pl_line 
FROM ws_pro_line
JOIN ws_pro_header h ON pl_obj_key = ph_obj_key
WHERE pl_line LIKE '%xxx\_%' ESCAPE '\'
ORDER BY 1,2

SELECT TOP 1000 * FROM ws_scr_header
SELECT TOP 1000 * FROM ws_scr_line
SELECT TOP 1000 * FROM ws_script_sequence

-- all procs
SELECT * FROM ws_pro_header
 
-- find proc by name
SELECT * FROM ws_pro_header WHERE ph_name LIKE 'xxx%'
 
-- find procs that has been updated since last compile (compile date < update date)
SELECT ph_name, ph_updated, ph_compiled FROM ws_pro_header WHERE ph_updated > ph_compiled
 
-- find procs that have been manually edited
SELECT  ph_name
FROM    ws_pro_header
WHERE   ph_updated IS NOT NULL;
 
 
-- procedure lines (the actual content)
SELECT COUNT(*) FROM ws_pro_line
SELECT TOP 1000 * FROM ws_pro_line
SELECT * FROM ws_pro_line WHERE pl_obj_key = 28 ORDER BY pl_line_no
 
-- find string in procedure  --> show all lines containing target
SELECT  h.ph_name,
        l.pl_line_no,
        l.pl_line
FROM    ws_pro_header h
JOIN    ws_pro_line l ON h.ph_obj_key = l.pl_obj_key
WHERE   pl_line LIKE '%xxx%' 
ORDER BY h.ph_name, l.pl_line_no
 
 
-- find string in procedure --> show number of hits per procedure - if found more than once on 1 line, this will only
-- count it once
SELECT  h.ph_name,
        COUNT(*)
FROM    ws_pro_header h
JOIN    ws_pro_line l ON h.ph_obj_key = l.pl_obj_key
WHERE   pl_line LIKE '%xxx%' 
GROUP BY h.ph_name
ORDER BY 1, 2 DESC
SELECT * FROM ws_pro_gro_map -- ?
 
 
----------------------------------------------------------------------------------------------------
-- <<< Parameters >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM dss_parameter
 
----------------------------------------------------------------------------------------------------
-- <<< Jobs >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_wrk_job_ctrl WHERE wjc_name LIKE 'xxx%'
 
-- all job dependencies
SELECT * FROM ws_wrk_job_dependency
 
-- jobs that a job depend on (find parent jobs)
SELECT * FROM ws_wrk_job_dependency WHERE wjd_child_job_name LIKE 'xxx'
 
-- jobs that depend on a job (find child jobs)
SELECT * FROM ws_wrk_job_dependency WHERE wjd_parent_job_name LIKE 'xxx'
 
-- jobs that a task is in
SELECT  j.wjc_name, t.wtc_name
FROM    ws_wrk_task_ctrl t
JOIN    ws_wrk_job_ctrl j
ON      t.wtc_job_key = j.wjc_job_key
WHERE   t.wtc_name LIKE 'xxx'
 
 -- find all jobs that calls a specified job/task        
SELECT 
	j.wjc_name 'Calling Job',
	t.wtc_name 'Called Job or Task',
    CASE t.wtc_action_type
        WHEN 1   THEN 'Drop'
        WHEN 2   THEN 'Create'
        WHEN 3   THEN 'Drop All Indexes'
        WHEN 4   THEN 'Pre Drop Indexes'
        WHEN 5   THEN 'Load'
        WHEN 6   THEN 'Update'
        WHEN 7   THEN 'Execute'
        WHEN 8   THEN 'Process'
        WHEN 9   THEN 'Build Indexes'
        WHEN 10  THEN 'Build All Indexes'
        WHEN 11  THEN 'SQL Server: Analyze'
        WHEN 12  THEN 'SQL Server: Quick Analyze'
        WHEN 13  THEN 'SQL Server: Stats'
        WHEN 14  THEN 'SQL Server: Quick Stats'
        WHEN 15  THEN 'Initial Build'
        WHEN 18  THEN 'Custom'
        WHEN 19  THEN 'Truncate'
        ELSE 'Something Else'
    END ActionType
FROM ws_wrk_task_ctrl t
JOIN ws_wrk_job_ctrl j ON t.wtc_job_key = j.wjc_job_key
WHERE 
	wtc_name LIKE 'xxx%'

-- find all jobs AND tasks for all tables in a project
SELECT  oo_name, op_description, t.wtc_name, j.wjc_name -- *
FROM    ws_obj_project
JOIN    ws_obj_pro_map ON op_project_key = opm_project_key
JOIN    ws_obj_object ON opm_obj_key = oo_obj_key
LEFT JOIN ws_wrk_task_ctrl t ON wtc_obj_key = oo_obj_key
LEFT JOIN ws_wrk_job_ctrl j ON t.wtc_job_key = j.wjc_job_key
WHERE   op_description LIKE 'zzz%'
        AND oo_type_key IN (5,6,7,8)   -- 5=Fact Table, 6=Dimension, 7=Stage Table, 8=Load Table       
ORDER BY 4
 
-- tasks in a job
SELECT  j.wjc_name,
        t.wtc_name,
        CASE t.wtc_action_type WHEN 8 THEN 'Update' WHEN 18 THEN 'Custom' ELSE 'Unknown' END Action,
        *
FROM    ws_wrk_task_ctrl t
JOIN    ws_wrk_job_ctrl j
ON      t.wtc_job_key = j.wjc_job_key
WHERE   j.wjc_name LIKE 'xxx'
        -- AND t.wtc_name LIKE '%cal%'
ORDER BY t.wtc_order
--ORDER BY t.wtc_name
 
 
-- Descendant Jobs
-- !!!!!! this does not include jobs started by procedures calling Ws_Job_Create !!!!!! --
DECLARE @jobName VARCHAR(255)
SET @jobName = 'job name'
;WITH Jobs( JobName, TaskName, JobKey, TaskKey, TaskType, ActionType, TaskOrder)  AS
(
SELECT  j.wjc_name,
        t.wtc_name,
        wtc_job_key,
        wtc_task_key,
        wtc_type,
        wtc_action_type,
        wtc_order
FROM    ws_wrk_task_ctrl t
JOIN    ws_wrk_job_ctrl j
        ON t.wtc_job_key = j.wjc_job_key
WHERE   j.wjc_name = @jobName
        --AND wtc_type = 'J'
       
UNION ALL
SELECT  j.wjc_name,
        t.wtc_name,
        wtc_job_key,
        wtc_task_key,
        wtc_type,
        wtc_action_type,
        wtc_order
FROM    ws_wrk_task_ctrl t
JOIN    ws_wrk_job_ctrl j
        ON t.wtc_job_key = j.wjc_job_key
JOIN    Jobs
        ON j.wjc_name = TaskName    
--        AND wtc_type = 'J'
--WHERE  
        --AND wtc_type = 'J'
       
)
--SELECT TaskName, COUNT(*) FROM Jobs GROUP BY TaskName ORDER BY 2 DESC
--SELECT * FROM Jobs ORDER BY TaskOrder
SELECT
    JobName,
    TaskName,
    CASE ActionType
        WHEN 1   THEN 'Drop'
        WHEN 2   THEN 'Create'
        WHEN 3   THEN 'Drop All Indexes'
        WHEN 4   THEN 'Pre Drop Indexes'
        WHEN 5   THEN 'Load'
        WHEN 6   THEN 'Update'
        WHEN 7   THEN 'Execute'
        WHEN 8   THEN 'Process'
        WHEN 9   THEN 'Build Indexes'
        WHEN 10  THEN 'Build All Indexes'
        WHEN 11  THEN 'SQL Server: Analyze'
        WHEN 12  THEN 'SQL Server: Quick Analyze'
        WHEN 13  THEN 'SQL Server: Stats'
        WHEN 14  THEN 'SQL Server: Quick Stats'
        WHEN 15  THEN 'Initial Build'
        WHEN 18  THEN 'Custom'
        WHEN 19  THEN 'Truncate'
        ELSE 'Something Else'
    END ActionType,
    JobKey,
    TaskKey,
    TaskType,
    TaskOrder
FROM Jobs
ORDER BY TaskOrder
 
 
 
-- Action Types
--  1   Drop                     Drop table, view or index                            
--  2   Create                     Create table, view or index 
--  3   Drop All Indexes         Drop all indexes on the table                        
--  4   Pre Drop Indexes         Drop all indexes on the table marked as "Pre Drop" 
--  5   Load                     Load the table (Load tables only) 
--  6   Update                     Run the update procedure on the table                
--  7   Execute                     Execute the procedure or host scripts                
--  8   Process                     Pre Drop Indexes then Update and then Build Indexes    
--  9   Build Indexes             Build the indexes on the table marked as "Pre Drop"    
--  10  Build All Indexes         Build all indexes on the table                        
--  11  SQL Server: Analyze                                                             
--  12  SQL Server: Quick Analyze                                                          
--  13  SQL Server: Stats                                                             
--  14  SQL Server: Quick Stats                                                         
--  15  Initial Build             Drop All Indexes then Custom then Build All Indexes 
--  18  Custom                     Run the custom procedure on the table                
--  19  Truncate                 Delete all rows from the table                        
--      Process and Statistics     Process then Default Stats (DB2 only) 
--      DB2: Analyze                                                                 
--      DB2: Quick Analyze                                                                
--      DB2: Stats                                                                     
--      DB2: Quick Stats                                                                
--      Oracle: Analyze                                                                 
--      Oracle: Quick Analyze                                                              
--      Oracle: Stats                                                                 
--      Oracle: Quick Stats                                                             
 
 
-- other job related
SELECT * FROM ws_wrk_job_dependency_run
SELECT * FROM ws_wrk_job_mon
SELECT * FROM ws_wrk_job_publish
SELECT * FROM ws_wrk_job_run
SELECT * FROM ws_wrk_job_thread
 
 
----------------------------------------------------------------------------------------------------
-- <<< Tasks >>>
----------------------------------------------------------------------------------------------------
 
-- all tasks
SELECT * FROM ws_wrk_task_ctrl WHERE wtc_name LIKE 'xxx%'
 
-- task dependencies
SELECT * FROM ws_wrk_dependency
SELECT * FROM ws_wrk_dependency_run
SELECT * FROM ws_wrk_task_run
SELECT * FROM ws_wrk_task_scr_hdr
SELECT * FROM ws_wrk_task_scr_line
 
 
----------------------------------------------------------------------------------------------------
-- <<< Logs >>>
----------------------------------------------------------------------------------------------------
 
-- audit log by job name/message
SELECT * FROM ws_wrk_audit_log
WHERE  
    wa_job LIKE 'xxx%'
    AND wa_message LIKE '%FAIL%'
ORDER BY 1 DESC
-- audit log latest entries
SELECT TOP 100 * FROM ws_wrk_audit_log WHERE wa_sequence = 42 ORDER BY 1 DESC
-- error log  --> audit log is a better place to find errors!
SELECT * FROM ws_wrk_error_log
WHERE   wd_job LIKE 'xxx%'
-- job log by name
SELECT * FROM ws_wrk_job_log
WHERE wjl_name LIKE 'xxx%'
-- job log by sequence
SELECT * FROM ws_wrk_job_log
WHERE wjl_sequence = 83441 -- 86416
-- task log by job sequence
SELECT  *
FROM    ws_wrk_task_log
WHERE   wtl_sequence = 83441 
-- ORDER BY wtl_started
ORDER BY wtl_order
-- running tasks by sequence
SELECT  *
FROM    ws_wrk_task_run
WHERE   wtr_sequence = 83411
-- running and completed tasks by sequence
SELECT  *
FROM    ws_admin_v_task
WHERE   sequence = 83441
ORDER BY [started]
 
 
-- audit log by sequence
SELECT  *
FROM    ws_wrk_audit_log
WHERE   wa_sequence = 83441
ORDER BY 1
 
 
-- task log by task name
SELECT * FROM ws_wrk_task_log
WHERE wtl_name = 'stage_xxx'
ORDER BY wtl_started
-- ORDER BY wtl_order
 
 
 
----------------------------------------------------------------------------------------------------
-- << Other RED system stuff >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_typ_act_map ORDER BY 1,2,3,4
SELECT * FROM ws_obj_action
 
-- Sequence Numbers
SELECT * FROM ws_wrk_sequence order by 2
 
-- all empty, still used?
SELECT * FROM ws_wrk_mon_db
SELECT * FROM ws_wrk_mon_job
SELECT * FROM ws_wrk_mon_log
SELECT * FROM ws_wrk_mon_notify
SELECT * FROM ws_wrk_mon_run
SELECT * FROM ws_wrk_mon_status
SELECT * FROM ws_wrk_msg_type
SELECT * FROM ws_wrk_tab_publish
 
ws_meta_wizard_type
SELECT * FROM ws_obj_type
ws_wrk_msg_type
 
----------------------------------------------------------------------------------------------------
-- <<< audit log >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_wrk_audit_log
WHERE wa_time_stamp > '20120215 15:39:00'
ORDER BY 1
 
----------------------------------------------------------------------------------------------------
-- <<< error log >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_wrk_error_log
WHERE wd_time_stamp > '20120214'
ORDER BY 1 DESC
 
----------------------------------------------------------------------------------------------------
-- <<< Schedulers >>>
----------------------------------------------------------------------------------------------------
SELECT * FROM ws_wrk_scheduler
 
----------------------------------------------------------------------------------------------------
-- <<< WhereScape Users >>>
----------------------------------------------------------------------------------------------------
SELECT  *
FROM    ws_user_adm
ORDER BY 1
 
----------------------------------------------------------------------------------------------------
-- <<< Meta data views >>>
----------------------------------------------------------------------------------------------------
SELECT  *
FROM    ws_admin_v_task
WHERE   sequence = 83441
ORDER BY [started]
 
----------------------------------------------------------------------------------------------------
-- <<< meta tables >>>
----------------------------------------------------------------------------------------------------
-- aggregates
SELECT TOP 1000 * FROM ws_agg_col
SELECT TOP 1000 * FROM ws_agg_tab

-- applications
SELECT TOP 1000 * FROM ws_app
SELECT TOP 1000 * FROM ws_app_obj

-- connections
SELECT TOP 1000 * FROM ws_dbc_connect

-- dimensions
SELECT TOP 1000 * FROM ws_dim_col
SELECT TOP 1000 * FROM ws_dim_tab

-- export
SELECT TOP 1000 * FROM ws_export_col
SELECT TOP 1000 * FROM ws_export_tab

-- fact
SELECT TOP 1000 * FROM ws_fact_col
SELECT TOP 1000 * FROM ws_fact_kpi
SELECT TOP 1000 * FROM ws_fact_tab

-- hierarchies
SELECT TOP 1000 * FROM ws_hie_header
SELECT TOP 1000 * FROM ws_hie_link

-- index
SELECT TOP 1000 * FROM ws_index_col
SELECT TOP 1000 * FROM ws_index_header

-- load
SELECT TOP 1000 * FROM ws_load_act
SELECT TOP 1000 * FROM ws_load_col
SELECT TOP 1000 * FROM ws_load_tab

-- normal
SELECT TOP 1000 * FROM ws_normal_col
SELECT TOP 1000 * FROM ws_normal_tab

-- ods
SELECT TOP 1000 * FROM ws_ods_col
SELECT TOP 1000 * FROM ws_ods_tab

-- views
SELECT TOP 1000 * FROM ws_view_col
SELECT TOP 1000 * FROM ws_view_tab

-- groups and projects
SELECT TOP 1000 * FROM ws_pro_gro_map
SELECT TOP 1000 * FROM ws_obj_group
SELECT TOP 1000 * FROM ws_obj_pro_map
SELECT TOP 1000 * FROM ws_obj_project

-- meta
SELECT TOP 1000 * FROM ws_obj_object
SELECT TOP 1000 * FROM ws_meta
SELECT TOP 1000 * FROM ws_meta_tables

-- users (devs)
SELECT * FROM ws_user_adm WHERE ua_name LIKE 'C%'

-- other
SELECT TOP 1000 * FROM ws_doc_glossary
SELECT TOP 1000 * FROM ws_meta
SELECT TOP 1000 * FROM ws_meta_doc
SELECT TOP 1000 * FROM ws_meta_names
SELECT TOP 1000 * FROM ws_meta_wizard_name
SELECT TOP 1000 * FROM ws_meta_wizard_set
SELECT TOP 1000 * FROM ws_meta_wizard_type
SELECT TOP 1000 * FROM ws_obj_action
SELECT TOP 1000 * FROM ws_obj_checkout
SELECT TOP 1000 * FROM ws_obj_checkout_history
SELECT TOP 1000 * FROM ws_obj_maps
SELECT TOP 1000 * FROM ws_obj_seq
SELECT TOP 1000 * FROM ws_obj_type
SELECT TOP 1000 * FROM ws_obj_versions
SELECT TOP 1000 * FROM ws_file_sequence
SELECT TOP 1000 * FROM ws_job_pro_map
SELECT TOP 1000 * FROM ws_join_col
SELECT TOP 1000 * FROM ws_join_tab
SELECT TOP 1000 * FROM ws_language
SELECT TOP 1000 * FROM ws_language_trans
SELECT TOP 1000 * FROM ws_rep_diagram
SELECT TOP 1000 * FROM ws_retro_col
SELECT TOP 1000 * FROM ws_retro_tab
SELECT TOP 1000 * FROM ws_sec_pol_line
SELECT TOP 1000 * FROM ws_sec_pol_table
SELECT TOP 1000 * FROM ws_sec_pol_user
SELECT TOP 1000 * FROM ws_sec_policy
SELECT TOP 1000 * FROM ws_sec_role
SELECT TOP 1000 * FROM ws_sec_role_table
SELECT TOP 1000 * FROM ws_sec_role_user
SELECT TOP 1000 * FROM ws_sec_table
SELECT TOP 1000 * FROM ws_table_attributes
SELECT TOP 1000 * FROM ws_table_joins
SELECT TOP 1000 * FROM ws_tem_header
SELECT TOP 1000 * FROM ws_tem_line
SELECT TOP 1000 * FROM ws_tran_model
SELECT TOP 1000 * FROM ws_typ_act_map


SELECT TOP 1000 * FROM ws_user_work
SELECT TOP 1000 * FROM ws_user_work_sequence

SELECT TOP 1000 * FROM ws_version_sequence

-- olap
SELECT TOP 1000 * FROM ws_olap_cube
SELECT TOP 1000 * FROM ws_olap_cube_act
SELECT TOP 1000 * FROM ws_olap_cube_act_drill
SELECT TOP 1000 * FROM ws_olap_cube_act_par
SELECT TOP 1000 * FROM ws_olap_cube_calc
SELECT TOP 1000 * FROM ws_olap_cube_dim
SELECT TOP 1000 * FROM ws_olap_cube_dim_att
SELECT TOP 1000 * FROM ws_olap_cube_dim_hie
SELECT TOP 1000 * FROM ws_olap_cube_kpi
SELECT TOP 1000 * FROM ws_olap_cube_mgp
SELECT TOP 1000 * FROM ws_olap_cube_mgp_mea
SELECT TOP 1000 * FROM ws_olap_db
SELECT TOP 1000 * FROM ws_olap_dim
SELECT TOP 1000 * FROM ws_olap_dim_att
SELECT TOP 1000 * FROM ws_olap_dim_att_rel
SELECT TOP 1000 * FROM ws_olap_dim_att_src
SELECT TOP 1000 * FROM ws_olap_dim_hie
SELECT TOP 1000 * FROM ws_olap_dim_hie_level
SELECT TOP 1000 * FROM ws_olap_mgp
SELECT TOP 1000 * FROM ws_olap_mgp_dim
SELECT TOP 1000 * FROM ws_olap_mgp_dim_att
SELECT TOP 1000 * FROM ws_olap_mgp_mea
SELECT TOP 1000 * FROM ws_olap_mgp_part
SELECT TOP 1000 * FROM ws_olap_perspect
SELECT TOP 1000 * FROM ws_olap_perspect_obj
SELECT TOP 1000 * FROM ws_olap_src
SELECT TOP 1000 * FROM ws_olap_trans
SELECT TOP 1000 * FROM ws_olap_trans_obj
SELECT TOP 1000 * FROM 
SELECT TOP 1000 * FROM -- working tables
SELECT TOP 1000 * FROM ws_wrk_audit_log
SELECT TOP 1000 * FROM ws_wrk_dependency
SELECT TOP 1000 * FROM ws_wrk_dependency_run
SELECT TOP 1000 * FROM ws_wrk_error_log
SELECT TOP 1000 * FROM ws_wrk_job_ctrl
SELECT TOP 1000 * FROM ws_wrk_job_dependency
SELECT TOP 1000 * FROM ws_wrk_job_dependency_run
SELECT TOP 1000 * FROM ws_wrk_job_log
SELECT TOP 1000 * FROM ws_wrk_job_mon
SELECT TOP 1000 * FROM ws_wrk_job_publish
SELECT TOP 1000 * FROM ws_wrk_job_run
SELECT TOP 1000 * FROM ws_wrk_job_thread
SELECT TOP 1000 * FROM ws_wrk_mon_db
SELECT TOP 1000 * FROM ws_wrk_mon_job
SELECT TOP 1000 * FROM ws_wrk_mon_log
SELECT TOP 1000 * FROM ws_wrk_mon_notify
SELECT TOP 1000 * FROM ws_wrk_mon_run
SELECT TOP 1000 * FROM ws_wrk_mon_status
SELECT TOP 1000 * FROM ws_wrk_msg_type
SELECT TOP 1000 * FROM ws_wrk_scheduler
SELECT TOP 1000 * FROM ws_wrk_sequence
SELECT TOP 1000 * FROM ws_wrk_tab_publish
SELECT TOP 1000 * FROM ws_wrk_task_ctrl
SELECT TOP 1000 * FROM ws_wrk_task_log
SELECT TOP 1000 * FROM ws_wrk_task_run
SELECT TOP 1000 * FROM ws_wrk_task_scr_hdr
SELECT TOP 1000 * FROM ws_wrk_task_scr_line

-- dss
SELECT TOP 1000 * FROM dss_current_day_details
SELECT TOP 1000 * FROM dss_fact_table
SELECT TOP 1000 * FROM dss_parameter
SELECT TOP 1000 * FROM dss_parameter_Audit
SELECT TOP 1000 * FROM dss_source_system


-- old cube stuff
ws_cube_db
ws_cube_dim
ws_cube_dim_v
ws_cube_drill
ws_cube_drill_v
ws_cube_hie
ws_cube_hie_v
ws_cube_level
ws_cube_level_member
ws_cube_level_member_v
ws_cube_level_v
ws_cube_mea
ws_cube_mea_v
ws_cube_part
ws_cube_part_v
ws_cube_src
ws_cube_tab
ws_cube_tab_v