Firebird SQL 獲取系統數據的多個語句

一、分頁寫法小例：

select first 10 templateid,code,name from template ;
select first 10 skip 10 templateid,code,name from template ;
select * from shop rows 1 to 10; --firebird2.0支持這種寫法

二、show

show tables;
show table tablename

三、

D:firebird2in>isql -u sysdba -p masterkey
connect "E:companyxmwsoft ewxmwsoftc2dbcts2.fdb";

四、--更新字段注釋

update RDB$RELATION_FIELDS
set RDB$DESCRIPTION = '描述信息'
where (RDB$RELATION_NAME = 'SHOP') and
(RDB$FIELD_NAME = 'CREDIT_BUY')

五、--顯示字段注釋

select RDB$FIELD_NAME,RDB$DESCRIPTION from RDB$RELATION_FIELDS
where (RDB$RELATION_NAME = 'SHOP')
and
(RDB$FIELD_NAME = 'CREDIT_BUY')

六、--更新表注釋

update RDB$RELATIONS set RDB$DESCRIPTION = '描述信息' where RDB$RELATION_NAME='TABLE_NAME'七、--查詢所有的表和視圖（包括系統表和系統視圖）

SELECT RDB$RELATION_NAME FROM RDB$RELATIONS;

八、--查詢所有的用戶表和用戶視圖

SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG = 0;九、--查詢所有的用戶表

SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG = 0 AND RDB$VIEW_BLR IS NULL

十、查所有用戶表、用戶視圖所有字段及相關定義

SELECT a.RDB$RELATION_NAME, b.RDB$FIELD_NAME, b.RDB$FIELD_ID, d.RDB$TYPE_NAME,
c.RDB$FIELD_LENGTH, c.RDB$FIELD_SCALE
FROM RDB$RELATIONS a
INNER JOIN RDB$RELATION_FIELDS b
ON a.RDB$RELATION_NAME = b.RDB$RELATION_NAME
INNER JOIN RDB$FIELDS c
ON b.RDB$FIELD_SOURCE = c.RDB$FIELD_NAME
INNER JOIN RDB$TYPES d
ON c.RDB$FIELD_TYPE = d.RDB$TYPE
WHERE a.RDB$SYSTEM_FLAG = 0
AND d.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
ORDER BY a.RDB$RELATION_NAME, b.RDB$FIELD_ID

十一、查找某表的所有字段及相關定義

SELECT A.RDB$FIELD_NAME, B.RDB$FIELD_TYPE, B.RDB$FIELD_LENGTH, B.RDB$FIELD_PRECISION, B.RDB$FIELD_SCALE
FROM RDB$RELATION_FIELDS A, RDB$FIELDS B
WHERE A.RDB$RELATION_NAME = 'tablename'
AND A.RDB$FIELD_SOURCE = B.RDB$FIELD_NAME
ORDER BY A.RDB$FIELD_POSITION

十二、查找某表的主鍵定義字段

select A.RDB$FIELD_NAME FROM RDB$INDEX_SEGMENTS A, RDB$RELATION_CONSTRAINTS B
WHERE B.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
AND B.RDB$RELATION_NAME = 'tablename'
AND A.RDB$INDEX_NAME = B.RDB$INDEX_NAME
ORDER BY A.RDB$FIELD_POSITION

十三、查找某表的外鍵定義

select r1.RDB$CONSTRAINT_NAME, rind.RDB$FIELD_NAME, r2.RDB$RELATION_NAME
FROM RDB$RELATION_CONSTRAINTS r1, RDB$RELATION_CONSTRAINTS r2, RDB$REF_CONSTRAINTS ref, RDB$INDEX_SEGMENTS rind
where r1.RDB$RELATION_NAME = 'tablename'
and r1.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
and r1.RDB$CONSTRAINT_NAME = ref.RDB$CONSTRAINT_NAME
and ref.RDB$CONST_NAME_UQ = r2.RDB$CONSTRAINT_NAME
and r1.RDB$INDEX_NAME = rind.RDB$INDEX_NAME十四 獲取用戶表的所有字段

SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME ='[TABLE_NAME]'

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈