使用SQL语句查询MySQL,SQLServer,Oracle所有数据库名和表名,字段名
<p><span><strong>MySQL中查询所有数据库名和表名</strong></span></p>
<p>
查询所有数据库</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_233560">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">show databases;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<br>
查询指定数据库中所有表名</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_41277">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">table_name </code><code class="sql keyword">from</code> <code class="sql plain">information_schema.tables </code><code class="sql keyword">where</code> <code class="sql plain">table_schema=</code><code class="sql string">'database_name'</code> <code class="sql color1">and</code> <code class="sql plain">table_type=</code><code class="sql string">'base table'</code><code class="sql plain">;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查询指定表中的所有字段名</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_25463">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">column_name </code><code class="sql keyword">from</code> <code class="sql plain">information_schema.columns </code><code class="sql keyword">where</code> <code class="sql plain">table_schema=</code><code class="sql string">'database_name'</code> <code class="sql color1">and</code> <code class="sql plain">table_name=</code><code class="sql string">'table_name'</code><code class="sql plain">;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查询指定表中的所有字段名和字段类型</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_14963">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">column_name,data_type </code><code class="sql keyword">from</code> <code class="sql plain">information_schema.columns </code><code class="sql keyword">where</code> <code class="sql plain">table_schema=</code><code class="sql string">'database_name'</code> <code class="sql color1">and</code> <code class="sql plain">table_name=</code><code class="sql string">'table_name'</code><code class="sql plain">;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<span><strong>SQLServer中查询所有数据库名和表名</strong></span></p>
<p>
查询所有数据库</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_324813">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">sysdatabases;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查询当前数据库中所有表名</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_359139">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">sysobjects </code><code class="sql keyword">where</code> <code class="sql plain">xtype=</code><code class="sql string">'U'</code><code class="sql plain">;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">xtype=</code><code class="sql string">'U'</code><code class="sql plain">:表示所有用户表，xtype=</code><code class="sql string">'S'</code><code class="sql plain">:表示所有系统表。</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查询指定表中的所有字段名</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_705899">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql keyword">name</code> <code class="sql keyword">from</code> <code class="sql plain">syscolumns </code><code class="sql keyword">where</code> <code class="sql plain">id=Object_Id(</code><code class="sql string">'table_name'</code><code class="sql plain">);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查询指定表中的所有字段名和字段类型</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_528220">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">sc.</code><code class="sql keyword">name</code><code class="sql plain">,st.</code><code class="sql keyword">name</code> <code class="sql keyword">from</code> <code class="sql plain">syscolumns sc,systypes st </code><code class="sql keyword">where</code> <code class="sql plain">sc.xtype=st.xtype </code><code class="sql color1">and</code> <code class="sql plain">sc.id </code><code class="sql color1">in</code><code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">id </code><code class="sql keyword">from</code> <code class="sql plain">sysobjects </code><code class="sql keyword">where</code> <code class="sql plain">xtype=</code><code class="sql string">'U'</code> <code class="sql color1">and</code> <code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">'table_name'</code><code class="sql plain">);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<span><strong>Oracle中查询所有数据库名和表名</strong></span></p>
<p>
查询所有数据库</p>
<p>
由于Oralce没有库名,只有表空间,所以Oracle没有提供数据库名称查询支持，只提供了表空间名称查询。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_704007">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">v$tablespace;</code><code class="sql comments">--查询表空间(需要一定权限)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查询当前数据库中所有表名</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_36885">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">user_tables;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查询指定表中的所有字段名</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_165555">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">column_name </code><code class="sql keyword">from</code> <code class="sql plain">user_tab_columns </code><code class="sql keyword">where</code> <code class="sql plain">table_name = </code><code class="sql string">'table_name'</code><code class="sql plain">;</code><code class="sql comments">--表名要全大写</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查询指定表中的所有字段名和字段类型</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_661988">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">column_name, data_type </code><code class="sql keyword">from</code> <code class="sql plain">user_tab_columns </code><code class="sql keyword">where</code> <code class="sql plain">table_name = </code><code class="sql string">'table_name'</code><code class="sql plain">;-</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
使用SQL语句查询MySQL,SQLServer,Oracle所有数据库名和表名，字段名的SQL语句，简单明了</p>
頁:
[1]