怎样用SQL语句查询一个数据库中的所有表? 怎样用SQL语句查询一个数据库中的所有表?
\u600e\u4e48\u7528Sql\u8bed\u53e5\u83b7\u53d6\u4e00\u4e2a\u6570\u636e\u5e93\u4e2d\u7684\u6240\u6709\u8868\u7684\u540d\u5b57\u5728\u7a0b\u5e8f\u4e2d\u901a\u8fc7sql\u8bed\u53e5\u67e5\u8be2\u6765\u83b7\u5f97\u67d0\u4e2a\u6570\u636e\u5e93\u7684\u6240\u6709\u8868\u540d\uff0c\u4ee3\u7801\u5982\u4e0b\uff1a
SELECT
table_name
FROM
information_schema.tables
WHERE table_schema = 'mydatabasename'
AND table_type = 'base table'
\u6269\u5c55\u8d44\u6599
1\uff0c\u5229\u7528sys.tables\u76ee\u5f55\u89c6\u56fe\u67e5\u8be2\u6240\u6709\u8868\u7684\u540d\u5b57\uff0csys.tables\u76ee\u5f55\u89c6\u56fe\u4e3a\u6bcf\u4e2a\u8868\u5bf9\u8c61\u8fd4\u56de\u4e00\u884c. \u793a\u4f8b\u8bed\u53e5\u5982\u4e0b\uff1a
select * from sys.tables
\u6ce8\u610f\uff1asys.tables\u76ee\u5f55\u89c6\u56fe\u4e5f\u53ea\u6709\u5728SQL SERVER2005\u53ca\u4ee5\u4e0a\u7684\u7248\u672c\u4e2d\u624d\u80fd\u4f7f\u7528\u3002
2\uff0c\u5229\u7528\u5b58\u50a8\u8fc7\u7a0bsp_tables sp_tables\u5b58\u50a8\u8fc7\u7a0b,\u53ef\u8fd4\u56de\u53ef\u5728\u5f53\u524d\u73af\u5883\u4e2d\u67e5\u8be2\u7684\u5bf9\u8c61\u5217\u8868\u3002\u8fd9\u4ee3\u8868\u53ef\u5728FROM\u5b50\u53e5\u4e2d\u51fa\u73b0\u7684\u4efb\u4f55\u5bf9\u8c61\u3002 \u6211\u4eec\u53ef\u4ee5\u6267\u884c\u5982\u4e0b\u8bed\u53e5\uff1a
exec sp_tables
\u5728\u7ed3\u679c\u96c6\u4e2d\u7b5b\u9009\u51fa\u6240\u6709TABLE_TYPE\u7b49\u4e8eTABLE\u7684\u8bb0\u5f55\u5c31\u662f\u8868\u4fe1\u606f\u4e86\u3002
\u53c2\u8003\u8d44\u6599\uff1a\u767e\u5ea6\u767e\u79d1 sql\u8bed\u53e5
\u767e\u5ea6\u767e\u79d1 sys.tables
--\u8bfb\u53d6\u5e93\u4e2d\u7684\u6240\u6709\u8868\u540dselect name from sysobjects where xtype='u'--\u8bfb\u53d6\u6307\u5b9a\u8868\u7684\u6240\u6709\u5217\u540dselect name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='\u8868\u540d')\u83b7\u53d6\u6570\u636e\u5e93\u8868\u540d\u548c\u5b57\u6bb5sqlserver\u4e2d\u5404\u4e2a\u7cfb\u7edf\u8868\u7684\u4f5c\u7528sysaltfiles \u4e3b\u6570\u636e\u5e93 \u4fdd\u5b58\u6570\u636e\u5e93\u7684\u6587\u4ef6syscharsets \u4e3b\u6570\u636e\u5e93 \u5b57\u7b26\u96c6\u4e0e\u6392\u5e8f\u987a\u5e8fsysconfigures \u4e3b\u6570\u636e\u5e93 \u914d\u7f6e\u9009\u9879syscurconfigs \u4e3b\u6570\u636e\u5e93 \u5f53\u524d\u914d\u7f6e\u9009\u9879sysdatabases \u4e3b\u6570\u636e\u5e93 \u670d\u52a1\u5668\u4e2d\u7684\u6570\u636e\u5e93syslanguages \u4e3b\u6570\u636e\u5e93 \u8bed\u8a00syslogins \u4e3b\u6570\u636e\u5e93 \u767b\u9646\u5e10\u53f7\u4fe1\u606fsysoledbusers \u4e3b\u6570\u636e\u5e93 \u94fe\u63a5\u670d\u52a1\u5668\u767b\u9646\u4fe1\u606fsysprocesses \u4e3b\u6570\u636e\u5e93 \u8fdb\u7a0bsysremotelogins\u4e3b\u6570\u636e\u5e93 \u8fdc\u7a0b\u767b\u5f55\u5e10\u53f7syscolumns \u6bcf\u4e2a\u6570\u636e\u5e93 \u5217sysconstrains \u6bcf\u4e2a\u6570\u636e\u5e93 \u9650\u5236sysfilegroups \u6bcf\u4e2a\u6570\u636e\u5e93 \u6587\u4ef6\u7ec4sysfiles \u6bcf\u4e2a\u6570\u636e\u5e93 \u6587\u4ef6sysforeignkeys \u6bcf\u4e2a\u6570\u636e\u5e93 \u5916\u90e8\u5173\u952e\u5b57sysindexs \u6bcf\u4e2a\u6570\u636e\u5e93 \u7d22\u5f15sysmenbers \u6bcf\u4e2a\u6570\u636e\u5e93 \u89d2\u8272\u6210\u5458sysobjects \u6bcf\u4e2a\u6570\u636e\u5e93 \u6240\u6709\u6570\u636e\u5e93\u5bf9\u8c61syspermissions \u6bcf\u4e2a\u6570\u636e\u5e93 \u6743\u9650systypes \u6bcf\u4e2a\u6570\u636e\u5e93 \u7528\u6237\u5b9a\u4e49\u6570\u636e\u7c7b\u578bselect \u5217\u540d=name from syscolumns where id=object_id(N'\u8981\u67e5\u7684\u8868\u540d')
SQL : select * from information_schema.tables ORACLE: select table_name from user_tables ACCESS: select name from MSysObjects where type=1 and flags=0希望采纳谢谢~！SELECT * FROM sysobjects s WHERE s.xtype='U'
如果是mysql 就用 use databasename；show tables;如果是sqlserver 就用 select * from sysobjects where xtype='U'如果是oracle 就用 select * from tabs
绛旓細SELECT璇彞涓娇鐢ˋLL鎴朌ISTINCT閫夐」鏉ユ樉绀鸿〃涓鍚堟潯浠剁殑鎵鏈夎鎴栧垹闄ゅ叾涓噸澶嶇殑鏁版嵁琛岋紝榛樿涓篈LL銆備娇鐢―ISTINCT閫夐」 鏃讹紝瀵逛簬鎵鏈夐噸澶嶇殑鏁版嵁琛屽湪SELECT杩斿洖鐨勭粨鏋滈泦鍚堜腑鍙繚鐣欎竴琛屻5銆侀檺鍒惰繑鍥炵殑琛屾暟 浣跨敤TOP n [PERCENT]閫夐」闄愬埗杩斿洖鐨勬暟鎹鏁帮紝TOP n璇存槑杩斿洖n琛岋紝鑰孴OP n PERCENT鏃讹紝璇存槑n鏄〃绀轰竴...
绛旓細鏌ヨ鏁版嵁搴閲屾墍鏈夎〃鍚嶅拰瀛楁鍚嶇殑璇彞 SQL 鏌ヨ鎵鏈夎〃鍚嶏細SELECT NAME FROM SYSOBJECTS WHERE TYPE='U'SELECT * FROM INFORMATION_SCHEMA.TABLES 缁撴瀯鍖栨煡璇㈣瑷(Structured Query Language)绠绉癝QL锛岀粨鏋勫寲鏌ヨ璇█鏄竴绉鏁版嵁搴撴煡璇鍜岀▼搴忚璁¤瑷锛岀敤浜庡瓨鍙栨暟鎹互鍙婃煡璇佹洿鏂板拰绠＄悊鍏崇郴鏁版嵁搴撶郴缁燂紱sql 璇彞...
绛旓細(1锛 鍚姩 鐩戝惉 lsnrctl start锛涳紙2锛夎繘鍏qlplus鐣岄潰 sqlplus /nolog SQL>conn sys/jiaxiaoai@orcl as sysdba;锛3锛夊惎鍔鏁版嵁搴瀹炰緥 SQL>startup;锛4锛鏌ョ湅褰撳墠鎵鏈夌殑鏁版嵁搴 select * from v$database;鎴 select name from v$database;锛5锛夋煡鐪嬪摢浜涚敤鎴锋嫢鏈塻ysdba銆乻ysoper鏉冮檺 select * from V_...
绛旓細SQL : select * from information_schema.tables ORACLE: select table_name from user_tables ACCESS: select name from MSysObjects where type=1 and flags=0甯屾湜閲囩撼璋㈣阿~锛
绛旓細鑱斿悎鏌ヨ鏃 鏌ヨ缁撴灉鐨勫垪鏍囬涓虹涓涓煡璇㈣鍙鐨勫垪鏍囬 鍥犳 瑕佸畾涔夊垪鏍囬蹇呴』鍦ㄧ涓涓煡璇㈣鍙ヤ腑瀹氫箟 瑕佸鑱斿悎鏌ヨ缁撴灉鎺掑簭鏃 涔熷繀椤讳娇鐢ㄧ涓鏌ヨ璇彞涓殑鍒楀悕 鍒楁爣棰樻垨鑰呭垪搴忓彿 鍦ㄤ娇鐢║NION 杩愮畻绗︽椂 搴斾繚璇佹瘡涓仈鍚堟煡璇㈣鍙ョ殑閫夋嫨鍒楄〃涓湁鐩稿悓鏁伴噺鐨勮〃杈惧紡 骞朵笖姣忎釜鏌ヨ閫夋嫨琛ㄨ揪寮忓簲鍏锋湁鐩稿悓鐨鏁版嵁绫诲瀷 鎴栨槸鍙互鑷...
绛旓細鍏蜂綋璇硶锛歍ABLE table_name [ORDER BY column_name] [LIMIT number [OFFSET number]]鍏跺疄浠庤娉曚笂鐪嬶紝鍙互鎺掑簭锛屼篃鍙互杩囨护璁板綍闆嗭紝涓嶈繃姣旇緝绠鍗曪紝娌℃湁 SELECT 閭ｄ箞寮哄ぇ銆傜ず渚 1 绠鍗曠殑寤轰竴寮犲緢灏忕殑琛 y1锛岃褰曟暟涓 10 鏉°傝〃 t1锛屾彃鍏 10 鏉¤褰 mysql-(ytt/3305)->create table t1 (r1 ...
绛旓細select * from sysobjects where xtype = 'U'杩欎釜鍙互鏌ュ埌鎵鏈夌殑table 鍚嶅瓧锛屼互鍙婅缁嗕俊鎭紝浣犺瘯涓涓
绛旓細1銆鏌ヨSQL涓殑鎵鏈夎〃锛 Select TABLE_NAME FROM 鏁版嵁搴鍚嶇О.INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE' 鎵ц涔嬪悗锛屽氨鍙互鐪嬪埌鏁版嵁搴撲腑鎵鏈夊睘浜庤嚜宸卞缓鐨勮〃鐨勫悕绉 2銆佹煡璇QL涓墍鏈夎〃鍙婂垪锛 Select dbo.sysobjects.name as Table_name, dbo.syscolumns.name AS Column_name FROM dbo....
绛旓細mysql鍙敤涓鏉sql璇彞鏌ュ嚭涓涓琛ㄩ噷涓嶅悓鏉′欢瀵瑰簲鐨勬暟鎹潯鏁扮殑姝ラ濡備笅锛氭垜浠渶瑕佸噯澶囩殑鏉愭枡鍒嗗埆鏄細鐢佃剳銆sql鏌ヨ鍣ㄣ1銆侀鍏堬紝鎵撳紑sql鏌ヨ鍣紝杩炴帴涓婄浉搴旂殑鏁版嵁搴琛紝渚嬪stu2琛ㄣ2銆佺偣鍑烩滄煡璇⑩濇寜閽紝杈撳叆锛歴elect count(*) from stu2 where sex=1 and age=2 union all select count(*) from ...
绛旓細鏁版嵁鎺у埗锛氭巿鏉冨拰鍥炴敹鏁版嵁搴涓殑鏉冮檺锛岄氳繃GRANT鍜孯EVOKE璇彞瀹炵幇銆3. SQL璇彞鐨勫熀鏈粨鏋勶細SQL璇彞鐨勫熀鏈粨鏋勬牴鎹笉鍚岀殑鎿嶄綔鑰屾湁鎵涓嶅悓銆備緥濡傦紝涓涓绠鍗曠殑SELECT鏌ヨ璇彞鐨勫熀鏈粨鏋勫涓嬶細sql SELECT 鍒楀悕绉 FROM 琛ㄥ悕绉 WHERE 鏉′欢;鍏朵腑锛孲ELECT鐢ㄤ簬鎸囧畾闇瑕佹煡璇㈢殑鍒楋紝FROM鐢ㄤ簬鎸囧畾鏌ヨ鐨勮〃锛學HERE鐢ㄤ簬鎸囧畾...
