The last few companies that I have worked for we have setup an internal wiki that everyone uses to hold information about all aspects of the business including, of course, the technical team. We use MediaWiki for this mainly because it is easy to setup and is also familiar to users through Wikipedia.
One area that we try and document is the database structure. Having this on hand and searchable in the wiki allows new employees to quickly get to grips with it and makes us all more productive.
The issue is that creating the MediaWiki tables by hand is a real pain, particularly with database tables with a large number of columns. In order to make this a less painful process I have written a short database script that given the database and table names will output information about the table in MediaWiki format.
Replace DATABASE_NAME and TABLE_NAME in the script below:
SELECT CONCAT('{| class="wikitable"', CHAR(13),'|-',CHAR(13),'| Column Name || Type
|| Default || Nullable? || Key || Comment')
UNION
SELECT CONCAT('|-',CHAR(13),' | ', COALESCE(COLUMN_NAME,''), ' || ',
COALESCE(COLUMN_TYPE,''), ' || ', COALESCE(COLUMN_DEFAULT,''), ' || ',
COALESCE(IS_NULLABLE,''), ' || ', COALESCE(COLUMN_KEY,''), ' || ',
COALESCE(COLUMN_COMMENT,''), CHAR(13))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'DATABASE_NAME'
AND TABLE_NAME = 'TABLE_NAME'
UNION
SELECT CONCAT('|-',CHAR(13),'|}')
Running the script in PHPMyAdmin will output something similar to the following that can then be cut and pasted into your wiki:
{| class="wikitable"
|-
| Column Name || Type || Default || Nullable? || Key || Comment
|-
| id || int(11) || || NO || PRI ||
|-
| company_id || int(11) || || NO || ||
|-
| username || varchar(100) || || NO || ||
|-
| user_id || varchar(100) || || NO || ||
|-
| password || varchar(100) || || NO || ||
|-
|}
The final output will look like:
If you are finding that PHPMyAdmin is truncating the output when you run the script so that you aren’t seeing all the details you may need to increase the number of characters that are displayed or temporarily turn this off by using the toggle in browse mode.