Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Script to Find Table and Column without comment or description

In this post, I am sharing a script to find the table and column, which are not associated with any comment or description of PostgreSQL.

Self-documentation is a most important thing for every developer and organization.
In our company, We are very strictly following this rule as a database development standard in which we have to write a comment or description for every table and column.

You can also manage this description using different UML tools.
But better to other tools, we should write in the database so whenever you create a database diagram, comment or description added automatically.

The Humans are doing a mistake, so sometimes I have to find such a list objects which didn’t associate with any comment or description.

I filtered below query by giving User_Name in WHERE clause, so if you want for all users, you should remove this filter.
Find Table without comment.

 pg_get_userbyid(pc.relowner)AS ObjectOwner
 ,pc.relname AS TableName
 ,pd.description AS Comment
FROM pg_class AS pc
LEFT OUTER JOIN pg_description AS pd 
 ON (pc.oid = pd.objoid)
WHERE pg_get_userbyid(pc.relowner) ='User_Name'
 AND pc.relkind = 'r'
 AND pd.description IS NULL;
Find Column without comment.

 pg_get_userbyid(pc.relowner) AS ObjectOwner
 ,pc.relname AS TableName
 ,pa.attname AS ColumnName
 ,pd.description AS Comment
FROM pg_attribute AS pa
JOIN pg_class AS pc 
 ON (pa.attrelid = pc.oid)
LEFT OUTER JOIN pg_description AS pd 
 ON (pc.oid = pd.objoid AND pa.attnum = pd.objsubid)
WHERE pg_get_userbyid(pc.relowner) ='User_Name'
 AND pc.relkind = 'r'
 AND pd.description IS NULL
 AND pa.attnum > 0
ORDER BY pa.attname;


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger