Datadictionary OpenWave 3.1.11

View: vwadm_objectcount

Schema

public

Owner

waveuser005

Descriptions

There is no description for view vwadm_objectcount

Fields

Rules

Definition

CREATE VIEW public.vwadm_objectcount (
   object,
   aantal,
   status)
AS
       (        (        (        (        (        (        (        (
           (        (        (        (        (
SELECT 'TABLECOLUMN'::text AS object,
                                                                                                                       count(pg_attribute.*) AS aantal,
                                                                                                                       'NVT'::text AS status
FROM pg_attribute
                                                                                                                 JOIN pg_class ON pg_class.oid = pg_attribute.attrelid
WHERE upper(pg_class.relname::text) ~~ 'TB%'::text AND pg_class.relkind =
   'r'::"char" AND pg_attribute.attnum > 0 AND pg_attribute.attstattarget < 0
UNION
SELECT 'VIEWCOLUMN'::text AS object,
                                                                                                                       count(pg_attribute.*) AS aantal,
                                                                                                                       'NVT'::text AS status
FROM pg_attribute
                                                                                                                 JOIN pg_class ON pg_class.oid = pg_attribute.attrelid
WHERE upper(pg_class.relname::text) ~~ 'VW%'::text AND pg_class.relkind =
   'v'::"char" AND pg_attribute.attnum > 0 AND pg_attribute.attstattarget < 0)
UNION
SELECT 'PRIMARY KEY'::text AS object,
                                                                                                               count(pg_constraint.conname) AS aantal,
                                                                                                               'NVT'::text AS status
FROM pg_constraint
WHERE pg_constraint.contype = 'p'::"char" AND
   upper(pg_constraint.conname::text) ~~ 'CPK_%'::text)
UNION
SELECT 'FOREIGN KEY'::text AS object,
                                                                                                       count(pg_constraint.conname) AS aantal,
                                                                                                       'NVT'::text AS status
FROM pg_constraint
WHERE pg_constraint.contype = 'f'::"char" AND
   upper(pg_constraint.conname::text) ~~ 'CFK_%'::text)
UNION
SELECT 'NOT NULL'::text AS object,
                                                                                               count(pg_constraint.conname) AS aantal,
                                                                                               'NVT'::text AS status
FROM pg_constraint
WHERE pg_constraint.contype = 'c'::"char" AND
   upper(pg_constraint.conname::text) ~~ 'CNN_%'::text)
UNION
SELECT 'CHECK'::text AS object,
                                                                                       count(pg_constraint.conname) AS aantal,
                                                                                       'NVT'::text AS status
FROM pg_constraint
WHERE pg_constraint.contype = 'c'::"char" AND
   upper(pg_constraint.conname::text) ~~ 'CHK_%'::text)
UNION
SELECT 'FUNCTION'::text AS object,
                                                                           count(p.proname)
                                                                               AS aantal,
                                                                           'NVT'::text
                                                                               AS status
FROM pg_namespace n
                                                                     JOIN
                                                                         pg_proc p ON p.pronamespace = n.oid
WHERE n.nspname = 'public'::name)
UNION
SELECT 'DEFAULT'::text AS object,
                                                                   count(pg_attribute.attname)
                                                                       AS aantal,
                                                                   'NVT'::text
                                                                       AS status
FROM pg_attribute
WHERE pg_attribute.atthasdef = true AND pg_attribute.attnotnull = false)
UNION
SELECT 'INDEX'::text AS object,
                                                           count(pg_class.relname)
                                                               AS aantal,
                                                           'NVT'::text AS status
FROM pg_class
WHERE pg_class.relkind = 'i'::"char" AND upper(pg_class.relname::text) ~~ 'IDX_%'::text)
UNION
SELECT 'INDEXUNIQUE'::text AS object,
                                                   count(pg_class.relname) AS aantal,
                                                   'NVT'::text AS status
FROM pg_class
WHERE pg_class.relkind = 'i'::"char" AND upper(pg_class.relname::text) ~~ 'IDU_%'::text)
UNION
SELECT 'SEQUENCE'::text AS object,
                                           count(pg_class.relname) AS aantal,
                                           'NVT'::text AS status
FROM pg_class
WHERE pg_class.relkind = 'S'::"char" AND upper(pg_class.relname::text) ~~ 'TB%'::text)
UNION
SELECT 'VIEW'::text AS object,
                                   count(pg_class.relname) AS aantal,
                                   'NVT'::text AS status
FROM pg_class
WHERE pg_class.relkind = 'v'::"char" AND upper(pg_class.relname::text) ~~
   'VW%'::text AND NOT upper(pg_class.relname::text) ~~ 'VWADM_%'::text AND NOT upper(pg_class.relname::text) ~~ 'VWBI_%'::text)
UNION
SELECT 'ADMINVIEW'::text AS object,
                           count(pg_class.relname) AS aantal,
                           'NVT'::text AS status
FROM pg_class
WHERE pg_class.relkind = 'v'::"char" AND upper(pg_class.relname::text) ~~
   'VWADM_%'::text)
UNION
SELECT 'BIVIEW'::text AS object,
                   count(pg_class.relname) AS aantal, 'NVT'::text AS status
FROM pg_class
WHERE pg_class.relkind = 'v'::"char" AND upper(pg_class.relname::text) ~~
   'VWBI_%'::text)
UNION
SELECT 'TABLE'::text AS object, count(pg_class.relname) AS aantal,
           'NVT'::text AS status
FROM pg_class
WHERE pg_class.relkind = 'r'::"char" AND upper(pg_class.relname::text) ~~ 'TB%'::text
ORDER BY 1;

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 22-2-2017 10:50