作者：mobiledu2502927877 | 来源：互联网 | 2023-01-29 04:43
SELECT表名casewhena.colorder1thend.nameelseend,索引j.name,表数据行数o.hs,表说明casewhena.colorder1then
SELECT
表名 = case when a.colorder=1 then d.name else &＃39;&＃39; end,
索引 = j.name,
表数据行数 = o.hs,
表说明 = case when a.colorder=1 then isnull(f.value,&＃39;&＃39;) else &＃39;&＃39; end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,&＃39;IsIdentity&＃39;)=1 then &＃39;√&＃39;else &＃39;&＃39; end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype=&＃39;PK&＃39; and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then &＃39;√&＃39; else &＃39;&＃39; end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,&＃39;PRECISION&＃39;),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,&＃39;Scale&＃39;),0),
允许空 = case when a.isnullable=1 then &＃39;√&＃39;else &＃39;&＃39; end,
默认值 = isnull(e.text,&＃39;&＃39;),
字段说明 = isnull(g.[value],&＃39;&＃39;)
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype=&＃39;U&＃39; and d.name<>&＃39;dtproperties&＃39;
left join
syscomments e
on
a.cdefault=e.id
left join
sys.extended_properties g
on
a.id=G.major_id and a.colid=g.minor_id
left join
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0
left join
(select a.name ,b.rows as hs
from sysobjects a inner join sysindexes b
on a.id = b.id
where a.type = &＃39;u&＃39;
and b.indid in (0,1)
) o
on (d.name = o.name)
left join sys.objects as h
on d.name=h.name
left join sys.indexes as j on h.object_id=j.object_id
where h.type<>&＃39;s&＃39;
order by
a.id,a.colorder
---------------------------------------------------以上是sqlserver------------------------------------------------------------
SELECT
d.TABLE_NAME tbName ,
COALESCE(t.COMMENTS, &＃39; &＃39;) tbDesc,
a.COLUMN_NAME columnName,
a.DATA_TYPE columnType,
a.DATA_LENGTH width,
a.DATA_SCALE precision,
d.NUM_ROWS,
decode(a.NULLABLE,&＃39;Y&＃39;,&＃39;0&＃39;,&＃39;1&＃39;) notNull,
COALESCE(m.COMMENTS, &＃39; &＃39;) comments,
decode(k.uniqueness,&＃39;UNIQUE&＃39;,&＃39;1&＃39;,&＃39;0&＃39;) uniques,
COALESCE(k.index_name, &＃39; &＃39;) indexName,
decode(k.key,&＃39;Y&＃39;,&＃39;1&＃39;,&＃39;0&＃39;) masterKey
FROM
user_tab_columns a
INNER JOIN user_tables d on a.TABLE_NAME=d.TABLE_NAME
LEFT JOIN user_tab_comments t ON t.TABLE_NAME=d.TABLE_NAME
LEFT JOIN user_col_comments m ON m.COLUMN_NAME=a.COLUMN_NAME AND m.TABLE_NAME=d.TABLE_NAME
LEFT JOIN
(
SELECT e.index_name,u.TABLE_NAME,u.COLUMN_NAME,e.uniqueness,decode(p.constraint_name,NULL,&＃39;N&＃39;,&＃39;Y&＃39;) key
from user_indexes e INNER JOIN user_ind_columns u ON e.index_name=u.index_name
LEFT JOIN ( select constraint_name from user_constraints where constraint_type=&＃39;P&＃39; ) p ON e.index_name=p.constraint_name
) k ON k.TABLE_NAME=a.TABLE_NAME and k.COLUMN_NAME=a.COLUMN_NAME
ORDER BY tbName
---------------------------------------------------以上是oracle------------------------------------------------------------
SELECT
(case ORDINAL_POSITION when 1 then TABLE_NAME ELSE &＃39;&＃39; end ) &＃39;库名&＃39;,
ORDINAL_POSITION AS &＃39;列的排列顺序&＃39;,
COLUMN_COMMENT AS &＃39;注释&＃39;,
COLUMN_NAME AS &＃39;列名&＃39;,
IS_NULLABLE AS &＃39;是否为空&＃39;,
DATA_TYPE AS &＃39;数据类型&＃39;,
CHARACTER_MAXIMUM_LENGTH AS &＃39;字符最大长度&＃39;,
COLUMN_DEFAULT AS &＃39;默认值&＃39;,
NUMERIC_PRECISION AS &＃39;数值精度(最大位数)&＃39;,
NUMERIC_SCALE AS &＃39;小数精度&＃39;,
COLUMN_TYPE AS 列类型,
COLUMN_KEY &＃39;KEY&＃39;,
EXTRA AS &＃39;额外说明&＃39;
FROM
information_schema.`COLUMNS`
WHERE
TABLE_SCHEMA = &＃39;ceshi&＃39; （ceshi替换为库名即可）
ORDER BY
TABLE_NAME,
ORDINAL_POSITION;
---------------------------------------------------以上是mysql------------------------------------------------------------
京公网安备 11010802041100号