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) OUTPUT 19 ) 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.xusertype 60 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_cmd output 64 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 int 77 declare @scheme nvarchar(32) 78 declare @indid int --当前索引ID 79 declare @p_indid int --前一个索引ID 80 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=INDID 86 ,@index_script=@index_script 87 +(case when @indid<>@p_indid and @ct>0 88 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_indid 98 then '' ,''+COLNAME+char(13)+char(10) 99 end) 100 ,@ct=@ct+1 101 ,@p_indid=@indid 102 from 103 ( 104 SELECT A.INDID,B.KEYNO 105 ,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 CLUSTER 111 FROM SYSINDEXES A INNER JOIN SYSINDEXKEYS B ON A.INDID=B.INDID AND A.ID=B.ID 112 WHERE A.ID=OBJECT_ID('''+@SchemeName+'.'+@TBName+''') and a.indid<>0 /*如果该表是一个分区表，就必须添加条件：and b.keyno<>0*/ 113 ) t 114 ORDER BY INDID,KEYNO' 115 EXEc sp_executesql @sql_cmd,N'@index_script varchar(max) output',@sql_cmd output 116 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_script 130 +''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.ID 133 INNER JOIN SYSCOLUMNS C ON O.PARENT_OBJ=C.ID AND C.CDEFAULT=T.ID 134 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_cmd output 136 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
该函数的原创作者：http://www.cnblogs.com/champaign/p/3492510.html
本人及修改了一部分内容，让该存储过程更灵活点。
公司DBA支持给建议不要用sysindexkeys来查找对应的列，而是使用syscolumns来提到：
比如：select * from syscolumns where id=object_id('dx.Article');
select * from sys.index_columns where object_id=object_id('dx.Article');