This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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.
|
1 2 3 4 5 6 7 8 9 10 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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; |
