sqlserver | mysql 转出表结构及实体对象

 sqlserver转java实体对象:

select '/**'+char(13)+char(10)    
+'*'+isnull(CONVERT(VARCHAR(100),g.value),'') + char(13)+char(10)    
+'*/'+char(13)+char(10)    
+'@TableField("'+ a.name +'")' + char(13)+char(10) 
+'private '+    
case b.name  
WHEN 'bigint' then 'Long'       
WHEN 'int' then 'Integer'    
WHEN 'tinyint' then 'Short'    
WHEN 'smallint' then 'Short'  
when 'varchar' then 'String'    
when 'nvarchar' then 'String'    
when 'text' then 'String'   
when 'decimal' then 'BigDecimal'     
when 'bit' then 'Boolean'  
when 'float' then 'BigDecimal'     
when 'datetime' then 'LocalDateTime' end    
+' '+lower(substring(a.name,1,1))+SUBSTRING(a.name,2,len(a.name)-1)--保持字段名称的原始大小写则直接用a.name    
+';'+char(13)+char(10)  
--,b.name,a.max_length ,g.value,b.name,a.name    
from sys.columns a 
LEFT join sys.types b on a.user_type_id=b.user_type_id     
LEFT join sys.extended_properties g on a.object_id=g.major_id and a.column_id=g.minor_id    
where object_id=object_id('dbo.t_vbs_rate') ;--其中Business为表名 

SELECT TOP 1 * FROM customer.CustomerContact

 

sqlserver转表结构:

SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明= case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号= a.colorder,
字段名= a.name,
类型 = b.name,
--占用字节数 = a.length,
长度= COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数= isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空= case when a.isnullable=1 then ''else '' end,
默认值= isnull(e.text,''),
字段说明= isnull(g.[value],''),
--标识= case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键= case when exists(SELECT 1 FROM sysobjects where xtype='PK' 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 '' else '' end
FROM syscolumns a
LEFT  JOIN  systypes b ON  a.xusertype=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 
LEFT JOIN sys.extended_properties f ON d.id=f.major_id and f.minor_id=0 
where d.name IN('OrderMove') 
 -- 如果只查询指定表,加上此where条件,tablename是要查询的表名;去除where条件查询所有的表信息
ORDER BY a.id,a.colorder

mysql 转表结构:

SELECT
  table_name 表名,
  COLUMN_NAME 列名,
  COLUMN_TYPE 数据类型,
  DATA_TYPE 字段类型,
  CHARACTER_MAXIMUM_LENGTH 长度,
  IS_NULLABLE 是否为空,
  COLUMN_DEFAULT 默认值,
  COLUMN_COMMENT 备注 
FROM
 INFORMATION_SCHEMA.COLUMNS
where
-- developerclub为数据库名称,到时候只需要修改成你要导出表结构的数据库即可
table_schema ='fts-process'
ORDER BY 表名
-- AND
-- article为表名,到时候换成你要导出的表的名称
-- 如果不写的话,默认会查询出所有表中的数据,这样可能就分不清到底哪些字段是哪张表中的了,所以还是建议写上要导出的名名称
-- table_name  = 't_process'

 

posted @ 2020-12-29 11:39  Ariter  阅读(343)  评论(0)    收藏  举报