VistaDB 5
Gibraltar VistaDB > Developer's Guide > How To Perform Common Tasks > How To - Get the Database Schema using SQL
How To - Get the Database Schema using SQL

To get the current schema from the database use the following SQL: 

Select all tables from Schema
Copy Code
select * from [database schema] where typeid = 1 

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

You CANNOT self join to this table. It does not accept that behavior and will not return correct results. You must execute it in two statements rather than a single statement in a self join.
Check Schema in For Items
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 ); 

TypeID enumerator meanings

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
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.

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 Schema Change
Copy Code
BEGIN TRY
   select StorageTypeId from storage;
END TRY
BEGIN CATCH
   PRINT 'Column does not exist';
   ALTER TABLE Storage ADD StorageTypeId Int;
END CATCH 

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

Alternate Test prior to Schema Change
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
  BEGIN
   PRINT 'COLUMN ALREADY EXISTS - SKIPPING';
  END
ELSE
  BEGIN
   PRINT 'ADDING COLUMN TO Storage Table';
   ALTER TABLE Storage ADD StorageTypeId Int;
  END 
See Also