Usually when we refer to database table structure, we are concerned about several attributes:
Field name, type, length, whether primary key, whether self-increasing, whether empty, default value, notes.
So, what are the properties of using SQL statements to get these attributes under the mainstream databases?

MySQL database

As the most popular database nowadays, MySQL is the easiest to get SQL.
In general, we can use the following line of statement to solve:

SHOW FULL COLUMNS FROM xxx;

Of course, you can also use tables in MySQL's own database information_schema, such as columns, to query for more complete information.

PostgreSQL database

Use its two most important Schema(information_schema, pg_catalog) tables below to conduct Association queries to obtain common attributes.

SELECT d.column_name AS "Field", d.udt_name AS "Type", COALESCE(d.character_maximum_length, d.numeric_precision, d.datetime_precision) AS "Length", 
  CASE WHEN t.conname IS NOT NULL THEN 'PRI' ELSE '' END AS "Key", 
  CASE WHEN s.extra IS NOT NULL THEN 'auto_increment' ELSE '' END "Extra", 
  d.is_nullable AS "Null", f.adsrc AS "Default", col_description(a.attrelid, a.attnum) AS "Comment"
 FROM information_schema.columns d, pg_class c, pg_attribute a
 LEFT JOIN pg_constraint t ON (a.attrelid = t.conrelid AND t.contype = 'p' AND a.attnum = t.conkey[1])
 LEFT JOIN pg_attrdef f ON (a.attrelid = f.adrelid AND a.attnum = f.adnum)
 LEFT JOIN (SELECT 'nextval(''' || c.relname || '''::regclass)' AS extra FROM pg_class c WHERE c.relkind = 'S') s ON f.adsrc = s.extra
WHERE a.attrelid = c.oid
AND a.attnum > 0
AND c.relname = d.table_name
AND d.column_name = a.attname
AND c.relname = 'xxx'
ORDER BY a.attnum;

Oracle database

System table: user_col_comments can provide most of the information, only: Comment, key need to be associated with other tables for acquisition.

SELECT t.column_name AS "Field", t.data_type AS "Type", t.data_length AS "Length", 
    CASE WHEN k.column_name IS NOT NULL THEN 'PRI' ELSE '' END AS "Key", 
    CASE WHEN t.identity_column = 'YES' THEN 'auto_increment' ELSE '' END AS "Extra",
    CASE WHEN t.nullable = 'N' THEN 'NO' ELSE 'YES' END AS "Null",
    t.data_default AS "Default", c.comments AS "Comment"
 FROM user_col_comments c, user_tab_cols t
LEFT JOIN (
    SELECT u.table_name, c.column_name 
     FROM user_constraints u, user_cons_columns c
    WHERE u.table_name = c.table_name
    AND u.constraint_name = c.constraint_name
    AND u.constraint_type = 'P') k ON t.table_name = k.table_name AND t.column_name = k.column_name
WHERE t.table_name = c.table_name
AND t.column_name = c.column_name 
AND t.table_name = 'xxx'
ORDER BY t.column_id;

MsSQL database

MsSQL is the SQL Server database. MsSQL is more complex and has the most associated system tables.

SELECT a.name AS 'Field', b.name AS 'Type', COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 'Length', 
  CASE WHEN p.name IS NOT NULL THEN 'PRI' ELSE '' END 'Key',
  CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN 'auto_increment' ELSE '' END AS 'Extra',
  CASE WHEN a.isnullable=1 THEN 'YES' ELSE 'NO' END AS 'Null',
  e.text AS 'Default', g.[value] AS 'Comment'
FROM sysobjects d, syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON (a.id = G.major_id AND a.colid = g.minor_id)
LEFT JOIN (
 SELECT s.name, k.id, k.colid FROM sysindexkeys k, sysindexes i, sysobjects s
 WHERE k.indid = i.indid
 AND s.name = i.name
 AND s.xtype = 'PK'
 ) p ON (p.id = a.id AND p.colid = a.colid)
WHERE d.id = a.id
AND d.xtype = 'U'
AND d.name = 'xxx'
ORDER BY a.colorder;

Note: All above refer to MySQL's attribute name output. xxx is the name of the table to be queried.

diboot Simple and Efficient Light Code Development Framework (star)