I have worked previously purely within the confines of a website to analyse the structure of the database. Wishing to make changes to, or simply understand the content of the tables storing information for a CMS website module, the initial task is to understand what tables exist.
On a recent occasion I was lucky enough to have an older local copy of the website. I was able to review and develop the SQL queries by viewing the tables and their contents using the Microsoft SQL Management Studio.
Once ready, I checked the existence of the tables, which I was interested in, on the live website, using some t-SQL code.
Dependant upon your website it may be easy to execute SQL code within its framework. For example the DotNetNuke CSS allows SQL to be run from its host admin pages.
Whether you have access to the server and the SQL Management Studio this maybe a simpler and more time effective approach.
Here’s the query which I used to get a list of the tables within an MS SQL database:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
Dependant upon your results you may also want to add a restriction, limiting the number of returned tables to only those associated with a particular module.
Restricting by TABLE_CATALOG and perhaps sorting by TABLE_NAME and of course to get a limited subset using a like WHERE TABLE_NAME LIKE ‘portal%’
With a little t-SQL code I was able to get a list of the tables within an MS SQL database via the website, without using Microsoft’s SQL Management Studio
