Топ-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

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
  • 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