--- In firebird-support@yahoogroups.com, "William Gonzáles S."
<wgonzaless@y...> wrote:

>
> Hello, I am migrating a SQL Server DB to FB, my
> application uses SQL queries to verify if a certain
> table exists and if a certain column exists in a
> table, the sentences are:
>
> //Does the table exist?
> select name from sysobjects where (name =
> 'TABLE_NAME') and (type = 'U')

select first 1 1
from TABLE_NAME

if it throws an exception, the table isn't there.

>
> //Does the column exist?
> select name from syscolumns where name = 'COLUMN_NAME'
>

This query is absolutely useless unless you are specifying the
tablename

> //Does the column (COL_NAME) exist in the TABLE
> 'TBL_NAME'?
> select syscolumns.name
> from syscolumns, sysobjects
> where syscolumns.id = sysobjects.id
> and sysobjects.name = 'TBL_NAME' and sysobjects.type
> = 'U'
> and syscolumns.name = 'COL_NAME'
>

select first 1 COL_NAME
from TBL_NAME

Again, if it throws an exception, then either COL_NAME or TBL_NAME
doesn't exist, the exception will probably tell you which.

Of course you could hunt through the system tables if you really
wanted.

SELECT RDB$RELATION_NAME AS TABLE_NAME
FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG = 0
AND RDB$VIEW_SOURCE IS NULL;

Gives you all your tables.

SELECT RF.RDB$FIELD_NAME AS FieldName,
CASE
WHEN T.RDB$TYPE_NAME = 'VARYING' THEN 'VARCHAR'
WHEN T.RDB$TYPE_NAME = 'TEXT' THEN 'CHAR'
WHEN T.RDB$TYPE_NAME = 'INT64' THEN 'BIGINT'
WHEN T.RDB$TYPE_NAME = 'LONG' THEN 'INTEGER'
ELSE
T.RDB$TYPE_NAME
END AS DataType,
F.RDB$FIELD_LENGTH AS FieldLength,
RF.RDB$NULL_FLAG AS AllowNulls,
CS.RDB$DEFAULT_COLLATE_NAME AS CharacterSet,
RF.RDB$DEFAULT_SOURCE AS DefaultValue,
F.RDB$COMPUTED_SOURCE AS ComputedSource,
F.RDB$FIELD_SUB_TYPE AS SubType,
F.RDB$FIELD_PRECISION AS FieldPrecision
FROM RDB$RELATION_FIELDS RF
LEFT JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
LEFT JOIN RDB$TYPES T ON (T.RDB$TYPE = F.RDB$FIELD_TYPE)
LEFT JOIN RDB$CHARACTER_SETS CS ON (CS.RDB$CHARACTER_SET_ID =
F.RDB$CHARACTER_SET_ID)
WHERE RF.RDB$RELATION_NAME = 'EMPLOYEE' AND
T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
ORDER BY RF.RDB$FIELD_POSITION;

Gives the field information for a given table.

You can pull these apart and design your own if you ***really***
wanted. Personally, I think it is less work for all concerned to
simply run a query and interpret the exception.

Adam