1. SQL Server 2000及以上
通过系统表：官方不推荐
或者SQL-92标准：INFORMATION_SCHEMA：TABLES、COLUMNS、KEY_COLUMN_USAGE，参考：http://technet.microsoft.com/en-us/library/ms186778(v=sql.90).aspx
或者系统存储过程(Catalog Stored Procedures)：sp_pkeys、sp_columns、sp_tables，参考：
http://technet.microsoft.com/en-US/library/ms182764(v=sql.90).aspx
1.1. 表：
exec sp_tables @table_type = "'TABLE'"
1.2. 列：
1.2.1. 使用存储过程：
exec sp_columns @table_name = '<table_name>'
1.2.2. 使用系统表(含列属性说明)：
select o.name as tablename,
c.name as columnname,
ep.value as comments,
t.name as typename,
c.length,
c.prec,
c.scale,
c.isnullable
from syscolumns c
inner join sysobjects o
on c.id = o.id and o.xtype = 'U' and o.name = '<table_name>'
inner join systypes t
on c.xtype = t.xtype
left outer join
(select * from fn_listextendedproperty ('MS_Description', 'schema', 'dbo', 'table', '<table_name>', 'column', default)) ep
on ep.objname collate Chinese_PRC_CI_AS = c.name
1.3. 主键：
exec sp_pkeys @table_name = '<table_name>'
2. SQL Server 2005及以上
通过所谓的catalog view，官方推荐，参考官方文档：
http://technet.microsoft.com/en-us/library/ms345522(v=sql.90).aspx
2.1. 表：
SELECT * FROM sys.tables;
2.2. 列：
SELECT c.name AS column_name
,c.column_id
,SCHEMA_NAME(t.schema_id) AS type_schema
,t.name AS type_name
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.precision
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('<schema_name.table_name>')
ORDER BY c.column_id;
2.3. 主键：
SELECT i.name AS index_name
,ic.index_column_id
,key_ordinal
,c.name AS column_name
,TYPE_NAME(c.user_type_id)AS column_type
,is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
AND i.object_id = OBJECT_ID('<schema_name.table_name>');
3. Sybase
不兼容SQL-92标准，没有INFORMATION_SCHEMA
只能通过系统表：syscolumns、sysobjects、syskeys
或者通过系统存储过程：sp_tables、sp_columns、sp_pkeys，参考：
3.1. 表：
sp_tables
Description
Returns a list of objects that can appear in a from clause.
Syntax
sp_tables [table_name] [, table_owner]
[, table_qualifier][, table_type]
Parameters
is the name of the table. Use wildcard characters to request information about more than one table.
is the table owner. Use wildcard characters to request information about more than one table.
is the name of the database. Acceptable values are the name of the current database and NULL.
is 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'"
![clip_image001[8]](https://images0.cnblogs.com/blog/561597/201401/06163804-6965eebc3b1a4ea38ffba00919569bd3.gif "clip_image001[8]"){kind=small}
Enclose each table type with single quotation marks, and enclose the entire parameter with double quotation marks. Enter table types in uppercase.
Examples
Example 1
sp_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 system tables.
Usage
· Adaptive Server does not necessarily check the read and write permissions on table_name. Access to the table is not guaranteed, even if you can display 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 are returned.
· The results set for sp_tables is:
| Column | Datatype | Description |
| table_qualifier | varchar(30) | The database name. This field can be NULL. |
| table_owner | varchar(30) | |
| table_name | varchar(30) | NOT NULL. The table name. |
| table_type | varchar(32) | NOT NULL. One of the following: 'TABLE', 'VIEW', 'SYSTEM TABLE'. |
| remarks | varchar(254) | NULL |
Permissions
Any user can execute sp_tables.
Tables used
master.dbo.sysattributes, master.dbo.sysloginroles, master.dbo.syssrvroles, sysroles
3.2. 列：
sp_columns <table_name>
Description
Returns information about the type of data that can be stored in one or more columns.
Syntax
sp_columns table_name [, table_owner ]
[, table_qualifier] [, column_name]
Parameters
is the name of the table or view. Use wildcard characters to request information about more than one table.
is 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 a table owner, sp_columns looks for tables owned by the current user and then for tables owned by the Database Owner.
is the name of the database. This can be either the current database or NULL.
is the name of the column for which you want information. Use wildcard characters to request information about more than one column.
Examples
Example 1
Displays 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 1
pubs2 dbo publishers pub_name 12 varchar
NULL 40 NULL NULL 1 NULL 39 2
Example 2
Displays 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:
| Column | Datatype | Description |
| table_qualifier | varchar(32) | The name of the database in which the table specified for the table_name parameter is stored. |
| table_owner | varchar(32) | The table owner. If no value was specified for the table_owner parameter, this value is the current owner or the Database Owner. |
| table_name | varchar(32) | NOT NULL. |
| column_name | varchar(32) | NOT NULL. |
| data_type | smallint | Integer code for ODBC datatype. If this is a datatype that cannot be mapped into an ODBC type, it is NULL. |
| type_name | varchar(30) | String representing a datatype. The underlying DBMS presents this datatype name. |
| precision | int | Number of significant digits. |
| length | int | Length in bytes of a datatype. |
| scale | smallint | Number of digits to the right of the decimal point. |
| radix | smallint | Base for numeric datatypes. |
| nullable | smallint | The value 1 means NULL is possible; 0 means NOT NULL. |
| remarks | varchar(254) | |
| ss_data_type | smallint | An Adaptive Server datatype. |
| colid | tinyint | A column appended to the results set. |
| column_def | varchar(255) | |
| sql_data_type | smallint | |
| sql_datetime_sub | smallint | |
| char_octet_length | int | |
| ordinal_position | int | |
| is_nullable | varchar(3) |
· sp_columns reports the type_name as float, and data_type as 6 for columns defined as double precision. The Adaptive Server double precision datatype is a float implementation supports the range of values as specified in the ODBC specifications.
Permissions
Any user can execute sp_columns.
3.3. 主键：
sp_pkeys <table_name>
Description
Returns information about primary key constraints created with the create table or alter table command for a single table.
Syntax
sp_pkeys table_name [, table_owner]
[, table_qualifier]
Parameters
is the name of the table. The use of wildcard characters in pattern matching is not supported.
is 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 looks for a table owned by the current user and then for a table owned by the Database Owner.
is the name of the database that contains the table. This can be either the current database or NULL.
Usage
· The results set for sp_pkeys is:
| Column | Datatype | Description |
| table_qualifier | varchar(32) | The database name. This field can be NULL. |
| table_owner | varchar(32) | The table owner. If no value was specified for the table_owner parameter, this value is the current owner or the Database Owner. |
| table_name | varchar(32) | NOT NULL. |
| column_name | varchar(32) | NOT NULL. |
| key_seq | smallint | NOT 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 sp_primarykey.
· 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 defined on it and that this unique index is also returned insp_statistics.
Permissions
Any user can execute sp_pkeys.
4. Oracle
不兼容SQL-92标准，没有INFORMATION_SCHEMA
通过数据字典视图：all_objects、all_tab_columns、all_col_comments、all_cons_columns、all_constraints等，参考：
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_part.htm#i125539
4.1. 表：
select tab.table_name as TableName,
cmts.COMMENTS as TableDesc
from ALL_ALL_TABLES tab
left outer join ALL_TAB_COMMENTS cmts
on tab.owner = cmts.owner
and tab.table_name = cmts.table_name
where tab.owner = '<owner>'
order by TableName
4.2. 列（包含主键信息）：
select cols.table_name as TableName,
cols.column_name as ColumnName,
cols.data_type as DataType,
cols.data_length as DataLength,
cols.data_precision as DataPrecision,
cols.data_scale as DataScale,
case when pkey.constraint_type is null then 0 else 1 end IsPrimaryKey,
case when cols.nullable = 'Y' then 1 else 0 end IsNullable,
cols.DATA_DEFAULT as DefaultValue,
cmts.comments as ColumnDesc,
cols.column_id as ColumnOrder
from all_tab_columns cols
left outer join all_col_comments cmts
on cols.owner = cmts.owner
and cols.table_name = cmts.table_name
and cols.column_name = cmts.column_name
left outer join
(select cons.owner, cons.table_name, cols_cons.column_name, cons.constraint_type
from all_cons_columns cols_cons
inner join all_constraints cons
on cols_cons.owner = cons.owner
and cols_cons.table_name = cons.table_name
and cols_cons.CONSTRAINT_NAME = cons.CONSTRAINT_NAME
and cons.constraint_type='P'
and cons.owner = '<owner>'
and cons.table_name = '<tablename>') pkey
on cols.owner = pkey.owner
and cols.table_name = pkey.table_name
and cols.column_name = pkey.column_name
where cols.owner = '<owner>' and cols.table_name = '<tablename>'
order by cols.table_name, cols.column_id
240
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
