Obtain the database name, all table names, all field names, and column descriptions in the SQL database.

Source: Internet
Author: User

1. Get allDatabase Name: 
(1) Select name from Master. DBO. sysdatabases order by name
2. Get all table names:
(1) Select name from sysobjects where xtype = 'U' order by name
Xtype = 'U': indicates all user tables;
Xtype = 's': indicates all system tables;

(2) Select name from sysobjects where type = 'U' and sysstat = '83'

Note: Generally, only type = 'U' is required, but sometimes the system tables are mixed in (I don't know why). After adding the following sentence, these system tables can be deleted.

3. Get all field names: 
(1) Select name from syscolumns where id = object_id ('tablename ')

(2) Select syscolumns. name, policypes. name, syscolumns. isnullable, syscolumns. length from syscolumns, policypes where syscolumns. xusertype = policypes. xusertype and "syscolumns. id = object_id ('tablename ')

Note:
(A) In order to highlight some important content, several pieces of information are selected for output.
(B) The syscolumns table only contains data type numbers. To obtain the complete name, you need to find it from the categorypes table. Generally, it is better to use xusertype for user data types, there will be no one-to-many cases.
(C) syscolumns. length is the length of the physical memory, so nvarchar, varchar, and Other types are shown in the database as half of this.

4. Obtain the column names of the primary keys in the table:

Select syscolumns. name from syscolumns, sysobjects, sysindexes, sysindexkeys where syscolumns. id = object_id ('tablename') and sysobjects. xtype = 'pk' and sysobjects. parent_obj = syscolumns. ID and sysindexes. id = syscolumns. ID and sysobjects. name = sysindexes. name and sysindexkeys. id = syscolumns. ID and sysindexkeys. indid = sysindexes. indid and syscolumns. colid = sysindexkeys. colid

Note: This is found in four system tables. The relationship is complex and can be roughly expressed:
Syscolumns contains the column information and table ID in the table. The sysobjects table contains the primary key name (similar to pk_table) and Table ID. The sysindexes table contains the primary key name, table ID, and index number, sysindexkeys contains the table ID, index number, and column number. After one item is matched, the column name can be found ~

5. Get the description of the columns in the table:

Select. name, G. value From syscolumns as a left join sysproperties g on. id = G. ID and. colid = G. smallid where. id = 'table id'

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.