SqlServer2008根據現有表，獲取該表的分區創建腳本

來源：懂視網責編：小采時間：2020-11-09 07:25:27

SqlServer2008根據現有表，獲取該表的分區創建腳本

SqlServer2008根據現有表，獲取該表的分區創建腳本:1 *============================================================== 2 名稱: [ GetMSSQLTableScript ] 3 功能: 獲取customize單個表的mysql腳本 4 創建：2015年3月23日 5 參數： @DBName -- 數據庫名稱 6 @TBName -

推薦度：

點擊下載本文 文檔為doc格式

導讀SqlServer2008根據現有表，獲取該表的分區創建腳本:1 *============================================================== 2 名稱: [ GetMSSQLTableScript ] 3 功能: 獲取customize單個表的mysql腳本 4 創建：2015年3月23日 5 參數： @DBName -- 數據庫名稱 6 @TBName -

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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com