2023年7月19日发(作者：)

获取各主流数据库表结构（元数据）1.

SQL Server 2000及以上通过系统表：官⽅不推荐或者SQL-92标准：INFORMATION_SCHEMA：TABLES、COLUMNS、KEY_COLUMN_USAGE，参考：或者系统存储过程(Catalog Stored Procedures)：sp_pkeys、sp_columns、sp_tables，参考：1.1.

表：exec sp_tables @table_type = "'TABLE'"1.2.

列：1.2.1.

使⽤存储过程：exec sp_columns @table_name = ''1.2.2.

使⽤系统表(含列属性说明)：select as tablename, as columnname, as comments, as typename,,,,ablefrom syscolumns c

inner join sysobjects oon = and = 'U' and = ''inner join systypes ton = eft outer join(select * from fn_listextendedproperty ('MS_Description', 'schema', 'dbo', 'table', '', 'column', default)) epon e collate Chinese_PRC_CI_AS = 1.3.

主键：exec sp_pkeys @table_name = ''2.

SQL Server 2005及以上通过所谓的catalog view，官⽅推荐，参考官⽅⽂档：2.1.

表：SELECT * FROM ;2.2.

列：SELECT AS column_name ,_id ,SCHEMA_NAME(_id) AS type_schema , AS type_name ,_user_defined ,_assembly_type ,_length ,ion ,ROM s AS cJOIN AS t ON _type_id=_type_idWHERE _id = OBJECT_ID('')ORDER BY _id;2.3.

主键：SELECT AS index_name ,_column_id ,key_ordinal , AS column_name ,TYPE_NAME(_type_id)AS column_type ,is_identityFROM s AS iINNER JOIN _columns AS ic ON _id = _id AND _id = _idINNER JOIN s AS c ON _id = _id AND _id = _idWHERE _primary_key = 1 AND _id = OBJECT_ID('');3.

Sybase不兼容SQL-92标准，没有INFORMATION_SCHEMA只能通过系统表：syscolumns、sysobjects、syskeys或者通过系统存储过程：sp_tables、sp_columns、sp_pkeys，参考：3.1.

表：sp_tables

DescriptionReturns a list of objects that can appear in a from sp_tables [table_name] [, table_owner] [, table_qualifier][, table_type]Parameterstable_nameis the name of the table. Use wildcard characters to request information about more than one _owneris the table owner. Use wildcard characters to request information about more than one _qualifieris the name of the database. Acceptable values are the name of the current database and _typeis a list of values, separated by commas, giving information about all tables of the table type(s) specified, including the following:"'TABLE', 'SYSTEM TABLE', 'VIEW'"Enclose each table type with single quotation marks, and enclose the entire parameter with double quotation marks. Enter table types in esExample 1sp_tables @table_type = "'TABLE', 'VIEW'"This procedure returns information about all tables in the current database of the type TABLE and VIEW and excludes information about ·

Adaptive Server does not necessarily check the read and write permissions on table_name. Access to the table is not guaranteed, even if you candisplay information about it.·

The results set includes tables, views, and synonyms and aliases for gateways to DBMS products.·

If the server attribute accessible_tables is “Y” in the results set for sp_server_info, only tables that are accessible by the current user arereturned.·

The results set for sp_tables is:Columntable_qualifiertable_ownertable_nametable_typeremarksDatatypevarchar(30)varchar(30)varchar(30)varchar(32)varchar(254)DescriptionThe database name. This field can be NULL.

NOT NULL. The table NULL. One of the following: 'TABLE', 'VIEW', 'SYSTEM TABLE'.NULLPermissionsAny user can execute sp_ ributes, inroles, roles, sysroles

3.2.

列：sp_columns

DescriptionReturns information about the type of data that can be stored in one or more sp_columns table_name [, table_owner ] [, table_qualifier] [, column_name]Parameterstable_nameis the name of the table or view. Use wildcard characters to request information about more than one _owneris the owner of the table or view. Use wildcard characters to request information about tables owned by more than one user. If you do not specify atable owner, sp_columns looks for tables owned by the current user and then for tables owned by the Database _qualifieris the name of the database. This can be either the current database or _nameis the name of the column for which you want information. Use wildcard characters to request information about more than one esExample 1Displays information about all columns in the publishers table that begin with “p”:sp_columns "publishers", null, null, "p%"table_qualifier table_owner table_name column_name data_type type_name precision length scale radix nullable remarks ss_data_type colid---------------- ----------- ----------- ----------- --------- ---------- --------- ------ ----- ----- -------- ------- ------------ ----pubs2 dbo publishers pub_id 1 char NULL 4 NULL NULL 0 NULL 47 1pubs2 dbo publishers pub_name 12 varchar NULL 40 NULL NULL 1 NULL 39 2Example 2Displays information about all columns beginning with “st” in tables that begin with “s”:sp_columns "s%", null, null, "st%"Usage·

The results set for sp_columns is:Columntable_qualifiertable_ownertable_namecolumn_namedata_typetype_nameprecisionlengthscaleradixnullableremarksss_data_typecolidcolumn_defsql_data_typesql_datetime_subchar_octet_lengthordinal_positionis_nullableDatatypevarchar(32)varchar(32)varchar(32)varchar(32)smallintvarchar(30)intintsmallintsmallintsmallintvarchar(254)smallinttinyintvarchar(255)smallintsmallintintintvarchar(3)DescriptionThe name of the database in which the table specified for the table_name parameter is table owner. If no value was specified for the table_owner parameter, this value is the currentowner or the Database r code for ODBC datatype. If this is a datatype that cannot be mapped into an ODBC type, it representing a datatype. The underlying DBMS presents this datatype of significant in bytes of a of digits to the right of the decimal for numeric value 1 means NULL is possible; 0 means NOT NULL.

An Adaptive Server datatype.A column appended to the results set.

·

sp_columns reports the type_name as float, and data_type as 6 for columns defined as double precision. The Adaptive Server double precisiondatatype is a float implementation supports the range of values as specified in the ODBC sionsAny user can execute sp_columns.

3.3.

主键：sp_pkeys

DescriptionReturns information about primary key constraints created with the create table or alter table command for a single sp_pkeys table_name [, table_owner] [, table_qualifier]Parameterstable_nameis the name of the table. The use of wildcard characters in pattern matching is not _owneris the name of the table owner. The use of wildcard characters in pattern matching is not supported. If table_owner is not specified, sp_pkeys looksfor a table owned by the current user and then for a table owned by the Database _qualifieris the name of the database that contains the table. This can be either the current database or ·

The results set for sp_pkeys is:Columntable_qualifiertable_ownertable_namecolumn_namekey_seqDatatypevarchar(32)varchar(32)varchar(32)varchar(32)smallintDescriptionThe database name. This field can be table owner. If no value was specified for the table_owner parameter, this value is the current owneror the Database NULL. The sequence number of the column in a multicolumn primary key.·

Primary keys must have been declared with the create table or alter table statement, not with .·

The term primary key refers to a logical primary key for a table. Adaptive Server expects that every logical primary key has a unique index definedon it and that this unique index is also returned sionsAny user can execute sp_pkeys.

4.

Oracle不兼容SQL-92标准，没有INFORMATION_SCHEMA通过数据字典视图：all_objects、all_tab_columns、all_col_comments、all_cons_columns、all_constraints等，参考：4.1.

表：select _name as TableName,TS as TableDescfrom ALL_ALL_TABLES tableft outer join ALL_TAB_COMMENTS cmtson = nd _name = _namewhere = ''order by TableName4.2.

列（包含主键信息）：select _name as TableName,_name as ColumnName, _type as DataType,_length as DataLength,_precision as DataPrecision,_scale as DataScale,case when aint_type is null then 0 else 1 end IsPrimaryKey,case when le = 'Y' then 1 else 0 end IsNullable,_DEFAULT as DefaultValue,ts as ColumnDesc,_id as ColumnOrderfrom all_tab_columns colsleft outer join all_col_comments cmtson = nd _name = _nameand _name = _nameleft outer join(select , _name, cols__name, aint_typefrom all_cons_columns cols_consinner join all_constraints conson cols_ = nd cols__name = _nameand cols_AINT_NAME = AINT_NAMEand aint_type='P'and = ''and _name = '') pkeyon = nd _name = _nameand _name = _namewhere = '' and _name = ''order by _name, _id