https://www.cnblogs.com/champaign/p/3492510.html

 

/****** Object: StoredProcedure [dbo].[GET_TableScript_MSSQL] Script Date: 06/15/2012 11:59:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*==============================================================

名称: GET_TableScript_MSSQL

功能: 获取customize单个表的mysql脚本

创建：2010年5月12日

参数：@DBNAME --数据库名称

@TBNAME --表名

@SQL --输出脚本

==============================================================*/

ALTER PROCEDURE [dbo].[GET_TableScript_MSSQL] (@DBNAME varchar(40),@TBNAME VARCHAR(100),@SQL VARCHAR(max) OUTPUT) AS

declare @table_script nvarchar(max) --建表的脚本

declare @index_script nvarchar(max) --索引的脚本

declare @default_script nvarchar(max) --默认值的脚本

declare @check_script nvarchar(max) --check约束的脚本

declare @sql_cmd nvarchar(max) --动态SQL命令

declare @err_info varchar(200)

set @tbname = UPPER(@tbname);

if OBJECT_ID(@DBNAME+'.dbo.'+@TBNAME) is null

BEGIN

set @err_info='对象:'+@DBNAME+'.dbo.'+@TBNAME+'不存在!'

raiserror(@err_info,16,1)

return

END

----------------------生成创建表脚本----------------------------

--1.添加算定义字段

set @table_script = 'CREATE TABLE '+@TBNAME+'

('+char(13)+char(10);

--添加表中的其它字段

set @sql_cmd=N'

use '+@DBNAME+'

set @table_script=''''

select @table_script=@table_script+

'' [''+t.NAME+''] ''

+(case when t.xusertype in (175,62,239,59,122,165,173) then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'')''

when t.xusertype in (231) and t.length=-1 then ''[ntext]''

when t.xusertype in (231) and t.length<>-1 then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'')''

when t.xusertype in (167) and t.length=-1 then ''[text]''

when t.xusertype in (167) and t.length<>-1 then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'')''

when t.xusertype in (106,108) then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'',''+convert(varchar(30),isnull(t.scale,''''))+'')''

else ''[''+p.name+'']''

END)

+(case when t.isnullable=1 then '' null'' else '' not null ''end)

+(case when COLUMNPROPERTY(t.ID, t.NAME, ''ISIDENTITY'')=1 then '' identity'' else '''' end)

+'',''+char(13)+char(10)

from syscolumns t join systypes p on t.xusertype = p.xusertype

where t.ID=OBJECT_ID('''+@TBNAME+''')

ORDER BY t.COLID;

'

EXEc sp_executesql @sql_cmd,N'@table_script varchar(max) output',@sql_cmd output

set @table_script=@table_script+@sql_cmd

IF len(@table_script)>0

set @table_script=substring(@table_script,1,len(@table_script)-3)+char(13)+char(10)

+')'+char(13)+char(10)

+'GO'+char(13)+char(10)+char(13)+char(10)

--------------------生成索引脚本---------------------------------------

set @index_script=''

set @sql_cmd=N'

use '+@DBNAME+'

declare @ct int

declare @indid int --当前索引ID

declare @p_indid int --前一个索引ID

select @indid=-1, @p_indid=0,@ct=0 --初始化，以后用@indid和@p_indid判断是否索引ID发生变化

set @index_script=''''

select @indid=INDID

,@index_script=@index_script

+(case when @indid<>@p_indid and @ct>0 then '')''+char(13)+char(10)+''go''+char(13)+char(10) else '''' end)

+(case when @indid<>@p_indid and UNIQ=''PRIMARY KEY''

then ''ALTER TABLE ''+TABNAME+'' ADD CONSTRAINT ''+name+'' PRIMARY KEY ''+cluster+char(13)+char(10)

+''(''+char(13)+char(10)

+'' ''+COLNAME+char(13)+char(10)

when @indid<>@p_indid and UNIQ=''UNIQUE''

then ''ALTER TABLE ''+TABNAME+'' ADD CONSTRAINT ''+name+'' UNIQUE ''+cluster+char(13)+char(10)

+''(''+char(13)+char(10)

+'' ''+COLNAME+char(13)+char(10)

when @indid<>@p_indid and UNIQ=''INDEX''

then ''CREATE ''+cluster+'' INDEX ''+name+'' ON ''+TABNAME+char(13)+char(10)

+''(''+char(13)+char(10)

+'' ''+COLNAME+char(13)+char(10)

when @indid=@p_indid

then '' ,''+COLNAME+char(13)+char(10)

END)

,@ct=@ct+1

,@p_indid=@indid

from

(

SELECT A.INDID,B.KEYNO

,NAME,(SELECT NAME FROM SYSOBJECTS WHERE ID=A.ID) AS TABNAME,

(SELECT NAME FROM SYSCOLUMNS WHERE ID=B.ID AND COLID=B.COLID) AS COLNAME,

(CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=''UQ'') THEN ''UNIQUE''

WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=''PK'') THEN ''PRIMARY KEY''

ELSE ''INDEX'' END) AS UNIQ,

(CASE WHEN A.INDID=1 THEN ''CLUSTERED'' WHEN A.INDID>1 THEN ''NONCLUSTERED'' END) AS CLUSTER

FROM SYSINDEXES A INNER JOIN SYSINDEXKEYS B ON A.INDID=B.INDID AND A.ID=B.ID

WHERE A.ID=OBJECT_ID('''+@TBNAME+''') and a.indid<>0

) t

ORDER BY INDID,KEYNO'

EXEc sp_executesql @sql_cmd,N'@index_script varchar(max) output',@sql_cmd output

set @index_script=@sql_cmd

IF len(@index_script)>0

set @index_script=@index_script+')'+char(13)+char(10)+'go'+char(13)+char(10)+char(13)+char(10)

--生成默认值约束

set @sql_cmd='

use '+@DBNAME+'

set @default_script=''''

SELECT @default_script=@default_script

+''ALTER TABLE ''+OBJECT_NAME(O.PARENT_OBJ)

+'' ADD CONSTRAINT ''+O.NAME+'' default ''+t.text+'' for ''+C.NAME+char(13)+char(10)

+''GO''+char(13)+char(10)

FROM SYSOBJECTS O INNER JOIN SYSCOMMENTS T ON O.ID=T.ID

INNER JOIN SYSCOLUMNS C ON O.PARENT_OBJ=C.ID AND C.CDEFAULT=T.ID

WHERE O.XTYPE=''D'' AND O.PARENT_OBJ=OBJECT_ID('''+@TBNAME+''')'

EXEc sp_executesql @sql_cmd,N'@default_script varchar(max) output',@sql_cmd output

set @default_script=@sql_cmd+char(13)+char(10)

set @SQL=@table_script+@index_script+@default_script

declare @len int,@n int

set @len=LEN(@SQL)

set @n=0

while(@len>0)

BEGIN

PRINT(substring(@SQL,@n*4000+1,4000));

set @n=@n+1

set @len=@len-4000;

END

 

 

 

 

 

 

------------------------------hbh-------------------------

 

 

 

 

/*

select *

into hbh_tmp_SM_SO_20190312001

from SM_SO

exec [GET_TableScript_MSSQL] '','SM_SO'

*/

/*==============================================================

名称: GET_TableScript_MSSQL

功能: 获取customize单个表的mysql脚本

创建：2010年5月12日

参数：@DatabaseName --数据库名称

@TableName --表名

@SQL --输出脚本

==============================================================*/

alter PROCEDURE HBH_SP_Common_GetTableScript (

@DatabaseName varchar(40) = ''

,@TableName VARCHAR(100) = ''

,@ContainIndex bit = 1

,@ContainConstraint bit = 1

-- ,@SQL VARCHAR(max) OUTPUT

)

with encryption

AS

 

declare @SQL VARCHAR(max)

declare @UseDB varchar(125) = ''

if (@DatabaseName is not null and @DatabaseName != '')

begin

set @UseDB = 'use ' + @DatabaseName

end

declare @table_script nvarchar(max) = '' --建表的脚本

declare @index_script nvarchar(max) = '' --索引的脚本

declare @default_script nvarchar(max) = '' --默认值的脚本

declare @check_script nvarchar(max) = '' --check约束的脚本

declare @sql_cmd nvarchar(max) = '' --动态SQL命令

declare @err_info varchar(200) = ''

set @TableName = UPPER(@TableName);

if OBJECT_ID(@DatabaseName+'.dbo.'+@TableName) is null

BEGIN

set @err_info='对象:'+@DatabaseName+'.dbo.'+@TableName+'不存在!'

raiserror(@err_info,16,1)

return

END

----------------------生成创建表脚本----------------------------

--1.添加算定义字段

set @table_script = 'CREATE TABLE '+@TableName+'

('+char(13)+char(10);

--添加表中的其它字段

set @sql_cmd=N'

' --use '+@DatabaseName+'

+ @UseDB + '

set @table_script=''''

select @table_script=@table_script+

'' [''+t.NAME+''] ''

+(case when t.xusertype in (175,62,239,59,122,165,173) then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'')''

when t.xusertype in (231) and t.length=-1 then ''[ntext]''

when t.xusertype in (231) and t.length<>-1 then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'')''

when t.xusertype in (167) and t.length=-1 then ''[text]''

when t.xusertype in (167) and t.length<>-1 then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'')''

when t.xusertype in (106,108) then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'',''+convert(varchar(30),isnull(t.scale,''''))+'')''

else ''[''+p.name+'']''

END)

+(case when t.isnullable=1 then '' null'' else '' not null ''end)

+(case when COLUMNPROPERTY(t.ID, t.NAME, ''ISIDENTITY'')=1 then '' identity'' else '''' end)

+'',''+char(13)+char(10)

from syscolumns t join systypes p on t.xusertype = p.xusertype

where t.ID=OBJECT_ID('''+@TableName+''')

ORDER BY t.COLID;

'

EXEc sp_executesql @sql_cmd,N'@table_script varchar(max) output',@sql_cmd output

set @table_script=@table_script+@sql_cmd

IF len(@table_script)>0

set @table_script=substring(@table_script,1,len(@table_script)-3)+char(13)+char(10)

+')'+char(13)+char(10)

+'GO'+char(13)+char(10)+char(13)+char(10)

--------------------生成索引脚本---------------------------------------

if(@ContainIndex is not null and @ContainIndex = 1)

begin

set @index_script=''

set @sql_cmd=N'

' --use '+@DatabaseName+'

+ @UseDB + '

declare @ct int

declare @indid int --当前索引ID

declare @p_indid int --前一个索引ID

select @indid=-1, @p_indid=0,@ct=0 --初始化，以后用@indid和@p_indid判断是否索引ID发生变化

set @index_script=''''

select @indid=INDID

,@index_script=@index_script

+(case when @indid<>@p_indid and @ct>0 then '')''+char(13)+char(10)+''go''+char(13)+char(10) else '''' end)

+(case when @indid<>@p_indid and UNIQ=''PRIMARY KEY''

then ''ALTER TABLE ''+TABNAME+'' ADD CONSTRAINT ''+name+'' PRIMARY KEY ''+cluster+char(13)+char(10)

+''(''+char(13)+char(10)

+'' ''+COLNAME+char(13)+char(10)

when @indid<>@p_indid and UNIQ=''UNIQUE''

then ''ALTER TABLE ''+TABNAME+'' ADD CONSTRAINT ''+name+'' UNIQUE ''+cluster+char(13)+char(10)

+''(''+char(13)+char(10)

+'' ''+COLNAME+char(13)+char(10)

when @indid<>@p_indid and UNIQ=''INDEX''

then ''CREATE ''+cluster+'' INDEX ''+name+'' ON ''+TABNAME+char(13)+char(10)

+''(''+char(13)+char(10)

+'' ''+COLNAME+char(13)+char(10)

when @indid=@p_indid

then '' ,''+COLNAME+char(13)+char(10)

END)

,@ct=@ct+1

,@p_indid=@indid

from

(

SELECT A.INDID,B.KEYNO

,NAME,(SELECT NAME FROM SYSOBJECTS WHERE ID=A.ID) AS TABNAME,

(SELECT NAME FROM SYSCOLUMNS WHERE ID=B.ID AND COLID=B.COLID) AS COLNAME,

(CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=''UQ'') THEN ''UNIQUE''

WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=''PK'') THEN ''PRIMARY KEY''

ELSE ''INDEX'' END) AS UNIQ,

(CASE WHEN A.INDID=1 THEN ''CLUSTERED'' WHEN A.INDID>1 THEN ''NONCLUSTERED'' END) AS CLUSTER

FROM SYSINDEXES A INNER JOIN SYSINDEXKEYS B ON A.INDID=B.INDID AND A.ID=B.ID

WHERE A.ID=OBJECT_ID('''+@TableName+''') and a.indid<>0

) t

ORDER BY INDID,KEYNO'

EXEc sp_executesql @sql_cmd,N'@index_script varchar(max) output',@sql_cmd output

set @index_script=@sql_cmd

IF len(@index_script)>0

set @index_script=@index_script+')'+char(13)+char(10)+'go'+char(13)+char(10)+char(13)+char(10)

end

--生成默认值约束

if(@ContainConstraint is not null and @ContainConstraint = 1)

begin

set @sql_cmd='

' --use '+@DatabaseName+'

+ @UseDB + '

set @default_script=''''

SELECT @default_script=@default_script

+''ALTER TABLE ''+OBJECT_NAME(O.PARENT_OBJ)

+'' ADD CONSTRAINT ''+O.NAME+'' default ''+t.text+'' for ''+C.NAME+char(13)+char(10)

+''GO''+char(13)+char(10)

FROM SYSOBJECTS O INNER JOIN SYSCOMMENTS T ON O.ID=T.ID

INNER JOIN SYSCOLUMNS C ON O.PARENT_OBJ=C.ID AND C.CDEFAULT=T.ID

WHERE O.XTYPE=''D'' AND O.PARENT_OBJ=OBJECT_ID('''+@TableName+''')'

EXEc sp_executesql @sql_cmd,N'@default_script varchar(max) output',@sql_cmd output

set @default_script=@sql_cmd+char(13)+char(10)

end

 

set @SQL=@table_script+@index_script+@default_script

declare @len int,@n int

set @len=LEN(@SQL)

set @n=0

while(@len>0)

BEGIN

PRINT(substring(@SQL,@n*4000+1,4000));

set @n=@n+1

set @len=@len-4000;

END

 

select @SQL