How to Identify the Database that Holds a Specific Table

From support-works
Jump to navigation Jump to search

Status: Published
Version: 1.0
Authors: HTL QA
Applies to: Supportworks ESP (Core Services)

How to Identify the Database that Holds a Specific Table

When a table has become corrupted, the table name will normally be stated in a log error entry as a file that cannot be accessed. However, the log entry does not give you the name of the database concerned, which you will need if you want to repair the table.

In order to find the database that the corrupt table belongs to, you can perform the following procedure to generate a list of databases and the table files belonging to them:

  1. If you already have a list of the table files, please go to step 5.
  2. On the Supportworks server, display a Windows command prompt by selecting Start > Run and entering cmd, and finally clicking OK.
  3. At the command prompt, change to the data folder of the Supportworks SQL Server by entering:
    cd C:\Program Files\Hornbill\Core Services\SwSqlServer\data
    (or you can replace C:\Program Files\Hornbill\ with the path to your actual installation folder if this is different to the default)
  4. Enter the following command to create a text file listing every sub-folder and file in the data folder:
    dir * /s > filelist.txt
    This text file, called filelist.txt, is created in the current folder. You will only need to create it once, unless you add more tables to a database, at which point you will need to run the command again.
  5. Using a text editor, open up the text file that lists the tables. You will see that it displays all the table files within each folder. Each folder below \SwSqlServer\data\ represents a database, and each table in a database is represented by three files: table-name.frm, table-name.MYD and table-name.MYI.
  6. Search for the required table name within the text file.
    Once you have found the three table files relating to the required table, you will be able to identify the database containing that table by the name of the folder in which those files are located.

Note: You will find that some tables exist in more than one database. For example, the swdata database and the sw_systemdb database both contain opencall and updatedb tables. If such a table is corrupt, we would strongly recommend that both copies of the table be repaired.