Investigating the table structure of a website to supplement an existing module.
One approach is to login to the server, where possible. A second is to run an SQL query from the website.
If you have access to the SQL server either directly of via a Remote Desktop login to the server. The table structure can be viewed directly using the SQL management studio.
Reviewing the table structure using the SQL management studio is easy and allows ready access to the contents of the tables too.
However, this requires the access and also there is the possibility of accidental greater damage.
For a website with an existing module, which was to be extended. I wished to know the table structure.
Given below is an SQL query to get the list of tables within a database.
SELECT C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.TABLES T INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME=C.TABLE_NAME WHERE T.TABLE_TYPE='BASE TABLE' ORDER BY C.TABLE_NAME
DotNetNuke includes a page, Sql Console, for superusers allowing interaction with the database.
On this page its possible to run SQL. The code is added in the large central box and the script run.
Above is shown the Sql Console from a DNN website with the SQL to get the list of tables in the database entered.
Helpful for a DNN website where a user having superuser permissions has access to the SQL page.
Click on the Run Code blue button to show the list of tables.
Above is shown the result of running the code with the list of tables.
I’ve chosen to show the code executed through a DNN website as this offers an easy interface.
Alternatively the code to get a list of the database tables can be included within a file, perhaps uploaded as a part of the skin/theme or a module. If you are taking this approach don’t forget to restrict access with the use of user roles to protect the results from public view.
The given SQL query used to get the list of tables within a database can be entered directly if the CMS facilitates. Or implemented via the theme or module being developed.
