c mysql创建表语句是_【Mysql】生成Sqlserver创建表语句

sql：

DROP VIEW

IF EXISTS `V_TableToSqlserverText`;

CREATE ALGORITHM = UNDEFINED

DEFINER = `root`@`localhost`

SQL SECURITY DEFINER

VIEW `V_TableToSqlserverText` AS (

SELECT TABLE_NAME tablename,rownum,sqltext

FROM(

SELECT TABLE_NAME,0 rownum,sqltext FROM (

SELECT c.TABLE_NAME,CONCAT('if object_id(''',c.TABLE_NAME,''',''U'') is not null

drop table [',c.TABLE_NAME,'];

CREATE TABLE ',c.TABLE_NAME,' (') sqltext

FROM information_schema.`TABLES` c

WHERE c.TABLE_SCHEMA='sevenblog'

AND c.TABLE_TYPE ='BASE TABLE'

) startSql

UNION ALL

SELECT c.TABLE_NAME,c.ORDINAL_POSITION rownum,

CONCAT('',

CASE

WHEN c.ORDINAL_POSITION >1 THEN ','

ELSE ''

END,

'[',

c.COLUMN_NAME,

'] ',

(

CASE

WHEN c.COLUMN_KEY = 'PRI' THEN 'uniqueidentifier'

WHEN c.DATA_TYPE = 'varchar' THEN CONCAT('n',c.DATA_TYPE,'(',c.CHARACTER_MAXIMUM_LENGTH,')')

ELSE c.DATA_TYPE

END

),

' ',

CASE

WHEN c.IS_NULLABLE = 'NO' THEN 'not null'

ELSE 'null'

END,

' ',

(

CASE

WHEN LENGTH(c.COLUMN_DEFAULT)>0 THEN CONCAT('default ','(',

(

CASE

WHEN c.COLUMN_DEFAULT = 'CURRENT_TIMESTAMP' THEN 'getdate()'

WHEN c.DATA_TYPE ='varchar' THEN CONCAT('''',c.COLUMN_DEFAULT,'''')

ELSE CONCAT(c.COLUMN_DEFAULT)

END

),')')

ELSE ''

END

),

CASE

WHEN c.COLUMN_KEY = 'PRI' THEN 'primary key'

ELSE ''

END

) sqltext

FROM information_schema.columns c

JOIN information_schema.`TABLES` b ON c.TABLE_NAME = b.TABLE_NAME

WHERE b.TABLE_SCHEMA='sevenblog'

AND b.TABLE_TYPE ='BASE TABLE'

UNION ALL

SELECT TABLE_NAME,100 rownum,sqltext from (

SELECT c.TABLE_NAME,CONCAT(');') sqltext

FROM information_schema.`TABLES` c

WHERE c.TABLE_SCHEMA='sevenblog'

AND c.TABLE_TYPE ='BASE TABLE'

) endStr

) tablesql

ORDER BY tablesql.TABLE_NAME,tablesql.rownum

);

结果如下

生成sql

if object_id('login_log','U') is not null

drop table [login_log];

CREATE TABLE login_log (

[Id] uniqueidentifier not null primary key

,[UserID] int not null

,[Ipaddress] nvarchar(100) not null

,[Ipproxy] nvarchar(100) not null

,[System] nvarchar(100) not null default (' ')

,[Option] int null

,[EnumDataEntityStatus] int not null default (0)

,[CreateTime] datetime null default (getdate())

,[LastUpdateTime] datetime null default (getdate())

,[CreateOperator] nvarchar(50) null

,[LastUpdateOperator] nvarchar(50) null

);

if object_id('users','U') is not null

drop table [users];

CREATE TABLE users (

[Id] uniqueidentifier not null primary key

,[Account] nvarchar(255) not null

,[Password] nvarchar(255) not null

,[Name] nvarchar(255) not null default (' ')

,[Sex] int null

,[EnumDataEntityStatus] int not null default (0)

,[BizCode] nvarchar(255) not null default (' ')

,[TypeId] int not null default (0)

,[TypeName] nvarchar(30) null

,[CreateTime] datetime null default (getdate())

,[LastUpdateTime] datetime null default (getdate())

,[CreateOperator] nvarchar(50) null

,[LastUpdateOperator] nvarchar(50) null

);