VistaDB 6
VistaDB / Developer's Guide / How To Perform Common Tasks / How To - Get the Database Schema using SQL
In This Topic
    How To - Get the Database Schema using SQL
    In This Topic

    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