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) 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');

posted @ 2015-03-23 15:30  cctext  阅读(1000)  评论(1)  编辑  收藏  举报