Every query we run

SELECT 1 AS connected

WITH column_stats AS (
  SELECT
    COUNT(*) AS total_columns,
    COUNTIF(cfp.description IS NOT NULL AND cfp.description != '') AS documented_columns
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS` cfp
  JOIN `your-project.your_dataset.INFORMATION_SCHEMA.TABLES` t
    ON cfp.table_catalog = t.table_catalog
    AND cfp.table_schema = t.table_schema
    AND cfp.table_name = t.table_name
  WHERE t.table_type = 'BASE TABLE'
)
SELECT
  documented_columns,
  total_columns,
  SAFE_DIVIDE(documented_columns, total_columns) AS value
FROM column_stats

WITH base_tables AS (
  SELECT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE'
),
tables_with_pk AS (
  SELECT DISTINCT tc.table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLE_CONSTRAINTS` tc
  WHERE tc.constraint_type = 'PRIMARY KEY'
),
tables_with_id_cols AS (
  SELECT DISTINCT c.table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.COLUMNS` c
  WHERE (LOWER(c.column_name) LIKE '%_id' OR LOWER(c.column_name) LIKE '%_key' OR LOWER(c.column_name) = 'id')
    AND c.is_nullable = 'NO'
),
tables_with_identifiers AS (
  SELECT table_name FROM tables_with_pk
  UNION DISTINCT
  SELECT table_name FROM tables_with_id_cols
)
SELECT
  (SELECT COUNT(*) FROM tables_with_identifiers ti JOIN base_tables bt ON ti.table_name = bt.table_name) AS tables_with_ids,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  SAFE_DIVIDE(
    (SELECT COUNT(*) FROM tables_with_identifiers ti JOIN base_tables bt ON ti.table_name = bt.table_name),
    (SELECT COUNT(*) FROM base_tables)
  ) AS value

WITH col_stats AS (
  SELECT
    COUNT(*) AS total_columns,
    -- Typed = not a semi-structured / generic type. Mirrors the Snowflake check
    -- (which excludes VARIANT/OBJECT/ARRAY); scalar types like STRING/INT count
    -- as typed. (Previously an always-true OR clause made this constant 1.0.)
    COUNTIF(c.data_type != 'JSON' AND c.data_type NOT LIKE 'STRUCT%' AND c.data_type NOT LIKE 'ARRAY%') AS typed_columns
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.COLUMNS` c
  JOIN `your-project.your_dataset.INFORMATION_SCHEMA.TABLES` t
    ON c.table_catalog = t.table_catalog
    AND c.table_schema = t.table_schema
    AND c.table_name = t.table_name
  WHERE t.table_type = 'BASE TABLE'
)
SELECT
  typed_columns,
  total_columns,
  SAFE_DIVIDE(typed_columns, total_columns) AS value
FROM col_stats

WITH base_tables AS (
  SELECT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE'
),
optimized AS (
  -- Partitioning OR clustering both count as access optimization. The previous
  -- version only checked partitioning, undercounting clustered-but-unpartitioned tables.
  SELECT DISTINCT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE is_partitioning_column = 'YES' OR clustering_ordinal_position IS NOT NULL
)
SELECT
  (SELECT COUNT(*) FROM optimized pt JOIN base_tables bt ON pt.table_name = bt.table_name) AS optimized_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  SAFE_DIVIDE(
    (SELECT COUNT(*) FROM optimized pt JOIN base_tables bt ON pt.table_name = bt.table_name),
    (SELECT COUNT(*) FROM base_tables)
  ) AS value

WITH base_tables AS (
  SELECT
    table_id,
    TIMESTAMP_MILLIS(last_modified_time) AS last_modified
  FROM `your-project.your_dataset.__TABLES__`
  WHERE type = 1
)
SELECT
  COUNTIF(last_modified >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)) AS active_tables,
  COUNT(*) AS total_tables,
  SAFE_DIVIDE(
    COUNTIF(last_modified >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)),
    COUNT(*)
  ) AS value
FROM base_tables

WITH base_tables AS (
  SELECT
    table_id,
    TIMESTAMP_MILLIS(last_modified_time) AS last_modified
  FROM `your-project.your_dataset.__TABLES__`
  WHERE type = 1
)
SELECT
  COUNTIF(last_modified >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)) AS fresh_tables,
  COUNT(*) AS total_tables,
  SAFE_DIVIDE(
    COUNTIF(last_modified >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)),
    COUNT(*)
  ) AS value
FROM base_tables

WITH col_stats AS (
  SELECT
    COUNT(*) AS total_columns,
    COUNTIF(ARRAY_LENGTH(cfp.policy_tags) > 0) AS tagged_columns
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS` cfp
  JOIN `your-project.your_dataset.INFORMATION_SCHEMA.TABLES` t
    ON cfp.table_catalog = t.table_catalog
    AND cfp.table_schema = t.table_schema
    AND cfp.table_name = t.table_name
  WHERE t.table_type = 'BASE TABLE'
)
SELECT
  tagged_columns,
  total_columns,
  SAFE_DIVIDE(tagged_columns, total_columns) AS value
FROM col_stats

WITH base_tables AS (
  SELECT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE'
),
temporal_tables AS (
  SELECT DISTINCT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE data_type IN ('DATE', 'DATETIME', 'TIME', 'TIMESTAMP')
)
SELECT
  (SELECT COUNT(*) FROM temporal_tables tt JOIN base_tables bt ON tt.table_name = bt.table_name) AS temporal_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  SAFE_DIVIDE(
    (SELECT COUNT(*) FROM temporal_tables tt JOIN base_tables bt ON tt.table_name = bt.table_name),
    (SELECT COUNT(*) FROM base_tables)
  ) AS value

WITH base_tables AS (
  SELECT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE'
),
fk_tables AS (
  SELECT DISTINCT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLE_CONSTRAINTS`
  WHERE constraint_type = 'FOREIGN KEY'
)
SELECT
  (SELECT COUNT(*) FROM fk_tables ft JOIN base_tables bt ON ft.table_name = bt.table_name) AS tables_with_fk,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  SAFE_DIVIDE(
    (SELECT COUNT(*) FROM fk_tables ft JOIN base_tables bt ON ft.table_name = bt.table_name),
    (SELECT COUNT(*) FROM base_tables)
  ) AS value

WITH base_tables AS (
  SELECT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE'
),
constrained AS (
  SELECT DISTINCT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLE_CONSTRAINTS`
  WHERE constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')
)
SELECT
  (SELECT COUNT(*) FROM constrained c JOIN base_tables bt ON c.table_name = bt.table_name) AS constrained_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  SAFE_DIVIDE(
    (SELECT COUNT(*) FROM constrained c JOIN base_tables bt ON c.table_name = bt.table_name),
    (SELECT COUNT(*) FROM base_tables)
  ) AS value

WITH base_tables AS (
  SELECT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE'
),
embedding_tables AS (
  SELECT DISTINCT table_name
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE data_type = 'ARRAY<FLOAT64>'
)
SELECT
  (SELECT COUNT(*) FROM embedding_tables et JOIN base_tables bt ON et.table_name = bt.table_name) AS embedding_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  SAFE_DIVIDE(
    (SELECT COUNT(*) FROM embedding_tables et JOIN base_tables bt ON et.table_name = bt.table_name),
    (SELECT COUNT(*) FROM base_tables)
  ) AS value

WITH pii AS (
  SELECT
    cfp.table_name,
    cfp.column_name,
    REGEXP_EXTRACT(LOWER(cfp.column_name), r'(email|phone|ssn|social_security|credit_card|first_name|last_name|address|dob|date_of_birth|passport|salary|password|ip_address)') AS category,
    (ARRAY_LENGTH(cfp.policy_tags) > 0) AS masked
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS` cfp
  JOIN `your-project.your_dataset.INFORMATION_SCHEMA.TABLES` t
    ON cfp.table_catalog = t.table_catalog
    AND cfp.table_schema = t.table_schema
    AND cfp.table_name = t.table_name
  WHERE t.table_type = 'BASE TABLE'
    AND REGEXP_CONTAINS(LOWER(cfp.column_name), r'(email|phone|ssn|social_security|credit_card|first_name|last_name|address|dob|date_of_birth|passport|salary|password|ip_address)')
)
SELECT
  COUNTIF(masked) AS masked_pii_columns,
  COUNT(*) AS total_pii_columns,
  CASE WHEN COUNT(*) = 0 THEN 1.0 ELSE SAFE_DIVIDE(COUNTIF(masked), COUNT(*)) END AS value,
  ARRAY_AGG(
    STRUCT(table_name AS `table`, column_name AS `column`, category, masked)
    ORDER BY masked, table_name, column_name LIMIT 50
  ) AS pii_columns
FROM pii

WITH base AS (SELECT table_name FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLES` WHERE table_type = 'BASE TABLE'),
idx AS (SELECT DISTINCT table_name FROM `your-project.your_dataset.INFORMATION_SCHEMA.VECTOR_INDEXES`)
SELECT
  (SELECT COUNT(*) FROM idx i JOIN base b ON i.table_name = b.table_name) AS indexed_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  SAFE_DIVIDE((SELECT COUNT(*) FROM idx i JOIN base b ON i.table_name = b.table_name), (SELECT COUNT(*) FROM base)) AS value

WITH base AS (SELECT table_name FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLES` WHERE table_type = 'BASE TABLE'),
idx AS (SELECT DISTINCT table_name FROM `your-project.your_dataset.INFORMATION_SCHEMA.SEARCH_INDEXES`)
SELECT
  (SELECT COUNT(*) FROM idx i JOIN base b ON i.table_name = b.table_name) AS indexed_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  SAFE_DIVIDE((SELECT COUNT(*) FROM idx i JOIN base b ON i.table_name = b.table_name), (SELECT COUNT(*) FROM base)) AS value

WITH base AS (SELECT table_name FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLES` WHERE table_type = 'BASE TABLE'),
ret AS (
  SELECT DISTINCT table_name FROM `your-project.your_dataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
  WHERE option_name IN ('expiration_timestamp', 'partition_expiration_days')
)
SELECT
  (SELECT COUNT(*) FROM ret r JOIN base b ON r.table_name = b.table_name) AS retention_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  SAFE_DIVIDE((SELECT COUNT(*) FROM ret r JOIN base b ON r.table_name = b.table_name), (SELECT COUNT(*) FROM base)) AS value

SELECT CURRENT_VERSION() AS version, CURRENT_ACCOUNT() AS account

SELECT DATABASE_NAME FROM INFORMATION_SCHEMA.DATABASES WHERE IS_TRANSIENT = 'NO' ORDER BY DATABASE_NAME

SELECT SCHEMA_NAME FROM "YOUR_DB".INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME <> 'INFORMATION_SCHEMA' ORDER BY SCHEMA_NAME

SELECT
  TABLE_CATALOG AS database_name,
  TABLE_SCHEMA AS schema_name,
  TABLE_NAME AS table_name,
  TABLE_TYPE AS table_type,
  COMMENT AS description,
  ROW_COUNT AS row_count,
  CREATED AS created_at,
  LAST_ALTERED AS last_modified_at
FROM "YOUR_DB".INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA')
ORDER BY TABLE_SCHEMA, TABLE_NAME

SELECT
  TABLE_CATALOG AS database_name,
  TABLE_SCHEMA AS schema_name,
  TABLE_NAME AS table_name,
  COLUMN_NAME AS column_name,
  DATA_TYPE AS data_type,
  IS_NULLABLE AS is_nullable,
  COMMENT AS description
FROM "YOUR_DB".INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA')
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

SELECT
  TAG_DATABASE,
  TAG_SCHEMA,
  TAG_NAME,
  TAG_VALUE,
  OBJECT_DATABASE,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COLUMN_NAME,
  DOMAIN AS object_type
FROM SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES
WHERE TAG_DELETED IS NULL

SELECT
  POLICY_CATALOG AS database_name,
  POLICY_SCHEMA AS schema_name,
  POLICY_NAME AS policy_name,
  POLICY_KIND AS policy_kind
FROM SNOWFLAKE.ACCOUNT_USAGE.MASKING_POLICIES
WHERE DELETED IS NULL

SELECT
  POLICY_CATALOG AS database_name,
  POLICY_SCHEMA AS schema_name,
  POLICY_NAME AS policy_name
FROM SNOWFLAKE.ACCOUNT_USAGE.ROW_ACCESS_POLICIES
WHERE DELETED IS NULL

SELECT
  POLICY_DB AS policy_database,
  POLICY_SCHEMA AS policy_schema,
  POLICY_NAME AS policy_name,
  POLICY_KIND AS policy_kind,
  REF_DATABASE_NAME AS ref_database,
  REF_SCHEMA_NAME AS ref_schema,
  REF_ENTITY_NAME AS ref_table,
  REF_COLUMN_NAME AS ref_column,
  REF_ENTITY_DOMAIN AS ref_type
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES

SELECT
  REFERENCING_DATABASE AS source_database,
  REFERENCING_SCHEMA AS source_schema,
  REFERENCING_OBJECT_NAME AS source_name,
  REFERENCING_OBJECT_DOMAIN AS source_type,
  REFERENCED_DATABASE AS target_database,
  REFERENCED_SCHEMA AS target_schema,
  REFERENCED_OBJECT_NAME AS target_name,
  REFERENCED_OBJECT_DOMAIN AS target_type
FROM SNOWFLAKE.ACCOUNT_USAGE.OBJECT_DEPENDENCIES

SELECT
  TABLE_CATALOG AS database_name,
  TABLE_SCHEMA AS schema_name,
  TABLE_NAME AS table_name,
  TABLE_OWNER AS owner
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE DELETED IS NULL

SELECT
  TABLE_CATALOG AS database_name,
  TABLE_SCHEMA AS schema_name,
  TABLE_NAME AS table_name,
  COLUMN_NAME AS column_name,
  CLASSIFICATION_TAG_NAME AS classification_tag,
  PRIVACY_CATEGORY AS privacy_category,
  SEMANTIC_CATEGORY AS semantic_category,
  PROBABILITY AS confidence
FROM SNOWFLAKE.ACCOUNT_USAGE.DATA_CLASSIFICATION_LATEST

WITH cols AS (
  SELECT c.comment
  FROM `your_catalog`.information_schema.columns c
  JOIN `your_catalog`.information_schema.tables t
    ON c.table_schema = t.table_schema AND c.table_name = t.table_name
  WHERE c.table_schema = 'your_schema' AND t.table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
)
SELECT COUNT_IF(comment IS NOT NULL AND comment <> '') AS documented_columns,
       COUNT(*) AS total_columns,
       CAST(COUNT_IF(comment IS NOT NULL AND comment <> '') AS DOUBLE) / NULLIF(COUNT(*), 0) AS value
FROM cols

WITH cols AS (
  SELECT c.data_type
  FROM `your_catalog`.information_schema.columns c
  JOIN `your_catalog`.information_schema.tables t
    ON c.table_schema = t.table_schema AND c.table_name = t.table_name
  WHERE c.table_schema = 'your_schema' AND t.table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
)
SELECT COUNT_IF(UPPER(data_type) <> 'VARIANT') AS typed_columns,
       COUNT(*) AS total_columns,
       CAST(COUNT_IF(UPPER(data_type) <> 'VARIANT') AS DOUBLE) / NULLIF(COUNT(*), 0) AS value
FROM cols

WITH base_tables AS (
  SELECT table_name FROM `your_catalog`.information_schema.tables
  WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
),
pk_tables AS (
  SELECT DISTINCT table_name FROM `your_catalog`.information_schema.table_constraints
  WHERE table_schema = 'your_schema' AND constraint_type = 'PRIMARY KEY'
),
id_tables AS (
  SELECT DISTINCT table_name FROM `your_catalog`.information_schema.columns
  WHERE table_schema = 'your_schema'
    AND (LOWER(column_name) LIKE '%\_id' OR LOWER(column_name) LIKE '%\_key' OR LOWER(column_name) = 'id')
    AND is_nullable = 'NO'
),
with_ids AS (SELECT table_name FROM pk_tables UNION SELECT table_name FROM id_tables)
SELECT
  (SELECT COUNT(*) FROM with_ids w JOIN base_tables b ON w.table_name = b.table_name) AS tables_with_ids,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  CAST((SELECT COUNT(*) FROM with_ids w JOIN base_tables b ON w.table_name = b.table_name) AS DOUBLE)
    / NULLIF((SELECT COUNT(*) FROM base_tables), 0) AS value

WITH base_tables AS (
  SELECT table_name FROM `your_catalog`.information_schema.tables
  WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
),
fk_tables AS (
  SELECT DISTINCT table_name FROM `your_catalog`.information_schema.table_constraints
  WHERE table_schema = 'your_schema' AND constraint_type = 'FOREIGN KEY'
)
SELECT
  (SELECT COUNT(*) FROM fk_tables f JOIN base_tables b ON f.table_name = b.table_name) AS tables_with_fk,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  CAST((SELECT COUNT(*) FROM fk_tables f JOIN base_tables b ON f.table_name = b.table_name) AS DOUBLE)
    / NULLIF((SELECT COUNT(*) FROM base_tables), 0) AS value

WITH base_tables AS (
  SELECT table_name FROM `your_catalog`.information_schema.tables
  WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
),
constrained AS (
  SELECT DISTINCT table_name FROM `your_catalog`.information_schema.table_constraints
  WHERE table_schema = 'your_schema' AND constraint_type IN ('PRIMARY KEY','FOREIGN KEY','UNIQUE')
)
SELECT
  (SELECT COUNT(*) FROM constrained c JOIN base_tables b ON c.table_name = b.table_name) AS constrained_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  CAST((SELECT COUNT(*) FROM constrained c JOIN base_tables b ON c.table_name = b.table_name) AS DOUBLE)
    / NULLIF((SELECT COUNT(*) FROM base_tables), 0) AS value

WITH base_tables AS (
  SELECT table_name FROM `your_catalog`.information_schema.tables
  WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
),
temporal AS (
  SELECT DISTINCT table_name FROM `your_catalog`.information_schema.columns
  WHERE table_schema = 'your_schema'
    AND UPPER(data_type) IN ('DATE','TIMESTAMP','TIMESTAMP_NTZ','TIMESTAMP_LTZ')
)
SELECT
  (SELECT COUNT(*) FROM temporal t JOIN base_tables b ON t.table_name = b.table_name) AS temporal_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  CAST((SELECT COUNT(*) FROM temporal t JOIN base_tables b ON t.table_name = b.table_name) AS DOUBLE)
    / NULLIF((SELECT COUNT(*) FROM base_tables), 0) AS value

SELECT
  COUNT_IF(last_altered >= CURRENT_TIMESTAMP() - INTERVAL 30 DAYS) AS active_tables,
  COUNT(*) AS total_tables,
  CAST(COUNT_IF(last_altered >= CURRENT_TIMESTAMP() - INTERVAL 30 DAYS) AS DOUBLE) / NULLIF(COUNT(*), 0) AS value
FROM `your_catalog`.information_schema.tables
WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')

SELECT
  COUNT_IF(last_altered >= CURRENT_TIMESTAMP() - INTERVAL 7 DAYS) AS fresh_tables,
  COUNT(*) AS total_tables,
  CAST(COUNT_IF(last_altered >= CURRENT_TIMESTAMP() - INTERVAL 7 DAYS) AS DOUBLE) / NULLIF(COUNT(*), 0) AS value
FROM `your_catalog`.information_schema.tables
WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')

WITH base_tables AS (
  SELECT table_name FROM `your_catalog`.information_schema.tables
  WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
),
tagged AS (
  SELECT table_name FROM `your_catalog`.information_schema.table_tags WHERE schema_name = 'your_schema'
  UNION
  SELECT table_name FROM `your_catalog`.information_schema.column_tags WHERE schema_name = 'your_schema'
)
SELECT
  (SELECT COUNT(*) FROM (SELECT DISTINCT table_name FROM tagged) tg JOIN base_tables b ON tg.table_name = b.table_name) AS tagged_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  CAST((SELECT COUNT(*) FROM (SELECT DISTINCT table_name FROM tagged) tg JOIN base_tables b ON tg.table_name = b.table_name) AS DOUBLE)
    / NULLIF((SELECT COUNT(*) FROM base_tables), 0) AS value

SELECT
  COUNT_IF(table_owner IS NOT NULL AND table_owner <> '') AS owned_tables,
  COUNT(*) AS total_tables,
  CAST(COUNT_IF(table_owner IS NOT NULL AND table_owner <> '') AS DOUBLE) / NULLIF(COUNT(*), 0) AS value
FROM `your_catalog`.information_schema.tables
WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')

WITH pii AS (
  SELECT c.table_name, c.column_name,
    regexp_extract(LOWER(c.column_name), '(email|phone|ssn|social_security|credit_card|first_name|last_name|address|dob|date_of_birth|passport|salary|password|ip_address)', 1) AS category
  FROM `your_catalog`.information_schema.columns c
  JOIN `your_catalog`.information_schema.tables t
    ON c.table_schema = t.table_schema AND c.table_name = t.table_name
  WHERE c.table_schema = 'your_schema' AND t.table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
    AND LOWER(c.column_name) RLIKE '(email|phone|ssn|social_security|credit_card|first_name|last_name|address|dob|date_of_birth|passport|salary|password|ip_address)'
),
masked AS (
  SELECT DISTINCT table_name, column_name
  FROM `your_catalog`.information_schema.column_masks
  WHERE table_schema = 'your_schema'
),
joined AS (
  SELECT p.table_name, p.column_name, p.category,
    (m.column_name IS NOT NULL) AS masked
  FROM pii p
  LEFT JOIN masked m ON p.table_name = m.table_name AND p.column_name = m.column_name
)
SELECT
  COUNT_IF(masked) AS masked_pii_columns,
  COUNT(*) AS total_pii_columns,
  CASE WHEN COUNT(*) = 0 THEN 1.0 ELSE CAST(COUNT_IF(masked) AS DOUBLE) / NULLIF(COUNT(*), 0) END AS value,
  to_json(collect_list(named_struct('table', table_name, 'column', column_name, 'category', category, 'masked', masked))) AS pii_columns
FROM joined

WITH base_tables AS (
  SELECT table_name FROM `your_catalog`.information_schema.tables
  WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
),
filtered AS (
  SELECT DISTINCT table_name FROM `your_catalog`.information_schema.row_filters WHERE table_schema = 'your_schema'
)
SELECT
  (SELECT COUNT(*) FROM filtered f JOIN base_tables b ON f.table_name = b.table_name) AS protected_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  CAST((SELECT COUNT(*) FROM filtered f JOIN base_tables b ON f.table_name = b.table_name) AS DOUBLE)
    / NULLIF((SELECT COUNT(*) FROM base_tables), 0) AS value

WITH base_tables AS (
  SELECT table_name FROM `your_catalog`.information_schema.tables
  WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
),
array_tables AS (
  SELECT DISTINCT table_name FROM `your_catalog`.information_schema.columns
  WHERE table_schema = 'your_schema' AND UPPER(data_type) = 'ARRAY'
)
SELECT
  (SELECT COUNT(*) FROM array_tables a JOIN base_tables b ON a.table_name = b.table_name) AS embedding_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  CAST((SELECT COUNT(*) FROM array_tables a JOIN base_tables b ON a.table_name = b.table_name) AS DOUBLE)
    / NULLIF((SELECT COUNT(*) FROM base_tables), 0) AS value

WITH base_tables AS (
  SELECT table_name FROM `your_catalog`.information_schema.tables
  WHERE table_schema = 'your_schema' AND table_type NOT IN ('VIEW','MATERIALIZED_VIEW')
),
lineaged AS (
  SELECT DISTINCT target_table_name AS table_name
  FROM system.access.table_lineage
  WHERE target_table_catalog = 'your_catalog' AND target_table_schema = 'your_schema'
    AND event_time >= CURRENT_TIMESTAMP() - INTERVAL 90 DAYS
)
SELECT
  (SELECT COUNT(*) FROM lineaged l JOIN base_tables b ON l.table_name = b.table_name) AS lineaged_tables,
  (SELECT COUNT(*) FROM base_tables) AS total_tables,
  CAST((SELECT COUNT(*) FROM lineaged l JOIN base_tables b ON l.table_name = b.table_name) AS DOUBLE)
    / NULLIF((SELECT COUNT(*) FROM base_tables), 0) AS value

WITH cols AS (
  SELECT cl.oid AS cloid, c.ordinal_position
  FROM information_schema.columns c
  JOIN pg_namespace n ON n.nspname = c.table_schema
  JOIN pg_class cl ON cl.relname = c.table_name AND cl.relnamespace = n.oid AND cl.relkind IN ('r','p')
  WHERE c.table_schema = $1
)
SELECT
  COUNT(*) AS total_columns,
  COUNT(*) FILTER (WHERE d.description IS NOT NULL AND d.description <> '') AS documented_columns,
  CASE WHEN COUNT(*) = 0 THEN 0
       ELSE (COUNT(*) FILTER (WHERE d.description IS NOT NULL AND d.description <> ''))::float / COUNT(*) END AS value
FROM cols
LEFT JOIN pg_description d ON d.objoid = cols.cloid AND d.objsubid = cols.ordinal_position

WITH base AS (SELECT table_name FROM information_schema.tables WHERE table_schema = $1 AND table_type = 'BASE TABLE'),
pk AS (SELECT DISTINCT table_name FROM information_schema.table_constraints WHERE table_schema = $1 AND constraint_type = 'PRIMARY KEY'),
idcols AS (SELECT DISTINCT table_name FROM information_schema.columns
           WHERE table_schema = $1 AND is_nullable = 'NO' AND (lower(column_name) ~ '(_id|_key)$' OR lower(column_name) = 'id')),
ids AS (SELECT table_name FROM pk UNION SELECT table_name FROM idcols)
SELECT
  (SELECT COUNT(*) FROM ids JOIN base USING (table_name)) AS tables_with_ids,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM ids JOIN base USING (table_name))::float / (SELECT COUNT(*) FROM base) END AS value

WITH cols AS (
  SELECT c.data_type
  FROM information_schema.columns c
  JOIN information_schema.tables t ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND t.table_type = 'BASE TABLE'
  WHERE c.table_schema = $1
)
SELECT
  COUNT(*) AS total_columns,
  COUNT(*) FILTER (WHERE data_type NOT IN ('json','jsonb','ARRAY','USER-DEFINED')) AS typed_columns,
  CASE WHEN COUNT(*) = 0 THEN 0
       ELSE (COUNT(*) FILTER (WHERE data_type NOT IN ('json','jsonb','ARRAY','USER-DEFINED')))::float / COUNT(*) END AS value
FROM cols

WITH base AS (
  SELECT cl.oid, cl.relkind
  FROM pg_class cl JOIN pg_namespace n ON n.oid = cl.relnamespace
  WHERE n.nspname = $1 AND cl.relkind IN ('r','p')
),
opt AS (
  SELECT oid FROM base b
  WHERE b.relkind = 'p'
     OR EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = b.oid AND i.indisprimary = false)
)
SELECT
  (SELECT COUNT(*) FROM opt) AS optimized_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM opt)::float / (SELECT COUNT(*) FROM base) END AS value

WITH pii AS (
  SELECT c.table_name, c.column_name,
    (regexp_match(lower(c.column_name), '(email|phone|ssn|social_security|credit_card|first_name|last_name|address|dob|date_of_birth|passport|salary|password|ip_address)'))[1] AS category,
    false AS masked
  FROM information_schema.columns c
  JOIN information_schema.tables t ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND t.table_type = 'BASE TABLE'
  WHERE c.table_schema = $1
    AND lower(c.column_name) ~ '(email|phone|ssn|social_security|credit_card|first_name|last_name|address|dob|date_of_birth|passport|salary|password|ip_address)'
)
SELECT
  COUNT(*) FILTER (WHERE masked) AS masked_pii_columns,
  COUNT(*) AS total_pii_columns,
  CASE WHEN COUNT(*) = 0 THEN 1.0 ELSE (COUNT(*) FILTER (WHERE masked))::float / COUNT(*) END AS value,
  (SELECT COALESCE(json_agg(row_to_json(t)), '[]'::json) FROM (
     SELECT table_name AS "table", column_name AS "column", category, masked
     FROM pii ORDER BY table_name, column_name LIMIT 50
  ) t) AS pii_columns
FROM pii

WITH base AS (SELECT table_name FROM information_schema.tables WHERE table_schema = $1 AND table_type = 'BASE TABLE'),
temporal AS (SELECT DISTINCT table_name FROM information_schema.columns
             WHERE table_schema = $1 AND data_type IN ('date','timestamp without time zone','timestamp with time zone','time without time zone','time with time zone'))
SELECT
  (SELECT COUNT(*) FROM temporal JOIN base USING (table_name)) AS temporal_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM temporal JOIN base USING (table_name))::float / (SELECT COUNT(*) FROM base) END AS value

WITH base AS (SELECT table_name FROM information_schema.tables WHERE table_schema = $1 AND table_type = 'BASE TABLE'),
fk AS (SELECT DISTINCT table_name FROM information_schema.table_constraints WHERE table_schema = $1 AND constraint_type = 'FOREIGN KEY')
SELECT
  (SELECT COUNT(*) FROM fk JOIN base USING (table_name)) AS tables_with_fk,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM fk JOIN base USING (table_name))::float / (SELECT COUNT(*) FROM base) END AS value

WITH base AS (SELECT table_name FROM information_schema.tables WHERE table_schema = $1 AND table_type = 'BASE TABLE'),
con AS (SELECT DISTINCT table_name FROM information_schema.table_constraints WHERE table_schema = $1 AND constraint_type IN ('PRIMARY KEY','FOREIGN KEY'))
SELECT
  (SELECT COUNT(*) FROM con JOIN base USING (table_name)) AS constrained_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM con JOIN base USING (table_name))::float / (SELECT COUNT(*) FROM base) END AS value

WITH base AS (SELECT table_name FROM information_schema.tables WHERE table_schema = $1 AND table_type = 'BASE TABLE'),
emb AS (SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema = $1 AND udt_name = 'vector')
SELECT
  (SELECT COUNT(*) FROM emb JOIN base USING (table_name)) AS embedding_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM emb JOIN base USING (table_name))::float / (SELECT COUNT(*) FROM base) END AS value

WITH base AS (SELECT COUNT(*) AS c FROM pg_tables WHERE schemaname = $1),
rls AS (SELECT COUNT(DISTINCT tablename) AS c FROM pg_policies WHERE schemaname = $1)
SELECT
  rls.c AS tables_with_rls,
  base.c AS total_tables,
  CASE WHEN base.c = 0 THEN 0 ELSE rls.c::float / base.c END AS value
FROM base, rls

SELECT
  COUNT(*) AS total_columns,
  SUM(CASE WHEN d.description IS NOT NULL AND d.description <> '' THEN 1 ELSE 0 END) AS documented_columns,
  CASE WHEN COUNT(*) = 0 THEN 0
       ELSE SUM(CASE WHEN d.description IS NOT NULL AND d.description <> '' THEN 1 ELSE 0 END)::float / COUNT(*) END AS value
FROM pg_attribute a
JOIN pg_class cl ON cl.oid = a.attrelid AND cl.relkind = 'r'
JOIN pg_namespace n ON n.oid = cl.relnamespace
LEFT JOIN pg_description d ON d.objoid = cl.oid AND d.objsubid = a.attnum
WHERE n.nspname = $1 AND a.attnum > 0 AND a.attisdropped = false

WITH base AS (
  SELECT cl.oid FROM pg_class cl JOIN pg_namespace n ON n.oid = cl.relnamespace
  WHERE n.nspname = $1 AND cl.relkind = 'r'
),
pk AS (SELECT DISTINCT conrelid AS oid FROM pg_constraint WHERE contype = 'p'),
idcols AS (
  SELECT DISTINCT attrelid AS oid FROM pg_attribute
  WHERE attnum > 0 AND attnotnull = true
    AND (lower(attname) ~ '(_id|_key)$' OR lower(attname) = 'id')
),
ids AS (SELECT oid FROM pk UNION SELECT oid FROM idcols)
SELECT
  (SELECT COUNT(*) FROM base JOIN ids USING (oid)) AS tables_with_ids,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM base JOIN ids USING (oid))::float / (SELECT COUNT(*) FROM base) END AS value

SELECT
  COUNT(*) AS total_columns,
  SUM(CASE WHEN lower(c.data_type) NOT IN ('super') THEN 1 ELSE 0 END) AS typed_columns,
  CASE WHEN COUNT(*) = 0 THEN 0
       ELSE SUM(CASE WHEN lower(c.data_type) NOT IN ('super') THEN 1 ELSE 0 END)::float / COUNT(*) END AS value
FROM information_schema.columns c
JOIN information_schema.tables t
  ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND t.table_type = 'BASE TABLE'
WHERE c.table_schema = $1

SELECT
  SUM(CASE WHEN (sortkey1 IS NOT NULL AND sortkey1 <> 'AUTO(SORTKEY)') OR diststyle ILIKE '%key%' OR diststyle = 'ALL' THEN 1 ELSE 0 END) AS optimized_tables,
  COUNT(*) AS total_tables,
  CASE WHEN COUNT(*) = 0 THEN 0
       ELSE SUM(CASE WHEN (sortkey1 IS NOT NULL AND sortkey1 <> 'AUTO(SORTKEY)') OR diststyle ILIKE '%key%' OR diststyle = 'ALL' THEN 1 ELSE 0 END)::float / COUNT(*) END AS value
FROM svv_table_info
WHERE "schema" = $1

WITH pii AS (
  SELECT cl.relname AS table_name, a.attname AS column_name
  FROM pg_attribute a
  JOIN pg_class cl ON cl.oid = a.attrelid AND cl.relkind = 'r'
  JOIN pg_namespace n ON n.oid = cl.relnamespace
  WHERE n.nspname = $1 AND a.attnum > 0 AND a.attisdropped = false
    AND lower(a.attname) ~ '(email|phone|ssn|social_security|credit_card|first_name|last_name|address|dob|date_of_birth|passport|salary|password|ip_address)'
),
masked AS (
  SELECT DISTINCT p.table_name, p.column_name
  FROM pii p
  JOIN svv_attached_masking_policy m
    ON m.schema_name = $1 AND m.table_name = p.table_name
   AND lower(m.input_columns::text) ~ ('(^|[^a-z0-9_])' || lower(p.column_name) || '($|[^a-z0-9_])')
)
SELECT
  (SELECT COUNT(*) FROM masked) AS masked_pii_columns,
  (SELECT COUNT(*) FROM pii) AS total_pii_columns,
  CASE WHEN (SELECT COUNT(*) FROM pii) = 0 THEN 1.0
       ELSE (SELECT COUNT(*) FROM masked)::float / (SELECT COUNT(*) FROM pii) END AS value

WITH base AS (SELECT COUNT(*) AS c FROM pg_tables WHERE schemaname = $1),
rls AS (
  -- is_rls_on is a "char" ('t'/'f') on live Redshift, not a boolean — cast
  -- to text and accept both spellings (validated live 2026-06-10).
  SELECT COUNT(DISTINCT relname) AS c FROM svv_rls_relation
  WHERE datname = current_database() AND relschema = $1 AND is_rls_on::text IN ('t','true')
)
SELECT
  rls.c AS tables_with_rls,
  base.c AS total_tables,
  CASE WHEN base.c = 0 THEN 0 ELSE rls.c::float / base.c END AS value
FROM base, rls

WITH base AS (SELECT table_name FROM information_schema.tables WHERE table_schema = $1 AND table_type = 'BASE TABLE'),
temporal AS (
  SELECT DISTINCT table_name FROM information_schema.columns
  WHERE table_schema = $1 AND lower(data_type) IN
    ('date','timestamp without time zone','timestamp with time zone','time without time zone','time with time zone')
)
SELECT
  (SELECT COUNT(*) FROM temporal JOIN base USING (table_name)) AS temporal_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM temporal JOIN base USING (table_name))::float / (SELECT COUNT(*) FROM base) END AS value

WITH base AS (
  SELECT cl.oid FROM pg_class cl JOIN pg_namespace n ON n.oid = cl.relnamespace
  WHERE n.nspname = $1 AND cl.relkind = 'r'
),
fk AS (SELECT DISTINCT conrelid AS oid FROM pg_constraint WHERE contype = 'f')
SELECT
  (SELECT COUNT(*) FROM base JOIN fk USING (oid)) AS tables_with_fk,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM base JOIN fk USING (oid))::float / (SELECT COUNT(*) FROM base) END AS value

WITH base AS (
  SELECT cl.oid FROM pg_class cl JOIN pg_namespace n ON n.oid = cl.relnamespace
  WHERE n.nspname = $1 AND cl.relkind = 'r'
),
con AS (SELECT DISTINCT conrelid AS oid FROM pg_constraint WHERE contype IN ('p','f'))
SELECT
  (SELECT COUNT(*) FROM base JOIN con USING (oid)) AS constrained_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE (SELECT COUNT(*) FROM base JOIN con USING (oid))::float / (SELECT COUNT(*) FROM base) END AS value

WITH pii AS (
  SELECT cl.relname AS table_name, a.attname AS column_name,
    REGEXP_SUBSTR(lower(a.attname), 'email|phone|ssn|social_security|credit_card|first_name|last_name|address|dob|date_of_birth|passport|salary|password|ip_address') AS category
  FROM pg_attribute a
  JOIN pg_class cl ON cl.oid = a.attrelid AND cl.relkind = 'r'
  JOIN pg_namespace n ON n.oid = cl.relnamespace
  WHERE n.nspname = $1 AND a.attnum > 0 AND a.attisdropped = false
    AND lower(a.attname) ~ '(email|phone|ssn|social_security|credit_card|first_name|last_name|address|dob|date_of_birth|passport|salary|password|ip_address)'
),
masked AS (
  SELECT DISTINCT p.table_name, p.column_name
  FROM pii p
  JOIN svv_attached_masking_policy m
    ON m.schema_name = $1 AND m.table_name = p.table_name
   AND lower(m.input_columns::text) ~ ('(^|[^a-z0-9_])' || lower(p.column_name) || '($|[^a-z0-9_])')
)
SELECT p.table_name, p.column_name, p.category,
  CASE WHEN m.column_name IS NOT NULL THEN true ELSE false END AS masked
FROM pii p
LEFT JOIN masked m ON m.table_name = p.table_name AND m.column_name = p.column_name
ORDER BY p.table_name, p.column_name
LIMIT 50

WITH base AS (
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = @schema AND TABLE_TYPE = 'BASE TABLE'
),
pk AS (
  SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = @schema AND CONSTRAINT_TYPE = 'PRIMARY KEY'
),
idcols AS (
  SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema AND IS_NULLABLE = 'NO'
    AND (lower(COLUMN_NAME) LIKE '%[_]id' OR lower(COLUMN_NAME) LIKE '%[_]key' OR lower(COLUMN_NAME) = 'id')
),
ids AS (SELECT TABLE_NAME FROM pk UNION SELECT TABLE_NAME FROM idcols)
SELECT
  (SELECT COUNT(*) FROM ids WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM base)) AS tables_with_ids,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE CAST((SELECT COUNT(*) FROM ids WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM base)) AS float)
            / (SELECT COUNT(*) FROM base) END AS value

SELECT
  COUNT(*) AS total_columns,
  SUM(CASE WHEN lower(c.DATA_TYPE) NOT IN ('sql_variant','varbinary') THEN 1 ELSE 0 END) AS typed_columns,
  CASE WHEN COUNT(*) = 0 THEN 0
       ELSE CAST(SUM(CASE WHEN lower(c.DATA_TYPE) NOT IN ('sql_variant','varbinary') THEN 1 ELSE 0 END) AS float) / COUNT(*) END AS value
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_TYPE = 'BASE TABLE'
WHERE c.TABLE_SCHEMA = @schema

WITH pii AS (
  SELECT c.object_id, c.column_id,
    CASE WHEN mc.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_masked
  FROM sys.columns c
  JOIN sys.tables tb ON tb.object_id = c.object_id
  JOIN sys.schemas s ON s.schema_id = tb.schema_id
  LEFT JOIN sys.masked_columns mc
    ON mc.object_id = c.object_id AND mc.column_id = c.column_id AND mc.is_masked = 1
  WHERE s.name = @schema AND (lower(c.name) LIKE '%email%' OR lower(c.name) LIKE '%phone%' OR lower(c.name) LIKE '%ssn%' OR lower(c.name) LIKE '%social_security%' OR lower(c.name) LIKE '%credit_card%' OR lower(c.name) LIKE '%first_name%' OR lower(c.name) LIKE '%last_name%' OR lower(c.name) LIKE '%address%' OR lower(c.name) LIKE '%dob%' OR lower(c.name) LIKE '%date_of_birth%' OR lower(c.name) LIKE '%passport%' OR lower(c.name) LIKE '%salary%' OR lower(c.name) LIKE '%password%' OR lower(c.name) LIKE '%ip_address%')
)
SELECT
  SUM(is_masked) AS masked_pii_columns,
  COUNT(*) AS total_pii_columns,
  CASE WHEN COUNT(*) = 0 THEN 1.0
       ELSE CAST(SUM(is_masked) AS float) / COUNT(*) END AS value
FROM pii

WITH base AS (
  SELECT tb.object_id FROM sys.tables tb
  JOIN sys.schemas s ON s.schema_id = tb.schema_id
  WHERE s.name = @schema
),
rls AS (
  SELECT DISTINCT sp.target_object_id AS object_id
  FROM sys.security_predicates sp
  JOIN sys.security_policies pol ON pol.object_id = sp.object_id AND pol.is_enabled = 1
)
SELECT
  (SELECT COUNT(*) FROM base WHERE object_id IN (SELECT object_id FROM rls)) AS tables_with_rls,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE CAST((SELECT COUNT(*) FROM base WHERE object_id IN (SELECT object_id FROM rls)) AS float)
            / (SELECT COUNT(*) FROM base) END AS value

WITH base AS (
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = @schema AND TABLE_TYPE = 'BASE TABLE'
),
temporal AS (
  SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @schema
    AND lower(DATA_TYPE) IN ('date','datetime','datetime2','smalldatetime','time','datetimeoffset')
)
SELECT
  (SELECT COUNT(*) FROM temporal WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM base)) AS temporal_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE CAST((SELECT COUNT(*) FROM temporal WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM base)) AS float)
            / (SELECT COUNT(*) FROM base) END AS value

WITH base AS (
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = @schema AND TABLE_TYPE = 'BASE TABLE'
),
fk AS (
  SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = @schema AND CONSTRAINT_TYPE = 'FOREIGN KEY'
)
SELECT
  (SELECT COUNT(*) FROM fk WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM base)) AS tables_with_fk,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE CAST((SELECT COUNT(*) FROM fk WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM base)) AS float)
            / (SELECT COUNT(*) FROM base) END AS value

WITH base AS (
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = @schema AND TABLE_TYPE = 'BASE TABLE'
),
con AS (
  SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = @schema AND CONSTRAINT_TYPE IN ('PRIMARY KEY','FOREIGN KEY','UNIQUE')
)
SELECT
  (SELECT COUNT(*) FROM con WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM base)) AS constrained_tables,
  (SELECT COUNT(*) FROM base) AS total_tables,
  CASE WHEN (SELECT COUNT(*) FROM base) = 0 THEN 0
       ELSE CAST((SELECT COUNT(*) FROM con WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM base)) AS float)
            / (SELECT COUNT(*) FROM base) END AS value

SELECT TOP 50
  tb.name AS table_name,
  c.name AS column_name,
  CASE WHEN lower(c.name) LIKE '%ip_address%' THEN 'ip_address' WHEN lower(c.name) LIKE '%social_security%' THEN 'social_security' WHEN lower(c.name) LIKE '%date_of_birth%' THEN 'date_of_birth' WHEN lower(c.name) LIKE '%credit_card%' THEN 'credit_card' WHEN lower(c.name) LIKE '%first_name%' THEN 'first_name' WHEN lower(c.name) LIKE '%last_name%' THEN 'last_name' WHEN lower(c.name) LIKE '%email%' THEN 'email' WHEN lower(c.name) LIKE '%phone%' THEN 'phone' WHEN lower(c.name) LIKE '%ssn%' THEN 'ssn' WHEN lower(c.name) LIKE '%address%' THEN 'address' WHEN lower(c.name) LIKE '%dob%' THEN 'dob' WHEN lower(c.name) LIKE '%passport%' THEN 'passport' WHEN lower(c.name) LIKE '%salary%' THEN 'salary' WHEN lower(c.name) LIKE '%password%' THEN 'password' ELSE 'pii' END AS category,
  CASE WHEN mc.column_id IS NOT NULL THEN 1 ELSE 0 END AS masked
FROM sys.columns c
JOIN sys.tables tb ON tb.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = tb.schema_id
LEFT JOIN sys.masked_columns mc
  ON mc.object_id = c.object_id AND mc.column_id = c.column_id AND mc.is_masked = 1
WHERE s.name = @schema AND (lower(c.name) LIKE '%email%' OR lower(c.name) LIKE '%phone%' OR lower(c.name) LIKE '%ssn%' OR lower(c.name) LIKE '%social_security%' OR lower(c.name) LIKE '%credit_card%' OR lower(c.name) LIKE '%first_name%' OR lower(c.name) LIKE '%last_name%' OR lower(c.name) LIKE '%address%' OR lower(c.name) LIKE '%dob%' OR lower(c.name) LIKE '%date_of_birth%' OR lower(c.name) LIKE '%passport%' OR lower(c.name) LIKE '%salary%' OR lower(c.name) LIKE '%password%' OR lower(c.name) LIKE '%ip_address%')
ORDER BY tb.name, c.name