mysql 已有表创建表分区_SqlServer2008根据现有表，获取该表的分区创建脚本

1 *==============================================================

2 名称: [GetMSSQLTableScript]

3 功能: 获取customize单个表的mysql脚本4 创建：2015年3月23日5 参数：@DBName --数据库名称

6 @TBName --表名

7 @SchemeName --数据库表引用的Scheme

8 @PartitionScheme --分区Scheme

9 @PartitionField --该表使用的分区字段

10 @SQL --输出脚本

11 ==============================================================*/

12 ALTER PROCEDURE [Tuning].[GetMSSQLTableScript](13 @DBName nvarchar(64),14 @SchemeName nvarchar(32),15 @TBName nvarchar(128),16 @PartitionScheme nvarchar(32),17 @PartitionField nvarchar(32),18 @SQL nvarchar(max) OUTPUT19 )20 AS

21 Begin

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

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

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

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

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

27 declare @err_info varchar(200)28 set @TBName = UPPER(@TBName);29 if OBJECT_ID(@DBName+'.'+@SchemeName+'.'+@TBName) is null

30 BEGIN

31 set @err_info='对象:'+@DBName+'.'+@SchemeName+'.'+@TBName+'不存在!'

32 raiserror(@err_info,16,1)33 return

34 END

35

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

37 --1.添加算定义字段

38 set @table_script = 'CREATE TABLE'+@SchemeName+'.'+@TBName+'

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

41

42 --添加表中的其它字段

43 set @sql_cmd=N'

44 use'+@DBName+'

45 set @table_script=''''

46 select @table_script=@table_script+47 ''[''+t.NAME+'']''

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

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

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

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

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

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

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

55 END)56 +(case when t.isnullable=1 then''null''else''not null''end)57 +(case when COLUMNPROPERTY(t.ID, t.NAME,''ISIDENTITY'')=1 then''identity''else''''end)58 +'',''+char(13)+char(10)59 from syscolumns t join systypes p on t.xusertype = p.xusertype60 where t.ID=OBJECT_ID('''+@SchemeName+'.'+@TBName+''')61 ORDER BY t.COLID;62 '

63 EXEc sp_executesql @sql_cmd,N'@table_script varchar(max) output',@sql_cmdoutput64 set @table_script=@table_script+@sql_cmd

65 IF len(@table_script)>0

66 set @table_script=substring(@table_script,1,len(@table_script)-3)+char(13)+char(10)67 +')On'+@PartitionScheme+'('+@PartitionField+')68 '+char(13)+char(10)69 --+'GO'

70 +char(13)+char(10)+char(13)+char(10)71

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

73 set @index_script=''

74 set @sql_cmd=N'

75 use'+@DBName+'

76 declare @ct int77 declare @scheme nvarchar(32)78 declare @indid int --当前索引ID79 declare @p_indid int --前一个索引ID80 declare @partitionField nvarchar(32)81 set @partitionField='''+@PartitionField+'''

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

84 set @scheme='''+@SchemeName+'''

85 select @indid=INDID86 ,@index_script=@index_script87 +(case when @indid<>@p_indid and @ct>088 then'')''+char(13)+char(10) +char(13)+char(10)89 else''''

90 end)91 +(case when @indid<>@p_indid and UNIQ=''PRIMARY KEY''

92 then''ALTER TABLE''+TABNAME+''ADD CONSTRAINT''+name+''PRIMARY KEY''+cluster+char(13)+char(10)+''(''+char(13)+char(10)+'' ''+COLNAME+'',''+@partitionField+char(13)+char(10)93 when @indid<>@p_indid and UNIQ=''UNIQUE''

94 then''ALTER TABLE''+TABNAME+''ADD CONSTRAINT''+name+''UNIQUE''+cluster+char(13)+char(10)+''(''+char(13)+char(10)+'' ''+COLNAME+'',''+@partitionField+char(13)+char(10)95 when @indid<>@p_indid and UNIQ=''INDEX''

96 then''CREATE''+cluster+''INDEX''+name+''ON''+TABNAME+char(13)+char(10)+''(''+char(13)+char(10)+'' ''+COLNAME+char(13)+char(10)97 when @indid=@p_indid98 then'',''+COLNAME+char(13)+char(10)99 end)100 ,@ct=@ct+1101 ,@p_indid=@indid102 from103 (104 SELECT A.INDID,B.KEYNO105 ,NAME,@scheme+''.''+(SELECT NAME FROM SYSOBJECTS WHERE ID=A.ID) AS TABNAME,106 (SELECT NAME FROM SYSCOLUMNS WHERE ID=B.ID AND COLID=B.COLID) AS COLNAME,107 (CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=''UQ'') THEN''UNIQUE''

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

109 ELSE''INDEX''END) AS UNIQ,110 (CASE WHEN A.INDID=1 THEN''CLUSTERED''WHEN A.INDID>1 THEN''NONCLUSTERED''END) AS CLUSTER111 FROM SYSINDEXES A INNER JOIN SYSINDEXKEYS B ON A.INDID=B.INDID AND A.ID=B.ID112 WHERE A.ID=OBJECT_ID('''+@SchemeName+'.'+@TBName+''') and a.indid<>0 /*如果该表是一个分区表，就必须添加条件：and b.keyno<>0*/113 ) t114 ORDER BY INDID,KEYNO'

115 EXEc sp_executesql @sql_cmd,N'@index_script varchar(max) output',@sql_cmdoutput116 set @index_script=@sql_cmd

117 IF len(@index_script)>0

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

120 +char(13)+char(10)+char(13)+char(10)121 --生成默认值约束

122 set @sql_cmd='

123 use'+@DBName+'

124 declare @scheme nvarchar(32)125 declare @partitionField nvarchar(32)126 set @partitionField='''+@PartitionField+'''

127 set @scheme='''+@SchemeName+'''

128 set @default_script=''''

129 SELECT @default_script=@default_script130 +''ALTER TABLE''+@scheme+''.''+OBJECT_NAME(O.PARENT_OBJ)131 +''ADD CONSTRAINT''+O.NAME+''default''+t.text+''for''+C.NAME+char(13)+char(10)+char(13)+char(10)132 FROM SYSOBJECTS O INNER JOIN SYSCOMMENTS T ON O.ID=T.ID133 INNER JOIN SYSCOLUMNS C ON O.PARENT_OBJ=C.ID AND C.CDEFAULT=T.ID134 WHERE O.XTYPE=''D''AND O.PARENT_OBJ=OBJECT_ID('''+@SchemeName+'.'+@TBName+''')'

135 EXEc sp_executesql @sql_cmd,N'@default_script varchar(max) output',@sql_cmdoutput136 set @default_script=@sql_cmd+char(13)+char(10)137

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

139 declare @len int,@n int

140 set @len=LEN(@SQL)141 set @n=0

142 while(@len>0)143 BEGIN

144 PRINT(substring(@SQL,@n*4000+1,4000));145 set @n=@n+1

146 set @len=@len-4000;147 END

148 End