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'