VistaDB 5
Gibraltar VistaDB > Developer's Guide > SQL Reference > SQL Reference - Database Schema
SQL Reference - Database Schema
To get the current schema from the database use the following SQL:
SELECT * FROM [database schema] WHERE typeid = 1

This will return all the current tables in the active database.

Just performing a SELECT * on the database schema will return all of the objects in the database. The following table represents the meaning of the typeid enumeration.

TypeID Internal Type Description
1 Table Table entries
2 Index Tables index structures
3 Column Table column entries
4 Constraint Table constraint entries
5 DefaultValue Default value script
6 Identity Identity Entry
7 Relationship Foreign Key constraint
8 Trigger Database trigger
9 Database Description User entered description of database
10 View View entry
11 CLR Stored proc Entry point name of CLR Proc
12 Assembly Assembly name included in database for CLR procedures
13 CLRTrigger Entry point name of CLR Trigger
14 StoredProcedure TSQL Stored procedure entry
15 UDF Entry User defined functions written in TSQL

Use Try Catch Blocks to test for schema

Use a TRY CATCH block to test for a column that you need to add and handle the exception. This syntax also works in SQL Server as well.

Try Catch Example
Copy Code
   select StorageTypeId from storage;
   PRINT 'Column does not exist';
   ALTER TABLE Storage ADD StorageTypeId Int;

This same code could be written as below, but the language complexity is quite a bit higher and prone to error.

Alternate Schema Test Example
Copy Code
-- Declare the variable
DECLARE @tablepresent as bit;

/* Add StorageTypeId to the Storage table. *//
SET @tablepresent = SELECT EXISTS(SELECT name FROM [database schema]
  WHERE  typeid = 3 AND name = 'StorageTypeId' AND foreignReference =
  (SELECT objectId FROM [database schema] WHERE  typeid = 1 AND name = 'Storage'));

IF @tablepresent = 1
   PRINT 'ADDING COLUMN TO Storage Table';
   ALTER TABLE Storage ADD StorageTypeId Int;


You cannot self reference or performs joins against the database schema object. It is not built for that type of operation. If you need to get schema out of a database in ADO.NET the best practice is to use the GetSchema call on the connection.


You can use multiple statements to achieve the same results as self referencing query, but we don't recommend this as a best practice.  It is included here for demo purposes only.

Mutlistatement Example
Copy Code
DECLARE @fkref as NCHAR(50);

SET @fkref = SELECT objectId from [database schema] WHERE  typeid = 1 and name = 'FileLinks';

SELECT EXISTS( SELECT name from [database schema] WHERE  typeid = 3 and
   name = 'FileLinkId' and foreignReference = @fkref );
See Also