Metadata
Metadata is data about data. The metadata for a PSQL database is called a schema. For a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables. Schemas are stored as data dictionary files (DDFs) by PSQL.
You can export the schema for one or more tables to a text file. The exported schema contains the CREATE TABLE SQL statement (and CREATE INDEX statement if applicable) to create the table and its indexes. The exported file has a default file extension .sql and is called a SQL script.
The SQL script file can be run (executed) in SQL Editor. See To open an SQL script.
The SQL script contains the text “Unable to open table” if a table cannot be opened. For example, the error occurs if an owner name is set on a table or a table has been deleted outside of PCC while PCC is currently running.
You have three options when exporting a table schema:
•
Include an IN DICTIONARY clause with a USING clause in the statement
•
Include only the USING clause in the statement
•
Omit both the IN DICTIONARY clause and the USING clause in the statement (a “plain” statement)
IN DICTIONARY Clause
The IN DICTIONARY clause instructs the database engine to modify only the DDFs, which leaves the underlying physical data unchanged. Normally, PSQL keeps DDFs and data files synchronized, but this clause allows you to force table dictionary definitions to match an existing data file.
The clause can be useful when you want to create a definition in the dictionary to match an existing data file. Another common use is if you want to duplicate an existing database. You export all the statements from one database, create a new database and then run the exported script against the new database.
If the SQL script contains IN DICTIONARY clauses, note that the data file must already exist when you run the SQL script in SQL Editor.
The IN DICTIONARY clause is always paired with a USING clause.
An exported statement looks similar to the following:
CREATE TABLE "Course" IN DICTIONARY USING 'Course.mkd' (
"Name" CHAR(7) NOT NULL CASE ,
"Description" CHAR(50) CASE ,
"Credit_Hours" USMALLINT,
"Dept_Name" CHAR(20) NOT NULL CASE
);
CREATE UNIQUE INDEX "Course_Name" IN DICTIONARY ON "Course"("Name");
CREATE INDEX "DeptName" IN DICTIONARY ON "Course"("Dept_Name");
See also IN DICTIONARY in SQL Engine Reference.
USING Clause
The USING keyword allows you to associate a table with a particular data file.
An exported statement looks similar to the following:
CREATE TABLE "Course" USING 'Course.mkd' (
"Name" CHAR(7) NOT NULL CASE ,
"Description" CHAR(50) CASE ,
"Credit_Hours" USMALLINT,
"Dept_Name" CHAR(20) NOT NULL CASE
);
CREATE UNIQUE INDEX "Course_Name" IN DICTIONARY ON "Course"("Name");
CREATE INDEX "DeptName" IN DICTIONARY ON "Course"("Dept_Name");
See USING in SQL Engine Reference.
Plain Statement
The “plain” statement omits the IN DICTIONARY clause and the USING clause. The plain CREATE TABLE syntax is useful to duplicate an existing table by simply changing the table name or to create the same table in a different database.
An exported statement looks similar to the following:
CREATE TABLE "Course"(
"Name" CHAR(7) NOT NULL CASE ,
"Description" CHAR(50) CASE ,
"Credit_Hours" USMALLINT,
"Dept_Name" CHAR(20) NOT NULL CASE
);
CREATE UNIQUE INDEX "Course_Name" ON "Course"("Name");
CREATE INDEX "DeptName" ON "Course"("Dept_Name");
See CREATE TABLE in SQL Engine Reference.
Exporting a Schema
You can export a schema for a particular table (or tables) or, at the database level, for all tables at once.
►
To export a table schema for a particular table
1
In PSQL Explorer, expand the Tables node for the desired database.
If you are interested in system tables, expand the Tables node under System Objects.
2
Click the table name for which you want to export its schema.
If you want to export the schema for additional tables, press and hold Shift or Ctrl then click the desired table names. (In other words, use multiple select for the tables you want.)
3
Right-click the selected table name, then click Export Table Schema.
4
Type the name (and location if you want) for the exported file.
The default file extension is .sql.
5
Optionally, select the IN DICTIONARY or the USING option.
6
Click OK.
►
To export all table schemas at once
1
In PSQL Explorer, expand the Databases node.
2
Right-click the database name for which you want to export all of the table schemas.
3
Click Export Table Schema.
4
Type the name (and location if you want) for the exported file.
The default file extension is .sql.
5
Optionally, select the IN DICTIONARY or the USING option.
6
Click OK.