資源描述：
《SQL查詢表名、列名、列屬性》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、Oracle:?1.查詢表名： selecttable_name,tablespace_name,temporary?fromuser_tables?//在所有非管理員創(chuàng)建的表中查詢??????select?table_name,tablespace_name,temporary?fromdba_tables?//在所有管理員創(chuàng)建的表中查詢??????select?table_name,tablespace_name,temporary?fromall_tables?//在所有表中查詢??????select?table_name
2、,tablespace_name,temporary?fromall_tables?where?table_name='表名'?//在所有表中查詢指定表???????selecttable_name,tablespace_name,temporary?fromall_tables?where?lespace_name='表空間名'//在所有表中查詢屬性指定表空間的表?????? 其中：table_name:表名（varchar2(30)）; tablespace_name:存儲表名的表空間（varchar2（30））； tem
3、porary:能無法為暫時表（varchar2（1））?！ g:?selecttable_name,tablespace_name,temporary?from?user_tables?wheretable_name='TEST_TEMP'; 結(jié)果： -------------------------------------------------------------------------------- table_nametablespace_name?temporary TEST_TEMPSDMP?N ----
4、---------------------------------------------------------------------------- 注：表名變量值必須大寫。 2.查詢表列名： 復(fù)制代碼代碼如下:?????SELECT?COLUMN_NAME?FROM?USER_TAB_COLUMNS?WHERE?TABLE_NAME?=?'表名'ORDER?BY?COLUMN_ID?????SELECT?COLUMN_NAME?FROM?DBA_TAB_COLUMNS?WHERETABLE_NAME?=?'表名'?ORD
5、ER?BY?COLUMN_ID?????SELECT?COLUMN_NAME?FROM?ALL_TAB_COLUMNS?WHERE?TABLE_NAME?=?'表名'ORDER?BY?COLUMN_ID selectcolumn_name,data_type?,data_length,data_precision,data_scale?fromuser_tab_columns?where?table_name='表名'; 其中：column_name:列名（varchar2(30)）; data_type:列的數(shù)據(jù)類型（var
6、char2(106)）; data_length:列的長度（number); eg:selectcolumn_name,data_type?,data_length,data_precision,data_scale?fromuser_tab_columns?where?table_name='TEST_TEMP';??????結(jié)果：??????column_name?data_type?data_length?data_precision?data_scale? ID?NUMBER?220 NAMENVARCHAR2?20
7、 SEX?CHAR1 GRADENVARCHAR2?10 -------------------------------------------------------------------------------- 注：表名變量值必須大寫?！ ×硗猓部梢?議決all_tab_columns來獲取有關(guān)表的數(shù)據(jù)?！ g:select?*from?all_tab_columns?where?table_name='TEST_TEMP';?sqlserver:select?namefrom?syscolumns?where?
8、id=object_id('表名')select?count(*)from?syscolumns?where?id=object_id('表名')--讀取庫中的所有表名?select?name?from?sysobjects?wher
