1 *============================================================== 2 名稱: [ GetMSSQLTableScript ] 3 功能: 獲取customize單個表的mysql腳本 4 創(chuàng)建：2015年3月23日 5 參數(shù)： @DBName -- 數(shù)據(jù)庫名稱 6 @TBName -- 表名 7 @SchemeName -- 數(shù)據(jù)庫表引用的
1 *==============================================================
2 名稱: [GetMSSQLTableScript]
3 功能: 獲取customize單個表的mysql腳本
4 創(chuàng)建：2015年3月23日
5 參數(shù)：@DBName --數(shù)據(jù)庫名稱
6 @TBName --表名
7 @SchemeName --數(shù)據(jù)庫表引用的Scheme
8 @PartitionScheme --分區(qū)Scheme
9 @PartitionField --該表使用的分區(qū)字段
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) --動態(tài)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 ----------------------生成創(chuàng)建表腳本----------------------------
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發(fā)生變化
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 /*如果該表是一個分區(qū)表，就必須添加條件：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
該函數(shù)的原創(chuàng)作者：http://www.cnblogs.com/champaign/p/3492510.html
本人及修改了一部分內(nèi)容，讓該存儲過程更靈活點。
公司DBA支持給建議不要用sysindexkeys來查找對應的列，而是使用syscolumns來提到：
比如：select * from syscolumns where id=object_id('dx.Article');
select * from sys.index_columns where object_id=object_id('dx.Article');
聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
