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.

SELECT 
 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.


SELECT 
 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;

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

What's is the difference between streaming replication Vs hot standby vs warm standby ?

Script to kill ALL IDLE Connection In postgreSQL