1 *============================================================== 2 名称: [ GetMSSQLTableScript ] 3 功能: 获取customize单个表的mysql脚本 4 创建：2015年3月23日 5 参数： @DBName -- 数据库名称 6 @TBName -- 表名 7 @SchemeName -- 数据库表引用的
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');
查看更多关于SqlServer2008根据现有表，获取该表的分区创建脚本的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did95324