Hi,

> Following Query can be used to get the complete Table Schema in
> Firebird....
>
> Any Suggestion Please Forward....
> Thanks In Advance
> ====================================================================
>
> SELECT
> R.RDB$FIELD_POSITION AS ORDINAL_POSITION,
>
> CASE WHEN 1 <= (SELECT
> COUNT(D1.RDB$DEPENDED_ON_NAME)
> FROM
> RDB$DEPENDENCIES D1
> WHERE
> D1.RDB$DEPENDED_ON_TYPE = 14 AND
> D1.RDB$DEPENDENT_NAME IN
> (SELECT
> D.RDB$DEPENDENT_NAME
> FROM
> RDB$DEPENDENCIES D
> WHERE
> D.RDB$DEPENDENT_TYPE = 2 AND
> D.RDB$DEPENDED_ON_TYPE = 0 AND
> D.RDB$DEPENDED_ON_NAME =
> R.RDB$RELATION_NAME AND
> D.RDB$FIELD_NAME =R.RDB$FIELD_NAME)
> )THEN 1
> ELSE 0
> END AS AUTO_INCREMENT,

Nope, this doesn't say anything. Firebird does not have a "true"
auto-increment facility linked to a column in a table. The only thing
this say, if there's some kind of registered dependency.

>
> CASE WHEN 1 <= (SELECT
> COUNT(I.RDB$INDEX_NAME)
> FROM
> RDB$INDICES I, RDB$INDEX_SEGMENTS S,

I suggest you learn to use the proper JOIN syntax.

> RDB$RELATION_CONSTRAINTS C
> WHERE
> I.RDB$INDEX_NAME = S.RDB$INDEX_NAME AND
> C.RDB$INDEX_NAME = S.RDB$INDEX_NAME AND
> C.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY' AND
> S.RDB$FIELD_NAME = R.RDB$FIELD_NAME AND
> C.RDB$RELATION_NAME = R.RDB$RELATION_NAME
> )THEN 1
> ELSE 0
> END AS PRIMARY_KEY,
> CASE WHEN 1 <= (SELECT
> COUNT(I.RDB$INDEX_NAME)
> FROM
> RDB$INDICES I, RDB$INDEX_SEGMENTS S
> WHERE
> I.RDB$INDEX_NAME = S.RDB$INDEX_NAME AND
> R.RDB$FIELD_NAME = S.RDB$FIELD_NAME AND
> I.RDB$RELATION_NAME = R.RDB$RELATION_NAME
> )THEN 1
> ELSE 0
> END AS INDEX_KEY,

What would an "index key" be exactly? What should it tell you?

> CASE WHEN 1 <= (SELECT
> COUNT(I.RDB$INDEX_NAME)
> FROM
> RDB$INDICES I, RDB$INDEX_SEGMENTS S
> WHERE
> I.RDB$INDEX_NAME = S.RDB$INDEX_NAME AND
> R.RDB$FIELD_NAME = S.RDB$FIELD_NAME AND
> I.RDB$UNIQUE_FLAG = 1 AND
> I.RDB$RELATION_NAME=R.RDB$RELATION_NAME
> ) THEN 1
> ELSE 0
> END AS UNIQUE_KEY,

Same here, what should this tell you?

> R.RDB$FIELD_NAME AS FIELD_NAME,
>
> CASE WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'SHORT' THEN 0
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'LONG' THEN 1
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'INT64' AND
> F.RDB$FIELD_SUB_TYPE = 0 THEN 2
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'INT64' AND
> F.RDB$FIELD_SUB_TYPE = 1 THEN 4
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'INT64' AND
> F.RDB$FIELD_SUB_TYPE = 2 THEN 8
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'FLOAT' THEN 16
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'DOUBLE' THEN 32
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'DATE' THEN 64
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'TIME' THEN 128
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'TIMESTAMP' THEN 256
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'TEXT' THEN 512
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'VARYING' THEN 1024
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'BLOB' AND
> F.RDB$FIELD_SUB_TYPE = 0 THEN 2048
> WHEN UPPER(TRIM(I.RDB$TYPE_NAME)) = 'BLOB' AND
> F.RDB$FIELD_SUB_TYPE = 1 THEN 4096
> ELSE -1
> END AS FIELD_TYPE,
>
> F.RDB$FIELD_LENGTH AS FIELD_SIZE,
>
> CASE WHEN F.RDB$SEGMENT_LENGTH IS NULL THEN 0
> ELSE F.RDB$SEGMENT_LENGTH
> END AS SEGMENT_SIZE,
>
> CASE WHEN R.RDB$NULL_FLAG = 1 THEN 1
> ELSE 0
> END AS NOT_NULL,
>
> CASE WHEN F.RDB$FIELD_PRECISION IS NULL THEN 0
> ELSE F.RDB$FIELD_PRECISION
> END AS NUMERIC_PRECISION,
>
> CASE WHEN F.RDB$FIELD_SCALE IS NULL THEN 0
> ELSE -1 * F.RDB$FIELD_SCALE
> END AS NUMERIC_SCALE,
>
> CASE WHEN R.RDB$DEFAULT_VALUE IS NOT NULL THEN 1
> ELSE 0
> END AS COLUMN_HASDEFAULT,
>
> R.RDB$DEFAULT_VALUE AS COLUMN_DEFAULT,
> R.RDB$DESCRIPTION AS DESCRIPTION
> FROM
> RDB$FIELDS F, RDB$TYPES I, RDB$RELATION_FIELDS R

Again, use the proper JOIN syntax.

> WHERE
> F.RDB$FIELD_TYPE = I.RDB$TYPE AND
> I.RDB$FIELD_NAME = 'RDB$FIELD_TYPE' AND
> F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE AND
> R.RDB$RELATION_NAME = '<TABLENAME>'
> ORDER BY
> ORDINAL_POSITION ASC

Oh, and the good news: your queries won't work with Firebird 1
cause it doesn't know "case".

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com