返回> 网站首页
FireBird 系统表相关SQL
yoours2011-07-14 13:16:25
简介一边听听音乐，一边写写文章。
一、List TABLEs (Include Views)
SELECT DISTINCT RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$SYSTEM_FLAG=0
或
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG=0
二、List TABLEs (Exclude Views)
SELECT DISTINCT RDB$RELATION_NAME FROM RDB$RELATION_FIELDS
WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_CONTEXT IS NULL
或
SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_BLR IS NULL
三、List VIEWs
SELECT DISTINCT RDB$VIEW_NAME FROM RDB$VIEW_RELATIONS
四、List users
SELECT DISTINCT RDB$USER FROM RDB$USER_PRIVILEGES
五、List INDICES
SELECT RDB$INDEX_NAME FROM RDB$INDICES
WHERE RDB$RELATION_NAME='Table' AND RDB$UNIQUE_FLAG IS NULL AND RDB$FOREIGN_KEY IS NULL
六、Detailed INDEX info
SELECT RDB$INDEX_SEGMENTS.RDB$FIELD_NAME AS field_name,
RDB$INDICES.RDB$DESCRIPTION AS description,
(RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION + 1) AS field_position
FROM RDB$INDEX_SEGMENTS
LEFT JOIN RDB$INDICES ON RDB$INDICES.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME
WHERE UPPER(RDB$INDICES.RDB$RELATION_NAME)='Table' -- table name
AND UPPER(RDB$INDICES.RDB$INDEX_NAME)='Table_Field_IDX' -- index name
AND RDB$RELATION_CONSTRAINTS.RDB$CONSTRAINT_TYPE IS NULL
ORDER BY RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION
七、List CONSTRAINTs
SELECT RDB$INDEX_NAME FROM RDB$INDICES
WHERE RDB$RELATION_NAME='TEST2' -- table name
AND (RDB$UNIQUE_FLAG IS NOT NULL OR RDB$FOREIGN_KEY IS NOT NULL)
八、List table fields
SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='Table'
下面是字段更多信息：
SELECT RDB$FIELD_NAME AS field_name,
RDB$FIELD_POSITION AS field_position,
RDB$DESCRIPTION AS field_description,
RDB$DEFAULT_value AS field_default_value,
RDB$NULL_FLAG AS field_not_null_constraint
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='Table'; -- table name
九、Detailed table field info
SELECT r.RDB$FIELD_NAME AS field_name,
r.RDB$DESCRIPTION AS field_description,
r.RDB$DEFAULT_value AS field_default_value,
r.RDB$NULL_FLAG AS field_not_null_constraint,
f.RDB$FIELD_LENGTH AS field_length,
f.RDB$FIELD_PRECISION AS field_precision,
f.RDB$FIELD_SCALE AS field_scale,
CASE f.RDB$FIELD_TYPE
WHEN 261 THEN 'BLOB'
WHEN 14 THEN 'CHAR'
WHEN 40 THEN 'CSTRING'
WHEN 11 THEN 'D_FLOAT'
WHEN 27 THEN 'DOUBLE'
WHEN 10 THEN 'FLOAT'
WHEN 16 THEN 'INT64'
WHEN 8 THEN 'INTEGER'
WHEN 9 THEN 'QUAD'
WHEN 7 THEN 'SMALLINT'
WHEN 12 THEN 'DATE'
WHEN 13 THEN 'TIME'
WHEN 35 THEN 'TIMESTAMP'
WHEN 37 THEN 'VARCHAR'
ELSE 'UNKNOWN'
END AS field_type,
f.RDB$FIELD_SUB_TYPE AS field_subtype,
coll.RDB$COLLATION_NAME AS field_collation,
cset.RDB$CHARACTER_SET_NAME AS field_charset
FROM RDB$RELATION_FIELDS r
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
WHERE r.RDB$RELATION_NAME='Table' -- table name
ORDER BY r.RDB$FIELD_POSITION;
注：8和16根据field_scale的值的情况可能为NUMERIC
十、List GENERATORs (sequences)
SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG IS NULL
十一、List TRIGGERs
SELECT * FROM RDB$TRIGGERS WHERE RDB$SYSTEM_FLAG IS NULL
指定表名：
SELECT * FROM RDB$TRIGGERS
WHERE RDB$SYSTEM_FLAG IS NULL
AND RDB$RELATION_NAME='Table'; -- table name
十二、List FUNCTIONs (UDF)
SELECT * FROM RDB$FUNCTIONS WHERE RDB$SYSTEM_FLAG IS NULL
十三、List Stored Procedures
SELECT * FROM RDB$PROCEDURES
十四、List FOREIGN KEY constraints
SELECT DISTINCT
rc.RDB$CONSTRAINT_NAME AS "constraint_name",
rc.RDB$RELATION_NAME AS "on table",
d1.RDB$FIELD_NAME AS "on field",
d2.RDB$DEPENDED_ON_NAME AS "references table",
d2.RDB$FIELD_NAME AS "references field",
refc.RDB$UPDATE_RULE AS "on update",
refc.RDB$DELETE_RULE AS "on delete"
FROM RDB$RELATION_CONSTRAINTS AS rc
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$DEPENDENCIES d1 ON d1.RDB$DEPENDED_ON_NAME = rc.RDB$RELATION_NAME
LEFT JOIN RDB$DEPENDENCIES d2 ON d1.RDB$DEPENDENT_NAME = d2.RDB$DEPENDENT_NAME
WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
AND d1.RDB$DEPENDED_ON_NAME <> d2.RDB$DEPENDED_ON_NAME
AND d1.RDB$FIELD_NAME <> d2.RDB$FIELD_NAME
AND rc.RDB$RELATION_NAME = 'Table' -- table name
十五、Detailed CONSTRAINT info
SELECT rc.RDB$CONSTRAINT_NAME,
s.RDB$FIELD_NAME AS field_name,
rc.RDB$CONSTRAINT_TYPE AS constraint_type,
i.RDB$DESCRIPTION AS description,
rc.RDB$DEFERRABLE AS is_deferrable,
rc.RDB$INITIALLY_DEFERRED AS is_deferred,
refc.RDB$UPDATE_RULE AS on_update,
refc.RDB$DELETE_RULE AS on_delete,
refc.RDB$MATCH_OPTION AS match_type,
i2.RDB$RELATION_NAME AS references_table,
s2.RDB$FIELD_NAME AS references_field,
(s.RDB$FIELD_POSITION + 1) AS field_position
FROM RDB$INDEX_SEGMENTS s
LEFT JOIN RDB$INDICES i ON i.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
LEFT JOIN RDB$INDICES i2 ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME
WHERE i.RDB$RELATION_NAME='Table' -- table name
AND rc.RDB$CONSTRAINT_NAME='FK_Field' -- constraint name
AND rc.RDB$CONSTRAINT_TYPE IS NOT NULL
ORDER BY s.RDB$FIELD_POSITION
十六、Detailed TRIGGER info
SELECT RDB$TRIGGER_NAME AS trigger_name,
RDB$RELATION_NAME AS table_name,
RDB$TRIGGER_SOURCE AS trigger_body,
CASE RDB$TRIGGER_TYPE
WHEN 1 THEN 'BEFORE'
WHEN 2 THEN 'AFTER'
WHEN 3 THEN 'BEFORE'
WHEN 4 THEN 'AFTER'
WHEN 5 THEN 'BEFORE'
WHEN 6 THEN 'AFTER'
END AS trigger_type,
CASE RDB$TRIGGER_TYPE
WHEN 1 THEN 'INSERT'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'UPDATE'
WHEN 4 THEN 'UPDATE'
WHEN 5 THEN 'DELETE'
WHEN 6 THEN 'DELETE'
END AS trigger_event,
CASE RDB$TRIGGER_INACTIVE
WHEN 1 THEN 0 ELSE 1
END AS trigger_enabled,
RDB$DESCRIPTION AS trigger_comment
FROM RDB$TRIGGERS
WHERE UPPER(RDB$TRIGGER_NAME)='Trigger'
十七、Detailed VIEW info
SELECT d.RDB$DEPENDENT_NAME AS view_name,
r.RDB$FIELD_NAME AS field_name,
d.RDB$DEPENDED_ON_NAME AS depended_on_table,
d.RDB$FIELD_NAME AS depended_on_field
FROM RDB$DEPENDENCIES d
LEFT JOIN RDB$RELATION_FIELDS r ON d.RDB$DEPENDENT_NAME = r.RDB$RELATION_NAME
AND d.RDB$FIELD_NAME = r.RDB$BASE_FIELD
WHERE UPPER(d.RDB$DEPENDENT_NAME)='View'
AND r.RDB$SYSTEM_FLAG = 0
AND d.RDB$DEPENDENT_TYPE = 1 --VIEW
ORDER BY r.RDB$FIELD_POSITION
列出View的内容：
SELECT RDB$VIEW_SOURCE FROM RDB$RELATIONS WHERE RDB$VIEW_SOURCE IS NOT NULLAND UPPER(RDB$RELATION_NAME) = 'View'
十八、engine version、network protocol
SELECT RDB$GET_CONTEXT('SYSTEM', 'ENGINE_VERSION') AS engine_version,
RDB$GET_CONTEXT('SYSTEM', 'NETWORK_PROTOCOL') AS protocol,
RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS') AS address
FROM RDB$DATABASE
SELECT DISTINCT RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$SYSTEM_FLAG=0
或
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG=0
二、List TABLEs (Exclude Views)
SELECT DISTINCT RDB$RELATION_NAME FROM RDB$RELATION_FIELDS
WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_CONTEXT IS NULL
或
SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_BLR IS NULL
三、List VIEWs
SELECT DISTINCT RDB$VIEW_NAME FROM RDB$VIEW_RELATIONS
四、List users
SELECT DISTINCT RDB$USER FROM RDB$USER_PRIVILEGES
五、List INDICES
SELECT RDB$INDEX_NAME FROM RDB$INDICES
WHERE RDB$RELATION_NAME='Table' AND RDB$UNIQUE_FLAG IS NULL AND RDB$FOREIGN_KEY IS NULL
六、Detailed INDEX info
SELECT RDB$INDEX_SEGMENTS.RDB$FIELD_NAME AS field_name,
RDB$INDICES.RDB$DESCRIPTION AS description,
(RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION + 1) AS field_position
FROM RDB$INDEX_SEGMENTS
LEFT JOIN RDB$INDICES ON RDB$INDICES.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME
WHERE UPPER(RDB$INDICES.RDB$RELATION_NAME)='Table' -- table name
AND UPPER(RDB$INDICES.RDB$INDEX_NAME)='Table_Field_IDX' -- index name
AND RDB$RELATION_CONSTRAINTS.RDB$CONSTRAINT_TYPE IS NULL
ORDER BY RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION
七、List CONSTRAINTs
SELECT RDB$INDEX_NAME FROM RDB$INDICES
WHERE RDB$RELATION_NAME='TEST2' -- table name
AND (RDB$UNIQUE_FLAG IS NOT NULL OR RDB$FOREIGN_KEY IS NOT NULL)
八、List table fields
SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='Table'
下面是字段更多信息：
SELECT RDB$FIELD_NAME AS field_name,
RDB$FIELD_POSITION AS field_position,
RDB$DESCRIPTION AS field_description,
RDB$DEFAULT_value AS field_default_value,
RDB$NULL_FLAG AS field_not_null_constraint
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='Table'; -- table name
九、Detailed table field info
SELECT r.RDB$FIELD_NAME AS field_name,
r.RDB$DESCRIPTION AS field_description,
r.RDB$DEFAULT_value AS field_default_value,
r.RDB$NULL_FLAG AS field_not_null_constraint,
f.RDB$FIELD_LENGTH AS field_length,
f.RDB$FIELD_PRECISION AS field_precision,
f.RDB$FIELD_SCALE AS field_scale,
CASE f.RDB$FIELD_TYPE
WHEN 261 THEN 'BLOB'
WHEN 14 THEN 'CHAR'
WHEN 40 THEN 'CSTRING'
WHEN 11 THEN 'D_FLOAT'
WHEN 27 THEN 'DOUBLE'
WHEN 10 THEN 'FLOAT'
WHEN 16 THEN 'INT64'
WHEN 8 THEN 'INTEGER'
WHEN 9 THEN 'QUAD'
WHEN 7 THEN 'SMALLINT'
WHEN 12 THEN 'DATE'
WHEN 13 THEN 'TIME'
WHEN 35 THEN 'TIMESTAMP'
WHEN 37 THEN 'VARCHAR'
ELSE 'UNKNOWN'
END AS field_type,
f.RDB$FIELD_SUB_TYPE AS field_subtype,
coll.RDB$COLLATION_NAME AS field_collation,
cset.RDB$CHARACTER_SET_NAME AS field_charset
FROM RDB$RELATION_FIELDS r
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
WHERE r.RDB$RELATION_NAME='Table' -- table name
ORDER BY r.RDB$FIELD_POSITION;
注：8和16根据field_scale的值的情况可能为NUMERIC
十、List GENERATORs (sequences)
SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG IS NULL
十一、List TRIGGERs
SELECT * FROM RDB$TRIGGERS WHERE RDB$SYSTEM_FLAG IS NULL
指定表名：
SELECT * FROM RDB$TRIGGERS
WHERE RDB$SYSTEM_FLAG IS NULL
AND RDB$RELATION_NAME='Table'; -- table name
十二、List FUNCTIONs (UDF)
SELECT * FROM RDB$FUNCTIONS WHERE RDB$SYSTEM_FLAG IS NULL
十三、List Stored Procedures
SELECT * FROM RDB$PROCEDURES
十四、List FOREIGN KEY constraints
SELECT DISTINCT
rc.RDB$CONSTRAINT_NAME AS "constraint_name",
rc.RDB$RELATION_NAME AS "on table",
d1.RDB$FIELD_NAME AS "on field",
d2.RDB$DEPENDED_ON_NAME AS "references table",
d2.RDB$FIELD_NAME AS "references field",
refc.RDB$UPDATE_RULE AS "on update",
refc.RDB$DELETE_RULE AS "on delete"
FROM RDB$RELATION_CONSTRAINTS AS rc
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$DEPENDENCIES d1 ON d1.RDB$DEPENDED_ON_NAME = rc.RDB$RELATION_NAME
LEFT JOIN RDB$DEPENDENCIES d2 ON d1.RDB$DEPENDENT_NAME = d2.RDB$DEPENDENT_NAME
WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
AND d1.RDB$DEPENDED_ON_NAME <> d2.RDB$DEPENDED_ON_NAME
AND d1.RDB$FIELD_NAME <> d2.RDB$FIELD_NAME
AND rc.RDB$RELATION_NAME = 'Table' -- table name
十五、Detailed CONSTRAINT info
SELECT rc.RDB$CONSTRAINT_NAME,
s.RDB$FIELD_NAME AS field_name,
rc.RDB$CONSTRAINT_TYPE AS constraint_type,
i.RDB$DESCRIPTION AS description,
rc.RDB$DEFERRABLE AS is_deferrable,
rc.RDB$INITIALLY_DEFERRED AS is_deferred,
refc.RDB$UPDATE_RULE AS on_update,
refc.RDB$DELETE_RULE AS on_delete,
refc.RDB$MATCH_OPTION AS match_type,
i2.RDB$RELATION_NAME AS references_table,
s2.RDB$FIELD_NAME AS references_field,
(s.RDB$FIELD_POSITION + 1) AS field_position
FROM RDB$INDEX_SEGMENTS s
LEFT JOIN RDB$INDICES i ON i.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
LEFT JOIN RDB$INDICES i2 ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME
WHERE i.RDB$RELATION_NAME='Table' -- table name
AND rc.RDB$CONSTRAINT_NAME='FK_Field' -- constraint name
AND rc.RDB$CONSTRAINT_TYPE IS NOT NULL
ORDER BY s.RDB$FIELD_POSITION
十六、Detailed TRIGGER info
SELECT RDB$TRIGGER_NAME AS trigger_name,
RDB$RELATION_NAME AS table_name,
RDB$TRIGGER_SOURCE AS trigger_body,
CASE RDB$TRIGGER_TYPE
WHEN 1 THEN 'BEFORE'
WHEN 2 THEN 'AFTER'
WHEN 3 THEN 'BEFORE'
WHEN 4 THEN 'AFTER'
WHEN 5 THEN 'BEFORE'
WHEN 6 THEN 'AFTER'
END AS trigger_type,
CASE RDB$TRIGGER_TYPE
WHEN 1 THEN 'INSERT'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'UPDATE'
WHEN 4 THEN 'UPDATE'
WHEN 5 THEN 'DELETE'
WHEN 6 THEN 'DELETE'
END AS trigger_event,
CASE RDB$TRIGGER_INACTIVE
WHEN 1 THEN 0 ELSE 1
END AS trigger_enabled,
RDB$DESCRIPTION AS trigger_comment
FROM RDB$TRIGGERS
WHERE UPPER(RDB$TRIGGER_NAME)='Trigger'
十七、Detailed VIEW info
SELECT d.RDB$DEPENDENT_NAME AS view_name,
r.RDB$FIELD_NAME AS field_name,
d.RDB$DEPENDED_ON_NAME AS depended_on_table,
d.RDB$FIELD_NAME AS depended_on_field
FROM RDB$DEPENDENCIES d
LEFT JOIN RDB$RELATION_FIELDS r ON d.RDB$DEPENDENT_NAME = r.RDB$RELATION_NAME
AND d.RDB$FIELD_NAME = r.RDB$BASE_FIELD
WHERE UPPER(d.RDB$DEPENDENT_NAME)='View'
AND r.RDB$SYSTEM_FLAG = 0
AND d.RDB$DEPENDENT_TYPE = 1 --VIEW
ORDER BY r.RDB$FIELD_POSITION
列出View的内容：
SELECT RDB$VIEW_SOURCE FROM RDB$RELATIONS WHERE RDB$VIEW_SOURCE IS NOT NULLAND UPPER(RDB$RELATION_NAME) = 'View'
十八、engine version、network protocol
SELECT RDB$GET_CONTEXT('SYSTEM', 'ENGINE_VERSION') AS engine_version,
RDB$GET_CONTEXT('SYSTEM', 'NETWORK_PROTOCOL') AS protocol,
RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS') AS address
FROM RDB$DATABASE
文章评论
1654人参与，0条评论