Топ-100
 
CerebroSQL

TABLE node for PostgreSQL

List of tables in the selected PostgreSQL database schema. The list can be filtered by entering part of the table name in the " Like name object " field

Table list in the PostgreSQL.jpg

SELECT c.oid,
       'core' as "Schema",
       c.relname as "Name",                     
       (select count(inhrelid) 
          from pg_inherits 
         where inhparent = c.oid
       ) "CountPar",
       CASE c.relkind        
         WHEN 'r' THEN 'table'  
         WHEN 't' THEN 'TOAST' 
         WHEN 'f' THEN 'foreign table' 
         WHEN 'p' THEN 'partition_table' 
       END as "Type",
       pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
       pg_catalog.obj_description(c.oid, 'pg_class') as "Description",
       u.usename
  FROM pg_catalog.pg_class c                                     
       LEFT JOIN pg_catalog.pg_user u on c.relowner = u.usesysid 
WHERE c.relkind IN ('r','t','f','p')
   AND c.relispartition = false
   and c.relnamespace =  (select oid 
                            from pg_catalog.pg_namespace n 
                           where nspname = $$SCHEMA_NAME)
ORDER BY c.relname

Menu
Узел Table - menu.jpg
  • Copy name  - copy object name to clipboard

  • Reindex - generate a command to rebuild indexes on a table 

  • Create code [Select]  - form the text of the query to the object (in the select phrase, the names of all fields are displayed)

  • Create code [Insert]  - form the insert command to insert data

  • Create code [Delete]  - form the delete command to delete all data

  • Create code [Truncate]  - generate a truncate command to clear the table

  • Create code [Drop]  - form a drop command to drop a table

  • Get source  - generate table creation code

  • Get statistics  - display table usage statistics. The data is displayed on the Statistics tab of the main window

  • View data (50 rows)  - display table data (limit 50)

  • Finding functions using a table - search for functions using the selected table. The search is performed in the main process and can take a long time

If several nodes are selected in the tree (using the Ctrl button), then the program generates SQL commands for all selected nodes

Finding functions using a table

select t.*, f.* from 
(
    SELECT  table_name, table_schema,
            table_schema||'.'||table_name full_name,
            table_type
      FROM information_schema.tables
     WHERE table_schema = $$TABLESCHEMA
       and table_name   = $$TABLENAME    
) t
JOIN 
(    
    SELECT n.nspname AS schema_name,
           p.proname AS function_name,
           pg_get_function_arguments(p.oid) AS args,
           pg_get_functiondef(p.oid) AS func_def
    FROM   pg_proc p
    JOIN   pg_namespace n ON n.oid = p.pronamespace
    WHERE  n.nspname not in ('pg_catalog','information_schema') and    n.nspname not like 'pg%'
) f
on position(t.table_name in f.func_def) >0

Child nodes
TOAST table
List of table columns
Checks created on table columns performed when inserting data
Indexes based on table columns
Table triggers
Foreign keys of a table
Table dependencies (who is referencing it)
Size of the table and its indexes
Table usage statistics