Firebird常用系统表Sql语句

--得到数据库的所有者名称
SELECT distinct RDB$OWNER_NAME AS DATABASE_OWNER
FROM RDB$RELATIONS
WHERE (RDB$SYSTEM_FLAG = 1);

--根据表名得到表的主键
SELECT RC.RDB$CONSTRAINT_NAME AS CONSTRAINT_NAME,
I.RDB$RELATION_NAME AS TABLE_NAME,
S.RDB$FIELD_NAME AS COLUMN_NAME
FROM RDB$RELATION_CONSTRAINTS RC
LEFT JOIN RDB$INDICES I ON
   (I.RDB$INDEX_NAME = RC.RDB$INDEX_NAME)
LEFT JOIN RDB$INDEX_SEGMENTS S ON
   (S.RDB$INDEX_NAME = I.RDB$INDEX_NAME)
WHERE (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY')
AND (I.RDB$RELATION_NAME = 'COUNTRY')

---根据表名得到其相关的依赖.
SELECT RDB$DEPENDENT_NAME AS DEPENDENT_NAME,
RDB$DEPENDED_ON_NAME AS DEPENDS_ON,
RDB$FIELD_NAME AS FIELD_NAME,
RDB$DEPENDENT_TYPE DEPENDENT_TYPE,
   CASE
    WHEN RDB$DEPENDENT_TYPE = 0 THEN 'TABLE'
    WHEN RDB$DEPENDENT_TYPE = 1 THEN 'VIEW'
    WHEN RDB$DEPENDENT_TYPE = 2 THEN 'TRIGGER'
    WHEN RDB$DEPENDENT_TYPE = 3 THEN 'COMPUTED'
    WHEN RDB$DEPENDENT_TYPE = 4 THEN 'VALIDATION'
    WHEN RDB$DEPENDENT_TYPE = 5 THEN 'PROCEDURE'
    WHEN RDB$DEPENDENT_TYPE = 6 THEN 'expression_r_INDEX'
    WHEN RDB$DEPENDENT_TYPE = 7 THEN 'EXCEPTION'
    WHEN RDB$DEPENDENT_TYPE = 8 THEN 'USER'
    WHEN RDB$DEPENDENT_TYPE = 9 THEN 'FIELD'
    WHEN RDB$DEPENDENT_TYPE = 10 THEN 'INDEX'
    WHEN RDB$DEPENDENT_TYPE = 14 THEN 'GENERATOR'
    ELSE 'UNKNOWN'
    END AS DEPENDENT_TYPE_STR,
   RDB$DEPENDED_ON_TYPE AS DEPEND_ON_TYPE,
   CASE
    WHEN RDB$DEPENDED_ON_TYPE = 0 THEN 'TABLE'
    WHEN RDB$DEPENDED_ON_TYPE = 1 THEN 'VIEW'
    WHEN RDB$DEPENDED_ON_TYPE = 2 THEN 'TRIGGER'
    WHEN RDB$DEPENDED_ON_TYPE = 3 THEN 'COMPUTED'
    WHEN RDB$DEPENDED_ON_TYPE = 4 THEN 'VALIDATION'
    WHEN RDB$DEPENDED_ON_TYPE = 5 THEN 'PROCEDURE'
    WHEN RDB$DEPENDED_ON_TYPE = 6 THEN 'expression_r_INDEX'
    WHEN RDB$DEPENDED_ON_TYPE = 7 THEN 'EXCEPTION'
    WHEN RDB$DEPENDED_ON_TYPE = 8 THEN 'USER'
    WHEN RDB$DEPENDED_ON_TYPE = 9 THEN 'FIELD'
    WHEN RDB$DEPENDED_ON_TYPE = 10 THEN 'INDEX'
    WHEN RDB$DEPENDED_ON_TYPE = 14 THEN 'GENERATOR'
    ELSE 'UNKNOWN'
    END AS DEPENDS_ON_TYPE_STR
FROM RDB$DEPENDENCIES WHERE RDB$DEPENDED_ON_NAME='JOB'
ORDER BY RDB$DEPENDENT_NAME

--根据索引名得到表名和与及其索引相关的列
SELECT I.RDB$RELATION_NAME AS TABLENAME,
S.RDB$FIELD_NAME AS FIELDNAME
FROM RDB$INDICES I
LEFT JOIN RDB$INDEX_SEGMENTS S ON
   (S.RDB$INDEX_NAME = I.RDB$INDEX_NAME)
WHERE (I.RDB$INDEX_NAME = 'NAMEX');

--根据表名得到其上所有的索引
SELECT I.RDB$INDEX_NAME AS FKINDEXNAME,
I.RDB$RELATION_NAME AS TABLENAME,
S.RDB$FIELD_NAME AS FIELDNAME
FROM RDB$INDICES I
LEFT JOIN RDB$INDEX_SEGMENTS S ON (S.RDB$INDEX_NAME = I.RDB$INDEX_NAME)
WHERE I.RDB$RELATION_NAME = 'CUSTOMER'
AND (NOT EXISTS(SELECT 1
   FROM RDB$RELATION_CONSTRAINTS R WHERE R.RDB$INDEX_NAME = I.RDB$INDEX_NAME))

--得到所有的用户表
SELECT RDB$RELATION_NAME AS TABLE_NAME
FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG = 0
AND RDB$VIEW_SOURCE IS NULL;

--根据表名与字段得到索引
SELECT I.RDB$INDEX_NAME AS FKINDEXNAME,
I.RDB$RELATION_NAME AS TABLENAME,
S.RDB$FIELD_NAME AS FIELDNAME
FROM RDB$INDICES I
LEFT JOIN RDB$INDEX_SEGMENTS S ON (S.RDB$INDEX_NAME = I.RDB$INDEX_NAME)
WHERE I.RDB$FOREIGN_KEY IS NOT NULL
AND I.RDB$RELATION_NAME = 'CUSTOMER'
AND S.RDB$FIELD_NAME = 'COUNTRY';

--根据表名得到列信息
SELECT RF.RDB$FIELD_NAME AS FieldName,
T.RDB$TYPE_NAME 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;

--检测一个列是否存在,结果为1存在,不为1不存在
SELECT COUNT(RF.RDB$FIELD_NAME)
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)
WHERE RF.RDB$RELATION_NAME = 'EMPLOYEE' AND
T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE' AND
RF.RDB$FIELD_NAME = 'EMP_NO'

--检测一个表是否存在
 SELECT COUNT(RDB$RELATION_NAME)
   FROM RDB$RELATIONS
   WHERE (RDB$RELATION_NAME = 'CUSTOMER')
    AND RDB$VIEW_SOURCE IS NULL;

或者

 SELECT  RDB$RELATION_NAME,  RDB$OWNER_NAME FROM RDB$RELATIONS where (RDB$RELATION_NAME='表名')   注意表名大小写！

--得到数据库中所有的视图
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$FLAGS = 1
AND RDB$VIEW_SOURCE IS NOT NULL;

--得到所有的系统表
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE (RDB$FLAGS = 0 OR RDB$FLAGS IS NULL)
AND RDB$VIEW_SOURCE IS NULL;