SQL Query Summary of all databases, table names, tables fields

Source: Internet
Author: User

SQL Server
1. Query All Tables
Select [ID], [name] from [sysobjects] where [type] = ' u ' ORDER by [name]
2. Query all databases
Select [Name] from [sysdatabases] ORDER by [name]
3. Query the fields in the table
Select [Name] from [syscolumns] where [name] = ' tablexxx ' ORDER by [Colid]

Oracle

1 . Find all indexes of the table (including index name, type, constituent column):
Select T.*,i.index_type from User_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = I.tabl E_name and t.table_name = table to query

2 . lookup table's primary key (including name, constituent column):
Select cu.* from User_cons_columns cu, user_constraints au where cu.constraint_name = Au.constraint_name and Au.constraint _type = ' P ' and au.table_name = table to query

3, find the uniqueness of the table constraints (including the name, the constituent column):
Select column_name from User_cons_columns cu, user_constraints au where cu.constraint_name = Au.constraint_name and Au.con Straint_type = ' U ' and au.table_name = table to query

4 . Find the foreign key of the table (including the name, the table name of the reference table and the corresponding key name, the following is divided into multi-step query):
SELECT * from user_constraints c where c.constraint_type = ' R ' and c.table_name = table to query
Query the column name of the FOREIGN KEY constraint:
SELECT * from User_cons_columns cl where cl.constraint_name = FOREIGN key Name
Query the column name of the key referencing the table:
SELECT * from User_cons_columns cl where cl.constraint_name = foreign key reference table key name

5 . Querying all columns of the table and their properties
Select t.*,c.comments from User_tab_columns t,user_col_comments c where t.table_name = C.table_name and T.column_name = c. column_name and t.table_name = table to query

SQL Query Summary of all databases, table names, tables fields

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.