(转)SQL SERVER 生成建表脚本

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