SQLServer、Oracle获取数据库、表、表里字段、类型、主键、标识、注释

SQLServer、Oracle获取数据库、表、表里字段、类型、注释

SQLServer：

--获取所有非系统数据库select name from master..sysdatabases where name not in('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs', 'ReportServer', 'ReportServerTempDB')--获取某个表的详细字段信息select c.name,t.name,c.prec,p.value from syscolumns cinner join systypes t on c.xtype= t.xtypeinner join sysobjects o on c.id= o.idinner join sys.extended_properties p  on c.id = p.major_id and c.colid = p.minor_idwhere  o.xtype='u'and t.status=0 and o.name='表名'--获取所有表信息select * from sysobjects where XType='U'--字段信息，关联表id=syscolumns.idselect * from syscolumns --注释信息，关联字段信息major_id=syscolumns.idselect * from sys.extended_properties--主键自增信息，关联id =syscolumns.idselect * from sysindexkeys select * from sysindexes --表详细信息（字段名，类型，长度，是否是主键，是否是标识，允许空，注释）select a.name N'字段名', b.name N'类型', COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', (case when (select count(*) from sysobjects where (name in  (select name from sysindexes where (id = a.id) AND (indid in  (select indid  from sysindexkeys  where (id = a.id) AND (colid in (select colid  from syscolumns where (id = a.id) AND (name = a.name))))))) AND  (xtype = 'PK'))>0 then 'PK' else '' end) N'主键', (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'identity'else '' end) N'标识', (case when a.isnullable=1 then 'true'else 'false' end) N'允许空', isnull(g.[value],'') AS N'注释' from syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 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 where d.name='表名'order by a.colorder

Oracle：

--获取当前用户所有的表SELECT *  FROM  user_tables--获取所有列，列信息SELECT * FROM user_tab_columns  --2个视图中分别存储了当前用户的表信息--user_tab_columns：表中列的详情，但是没有是否是主键的标识--user_cons_columns：约束视图。SELECT utc.COLUMN_NAME as 列名,ucc.COLUMN_NAME AS 主键, utc.DATA_TYPE as 列类型,utc.DATA_LENGTH as 类型长度,utc.NULLABLE as 是否可为空 FROM user_tab_columns utc left join user_cons_columns ucc on utc.TABLE_NAME = ucc.TABLE_NAME  where POSITION=1 AND ucc.TABLE_NAME ='你的表名' ORDER by utc.COLUMN_ID--user_col_comments：注释