/*
功能描述:
1.批量产生修改表字段脚本
2.@filter设置过滤类型长度相同的修改
3. 考虑因素( default_constraint/check_constraint/index/full_index/statistics)
使用方法:
@filter 设置过滤， @s 输入需要修改的cloumn 
潜在问题:数据类型考虑不完全，有可能部分数据类型过滤功能无法使用,这只是根据自己工作需要编写的部分功能

*/

USE masterGOSET NOCOUNT ONDECLARE @s varchar(max)DECLARE @sql varchar(max)DECLARE @x xmldeclare @filter int;set @filter=0  -- 1:filter ChangeTotype=currenttype   0 do nothing/*****INPUT*******************dbname;tbname;columnname;ChangeTotype*//*mydb;fullindex;cc;varchar(30)test;fullindex;cb;varchar(30)test;fullindex;cc;varchar(30)*/SET @s = ‘mydb;fullindex;cc;varchar(30)test;fullindex;cb;varchar(30)test;fullindex;cc;varchar(30)‘IF OBJECT_ID(‘tempdb.dbo.#lynn_tbinfo‘) IS NOT NULL    DROP TABLE #lynn_tbinfoCREATE TABLE #lynn_tbinfo(    ID int identity(1,1) PRIMARY KEY,    DBName varchar(500),    TbName varchar(500),    ColName varchar(500),    ChangeTotype varchar(500),    Size_MB decimal(20,2),    Rows bigint,    currenttype  varchar(500),    is_nullable int,    ischaged int,    alterscript nvarchar(max) )DECLARE @tt table(    COL varchar(max))SELECT    @sql = ‘‘,    @s = N‘<root><col>‘ + REPLACE(@s,CHAR(10),N‘</col><col>‘) + N‘</col></root>‘,            @x = CONVERT(xml,@s)INSERT @tt    SELECT                REPLACE(REPLACE(REPLACE(REPLACE(col,CHAR(10),N‘‘),CHAR(10),N‘‘),CHAR(9),N‘‘),‘;‘,‘.‘) AS COL    FROM    (                SELECT                            REPLACE( REPLACE(RTRIM(LTRIM(T.c.value(N‘(text())[1]‘,N‘nvarchar(300)‘))),CHAR(10),N‘‘),‘ ‘,‘‘) AS col                FROM @x.nodes(‘/root/col‘) T(c)    )A    WHERE A.col IS NOT NULL   --insert into #lynn_tbinfo(DBName,TbName,ColName,ChangeTotype)SELECT PARSENAME(COL,4),PARSENAME(COL,3),PARSENAME(COL,2),PARSENAME(COL,1) FROM @tt --SELECT * FROM #lynn_tbinfo--***get current size & rows & column type    begin--IF OBJECT_ID(‘tempdb.dbo.#getsize‘) IS NOT NULL    DROP TABLE #getsizecreate table #getsize(    dbname varchar(500)    ,tbname varchar(500)    ,Size_MB decimal(20,2)    ,rows bigint)if OBJECT_ID(‘tempdb..#currenttype‘,‘U‘) is not null    drop table #currenttypecreate table #currenttype(    dbname varchar(200),    tbname varchar(200),    columnname varchar(200),    columntype varchar(200),    columnlength varchar(10),    precision int,    scale int,    currenttype  varchar(20),    is_nullable int)IF OBJECT_ID(‘tempdb.dbo.#indexinfo‘) IS NOT NULL    DROP TABLE #indexinfocreate table #indexinfo(    id int identity(1,1) primary key,    dbname varchar(500),    index_ix bigint,    tbname varchar(500),    colname    varchar(500),    ix_name    varchar(500),    type_desc  varchar(50),        is_unique    int,    is_primary_key    int,    fill_factor    int,    has_filter    int,    is_disabled    int,    filter_definition varchar(2000),        index_column_id    int,    is_descending_key int,    is_included_column int,    dropscript nvarchar(max),    createscript nvarchar(max))IF OBJECT_ID(‘tempdb.dbo.#dfinfo‘) IS NOT NULL    DROP TABLE #dfinfocreate table #dfinfo(    id int identity(1,1) primary key,    dbname varchar(500),    tbname varchar(500),    dfname  varchar(500),    definition nvarchar(max),    colname    varchar(500),    dropscript nvarchar(max),    createscript nvarchar(max))IF OBJECT_ID(‘tempdb.dbo.#stinfo‘) IS NOT NULL    DROP TABLE #stinfocreate table #stinfo(    id int identity(1,1) primary key,    dbname varchar(500),    tbname varchar(500),    stname  varchar(500),    has_filter int,    filter_definition nvarchar(max),    colname    varchar(500),    stats_column_id int,    dropscript nvarchar(max),    createscript nvarchar(max))IF OBJECT_ID(‘tempdb.dbo.#ckinfo‘) IS NOT NULL    DROP TABLE #ckinfocreate table #ckinfo(    id int identity(1,1) primary key,    dbname varchar(500),    tbname varchar(500),    ckname  varchar(500),    colname    varchar(500),    definition nvarchar(max),    dropscript nvarchar(max),    createscript nvarchar(max))IF OBJECT_ID(‘tempdb.dbo.#fullindexinfo‘) IS NOT NULL    DROP TABLE #fullindexinfocreate table #fullindexinfo(    id int identity(1,1) primary key,    dbname varchar(500),    tbname varchar(500),    colname  varchar(500),    ix_name varchar(500),    catelogname    varchar(500),    is_enabled int,    dropscript nvarchar(max),    createscript nvarchar(max))declare @sizesql1 nvarchar(max),@sizesql2 nvarchar(max)        ,@db nvarchar(500),@tb nvarchar(500);declare @typesqll Nvarchar(max),@typesql2 Nvarchar(max),@colname nvarchar(500);SELECT @sizesql1=N‘use {dbname}insert into #getsize(dbname,tbname,Size_MB,rows)select ‘‘{dbname}‘‘,‘‘{tbname}‘‘, cast(SUM (reserved_page_count)*8.0/1024 as decimal(20,2)),SUM (    CASE  WHEN (index_id < 2) THEN row_count  ELSE 0  END   )  from sys.dm_db_partition_stats  where object_id = object_id(‘‘{tbname}‘‘)‘select @typesqll=N‘use {dbname}insert into #currenttype(dbname,tbname,columnname,columntype,columnlength,precision,scale,is_nullable)select DB_NAME(),OBJECT_NAME(co.object_id) as tbname, co.name as colname, ty.name typename,  length=case               when co.max_length=-1 then ‘‘max‘‘              when ty.name like ‘‘N%‘‘   then cast(co.max_length/2 as varchar(10))       else cast(co.max_length as varchar(10)) end,co.precision,co.scale,  co.is_nullablefrom sys.columns as co with(nolock)inner join sys.types as ty with(nolock)  on ty.user_type_id=co.user_type_idinner join sys.tables as t   on t.object_id=   co.object_id  where OBJECT_NAME(co.object_id)= ‘‘{tbname}‘‘ and co.name= ‘‘{colname}‘‘   ‘declare @getinfo1 nvarchar(max),@getinfo2 nvarchar(max);select @getinfo1=N‘use [{db}]--index;with ixinfo as(select  IX.index_id ,object_name(ix.object_id) tbname ,co.name colname,ix.name ixname,ix.type_desc ,ix.is_unique,ix.is_primary_key,ix.fill_factor,ix.has_filter,ix.is_disabled,ix.filter_definition,ixc.index_column_id,ixc.is_descending_key,ixc.is_included_columnfrom sys.indexes as ix with(nolock)  inner join sys.index_columns as ixc with(nolock)        on ixc.object_id=ix.object_id  AND IX.index_id=IXC.index_id inner join sys.columns as co with(nolock)    on co.object_id=ix.object_id  and co.column_id=ixc.column_id),ixinfoa as(select     distinct ixa.index_id,ixa.ixname,ixa.tbname from ixinfo as ixainner join #lynn_tbinfo as t    on t.DBName=DB_NAME() and t.TbName=ixa.tbname and t.ColName=ixa.colname)insert into #indexinfoselect  DB_NAME(),ixb.* ,NULL,NULLfrom ixinfo as ixbinner join ixinfoa ixc        on ixc.index_id=ixb.index_id and ixc.tbname=ixb.tbnameorder by ixb.tbname, ixb.index_id;--dfinsert into #dfinfoselect DB_NAME(),OBJECT_NAME(cof.object_id) tbname,df.name dfname, df.definition,cof.name colname,NULL,NULLFROM sys.default_constraints as df WITH(NOLOCK)  inner join sys.columns as cof  WITH(NOLOCK) on cof.object_id=df.parent_object_id  and cof.column_id=df.parent_column_idinner join #lynn_tbinfo as ldf    on ldf.DBName=DB_NAME() and object_id(ldf.TbName)=cof.object_id and ldf.ColName=cof.name;--stat;with stat as(SELECT     object_name(cs.object_id) tbname,st.name stname,st.has_filter,st.filter_definition,    cs.name colname,stc.stats_column_idfrom sys.stats as st with(nolock)  inner join sys.stats_columns as stc with(nolock)        on st.object_id=stc.object_id  and  st.stats_id=stc.stats_id  inner join sys.columns cs  with(nolock)         on cs.object_id=st.object_id  and cs.column_id=stc.column_id  and st.user_created=1),stat2as(  select distinct s1.tbname, s1.stname  from  stat as s1    inner join #lynn_tbinfo as ls        on ls.DBName=DB_NAME() and ls.TbName=s1.tbname and ls.ColName=s1.colname)insert into #stinfoselect DB_NAME(),s2.*,NULL,NULLfrom stat as s2inner join stat2 s3    on s3.tbname=s2.tbname and s3.stname=s2.stname ;   --checkinsert into #ckinfoSELECT DB_NAME(),OBJECT_NAME(cc.object_id) tbname,ck.name ckname,cc.name colname,ck.definition,NULL,NULLFROM  SYS.check_constraints as ck with(nolock)inner join sys.columns as cc with(nolock)    on cc.object_id=ck.parent_object_id and cc.column_id=ck.parent_column_id inner join #lynn_tbinfo  lc    on lc.dbname=DB_NAME() and object_id(lc.tbname)=cc.object_id  and lc.colname=cc.name;insert into #fullindexinfoselect DB_NAME(),OBJECT_NAME(fi.object_id) tbname,fcl.name colname,fix.name ix_name,fc.name catelogname,fi.is_enabled,NULL,NULLfrom sys.fulltext_indexes  as fi with(nolock)inner join  sys.fulltext_index_columns as fic with(nolock)    on fic.object_id=fi.object_idinner join sys.fulltext_index_catalog_usages as ficu  with(nolock)    on ficu.object_id=fi.object_idinner join sys.fulltext_catalogs as fc with(nolock)    on fc.fulltext_catalog_id=ficu.fulltext_catalog_idinner join sys.indexes as fix with(nolock)    on fix.object_id=fi.object_id and fix.index_id= fi.unique_index_idinner join sys.columns as fcl  with(nolock)    on fcl.object_id=fi.object_id and fcl.column_id= fic.column_idwhere exists(select top(1) 1 from #lynn_tbinfo  flcc  with(nolock)    where flcc.DBName=DB_NAME() and object_id(flcc.TbName)=fi.object_id and flcc.ColName=fcl.name)or exists(select top(1) 1 from #indexinfo flix  with(nolock)    where flix.DBName=DB_NAME() and flix.tbname=OBJECT_NAME(fi.object_id) and flix.ix_name=fix.name);--inner join #lynn_tbinfo  flcc  with(nolock)--    on flcc.DBName=DB_NAME() and object_id(flcc.TbName)=fi.object_id and flcc.ColName=fcl.name;‘ DECLARE lynncursor CURSOR  LOCAL STATIC FORWARD_ONLY READ_ONLY     FOR      SELECT distinct  DBName from #lynn_tbinfo with(nolock)    OPEN lynncursor          FETCH NEXT FROM lynncursor INTO @db    WHILE @@fetch_status = 0            BEGIN                   select @getinfo2= REPLACE(@getinfo1,‘{db}‘,@db)                EXECUTE sp_executesql @getinfo2;           DECLARE lynncursor2 CURSOR  LOCAL STATIC FORWARD_ONLY READ_ONLY         FOR              SELECT distinct TbName from #lynn_tbinfo with(nolock) where dbname=@db        OPEN lynncursor2        FETCH NEXT FROM lynncursor2 INTO  @tb         WHILE @@fetch_status = 0             BEGIN                select @sizesql2=‘‘                select @sizesql2=replace( replace (@sizesql1,‘{dbname}‘,@db),‘{tbname}‘,@tb)                exec sp_executesql @sizesql2                    DECLARE lynncursor3 CURSOR  LOCAL STATIC FORWARD_ONLY READ_ONLY                 FOR                  SELECT distinct  colname from #lynn_tbinfo with(nolock) where dbname=    @db and tbname=@tb                OPEN lynncursor3                      FETCH NEXT FROM lynncursor3 INTO @colname                WHILE @@fetch_status = 0                       BEGIN                                        select @typesql2= REPLACE(REPLACE( REPLACE(@typesqll,‘{dbname}‘,@db), ‘{tbname}‘,@tb ),‘{colname}‘,@colname)                        EXECUTE sp_executesql @typesql2;                                                FETCH NEXT FROM lynncursor3 INTO @colname                    END                CLOSE lynncursor3                      DEALLOCATE lynncursor3                FETCH NEXT FROM lynncursor2 INTO  @tb             END        CLOSE lynncursor2              DEALLOCATE lynncursor2        FETCH NEXT FROM lynncursor INTO  @db    END      CLOSE lynncursor          DEALLOCATE lynncursorupdate #currenttypeset currenttype=case        when columntype IN(‘varchar‘,‘nvarchar‘,‘char‘,‘nchar‘,‘binary‘) then  columntype+‘(‘+CAST(columnlength AS varchar(10))+‘)‘    when columntype IN (‘decimal‘) then columntype+‘(‘+CAST(precision AS varchar(10))+‘,‘+CAST(scale AS varchar(10))+‘)‘ else columntypeend  --update currenttype into #lynn_tbinfoupdate  iiset  ii.currenttype=c.currenttype    ,ii.is_nullable=c.is_nullable    ,ii.ischaged= case when ii.ChangeTotype=c.currenttype then 1 else 0 endfrom #lynn_tbinfo as iiinner join #currenttype as c    on ii.dbname=c.dbname  and ii.tbname=c.tbname and ii.ColName=c.columnname  --update size & rows into #lynn_tbinfoupdate  iset  i.Size_MB=g.Size_MB    ,i.Rows=g.rowsfrom #lynn_tbinfo as iinner join #getsize as g    on i.dbname=g.dbname  and i.tbname=g.tbnameIF OBJECT_ID(‘tempdb.dbo.#getsize‘) IS NOT NULL    DROP TABLE #getsizeIF OBJECT_ID(‘tempdb.dbo.#currenttype‘) IS NOT NULL    DROP TABLE #currenttypeIF OBJECT_ID(‘tempdb.dbo.#lynn_result‘) IS NOT NULL    DROP TABLE #lynn_resultCREATE TABLE #lynn_result(    ID int identity(1,1) PRIMARY KEY,    DBName varchar(500),    TbName varchar(500),    Size_MB decimal(20,2),    Rows int,    inputcolumns int,    altercolumns int,    dropscript nvarchar(max),    alterscript nvarchar(max),    addscript nvarchar(max))insert into #lynn_result(DBName,TbName,inputcolumns)select  DBName,TbName,count(1) as colcount from #lynn_tbinfogroup by DBName,TbName--select * from #lynn_resultif @filter=1    delete #lynn_tbinfo where ischaged=1;with co (DBName,TbName,colcount)as(    select  DBName,TbName,count(1) as colcount from #lynn_tbinfo    group by DBName,TbName)update  rset altercolumns= isnull(co.colcount,0)from   #lynn_result as rleft join coon co.dbname=r.dbname and co.tbname=r.tbnameupdate #lynn_tbinfoset alterscript=‘ALTER TABLE DBO.[‘+TbName+‘] ALTER COLUMN [‘+colname+‘] ‘+ changetotype+‘ ‘+ REPLACE(REPLACE(is_nullable,0,‘NOT‘),1,‘‘) + ‘ NULL‘update #indexinfoset dropscript=    case when is_primary_key=1    then ‘if exists( select top(1) 1 from sys.indexes  with(nolock) where object_id=object_id(‘‘‘+tbname+‘‘‘) and name=‘‘‘+ix_name+‘‘‘)‘        +char(10)+‘    ALTER TABLE DBO.[‘+tbname+‘] DROP CONSTRAINT  [‘+ix_name+‘]‘    else ‘if exists( select top(1) 1 from sys.indexes  with(nolock) where object_id=object_id(‘‘‘+tbname+‘‘‘) and name=‘‘‘+ix_name+‘‘‘)‘        +char(10)+‘    DROP INDEX [‘+ix_name+‘] ON dbo.[‘+tbname+‘]‘    end    ,createsCript= ‘if not exists( select top(1) 1 from sys.indexes  with(nolock) where object_id=object_id(‘‘‘+tbname+‘‘‘) and name=‘‘‘+ix_name+‘‘‘)‘        +char(10)+    case when is_primary_key=1    then ‘    ALTER TABLE dbo.[‘+tbname+‘] ADD CONSTRAINT [‘+ix_name+‘] PRIMARY KEY ‘+ type_desc    else ‘    CREATE ‘+case when is_unique=1 then ‘ UNIQUE ‘ ELSE ‘‘ END    +type_desc+‘ INDEX [‘+ix_name+‘] ON DBO.[‘+tbname+‘]‘    end; update #stinfoset dropscript=‘if exists( select top(1) 1 from sys.stats with(nolock) where name=‘‘‘+stname+‘‘‘ and object_id=object_id(‘‘‘+tbname+‘‘‘) )‘            +char(10)+‘    DROP STATISTICS dbo.[‘+tbname+‘].[‘+stname+‘]‘    ,createsCript=‘if not exists( select top(1) 1 from sys.stats with(nolock) where name=‘‘‘+stname+‘‘‘ and object_id=object_id(‘‘‘+tbname+‘‘‘) )‘            +char(10)+‘    CREATE STATISTICS [‘+stname+‘] ON DBO.[‘+tbname+‘]‘update #dfinfo    set dropscript=‘if exists( select top(1) 1 from sys.default_constraints  with(nolock) where parent_object_id=object_id(‘‘‘+tbname+‘‘‘) and name=‘‘‘+dfname+‘‘‘ )‘        +char(10)+‘    ALTER TABLE DBO.[‘+tbname+‘] DROP CONSTRAINT  [‘+dfname+‘]‘    ,createsCript=‘if not exists( select top(1) 1 from sys.default_constraints  with(nolock) where parent_object_id=object_id(‘‘‘+tbname+‘‘‘) and name=‘‘‘+dfname+‘‘‘ )‘        +char(10)+‘    ALTER TABLE DBO.[‘+tbname+‘] ADD CONSTRAINT  [‘+dfname+‘] DEFAULT ‘+[definition]+‘ FOR [‘+colname+‘]‘;update #ckinfo    set dropscript=‘if exists( select top(1) 1 from sys.check_constraints  with(nolock) where parent_object_id=object_id(‘‘‘+tbname+‘‘‘) and name=‘‘‘+ckname+‘‘‘ )‘            +char(10)+‘    ALTER TABLE DBO.[‘+tbname+‘] DROP CONSTRAINT  [‘+ckname+‘]‘        ,createsCript=‘if not exists( select top(1) 1 from sys.check_constraints  with(nolock) where parent_object_id=object_id(‘‘‘+tbname+‘‘‘) and name=‘‘‘+ckname+‘‘‘ )‘            +char(10)+‘    ALTER TABLE DBO.[‘+tbname+‘] ADD CONSTRAINT [‘+ckname+‘] CHECK ‘+ [definition]    ;UPDATE #fullindexinfo    SET dropscript=‘DROP FULLTEXT INDEX ON DBO.[‘+tbname+‘]‘        ,createsCript=‘CREATE FULLTEXT INDEX ON DBO.[‘+tbname+‘]([‘+ colname + ‘]) KEY INDEX ‘+ix_name;--SELECT * FROM #lynn_tbinfo--select ‘index‘,* from #indexinfo order  by dbname,tbname,ix_name--select ‘df‘,* from #dfinfo--select ‘sta‘,* from #stinfo--select ‘check‘,* from #ckinfo--select ‘fullindex‘,* from #fullindexinfoDECLARE @db4 varchar(500),@tb4 varchar(500),@ixname varchar(500),@stname varchar(500);DECLARE @list varchar(2000),@includelist varchar(2000),@filtersql varchar(2000),@withsql varchar(2000)     ,@totalsql VARCHAR(MAX);DECLARE lynncursor4 CURSOR   LOCAL  STATIC  FORWARD_ONLY  READ_ONLY FOR      select distinct dbname,tbname from  #lynn_tbinfo with(nolock)OPEN lynncursor4      FETCH NEXT FROM lynncursor4 INTO @db4, @tb4WHILE @@fetch_status = 0        BEGIN        --ix    DECLARE lynncursor5 CURSOR   LOCAL  STATIC  FORWARD_ONLY  READ_ONLY     FOR       select distinct ix_name  from #indexinfo with(nolock) where dbname=@db4 and tbname=@tb4     OPEN lynncursor5          FETCH NEXT FROM lynncursor5 INTO @ixname    WHILE @@fetch_status = 0              BEGIN              select @list=‘‘,@includelist=‘‘,@filtersql=‘‘,@withsql=‘‘,@totalsql=‘‘;            select @list=@list+‘,‘+‘[‘+colname+‘]‘+ case  is_descending_key when 1 then ‘ DESC‘  ELSE ‘ ASC‘ END            from #indexinfo where dbname=@db4 and tbname=@tb4 and  ix_name =@ixname and is_included_column=0            order by index_column_id            select @includelist=@includelist+‘,‘+‘[‘+colname+‘]‘            from #indexinfo where dbname=@db4 and tbname=@tb4 and  ix_name =@ixname and is_included_column=1             order by index_column_id                        select  @list=‘(‘+SUBSTRING(@list,2,5000)+‘)‘            select  @includelist=‘(‘+SUBSTRING(@includelist,2,5000)+‘)‘            IF @includelist=‘()‘                SELECT @includelist=‘‘            select top(1) @filtersql=filter_definition  from #indexinfo             where dbname=@db4 and tbname=@tb4 and  ix_name =@ixname and filter_definition is not null            select top(1) @withsql= ‘WITH (FILLFACTOR=‘+CAST(fill_factor AS VARCHAR(3))+‘)‘ from #indexinfo             where dbname=@db4 and tbname=@tb4 and  ix_name =@ixname  AND fill_factor<>0             --select @ixname,@ixlist,@includelist,@wheresql,@withsql             select @totalsql=CHAR(10)+‘    ‘+@list+case when @includelist<>‘‘ then  ‘ INCLUDE ‘+ @includelist  ELSE ‘‘ END                        +case when @filtersql<>‘‘ then  ‘ WHERE ‘+ @filtersql  ELSE ‘‘ END                        +@withsql                                            --SELECT @ixsql                UPDATE #indexinfo            SET createsCript=createsCript+@totalsql            WHERE dbname=@db4 and tbname=@tb4 and  ix_name =@ixname                            FETCH NEXT FROM lynncursor5 INTO @ixname        END        CLOSE lynncursor5             DEALLOCATE lynncursor5        DECLARE lynncursor6 CURSOR   LOCAL  STATIC  FORWARD_ONLY  READ_ONLY         FOR          select distinct stname  from #stinfo with(nolock) where dbname=@db4 and tbname=@tb4         OPEN lynncursor6              FETCH NEXT FROM lynncursor6 INTO @stname        WHILE @@fetch_status = 0              BEGIN              SELECT @list=‘‘,@filtersql=‘‘,@totalsql=‘‘            SELECT @list=@list+‘,‘+‘[‘+colname+‘]‘ from  #stinfo where dbname=@db4 and tbname=@tb4  and stname=@stname order by stats_column_id            SELECT  @list=‘(‘+SUBSTRING(@list,2,5000)+‘)‘            --select @list            select top(1) @filtersql=filter_definition  from  #stinfo where  dbname=@db4 and tbname=@tb4  and stname=@stname  and has_filter=1            --SELECT @filtersql            SELECT  @totalsql=‘    ‘+@list+case when @filtersql<>‘‘ then  ‘ WHERE ‘+ @filtersql  ELSE ‘‘ END            --SELECT @totalsql            UPDATE #stinfo            SET createsCript=createsCript+@totalsql            WHERE dbname=@db4 and tbname=@tb4 and  stname =@stname        FETCH NEXT FROM lynncursor6 INTO @stname        END         CLOSE lynncursor6            DEALLOCATE lynncursor6    FETCH NEXT FROM lynncursor4 INTO @db4, @tb4  END  CLOSE lynncursor4      DEALLOCATE lynncursor4DECLARE @fdb varchar(500),@ftb varchar(500), @fSize_MB decimal(20,2), @fRows bigint ;DECLARE @dropsql nvarchar(max),@altersql nvarchar(max),@addsql nvarchar(max);DECLARE @tempcounter varchar(20);DECLARE lynn CURSOR  LOCAL  STATIC  FORWARD_ONLY  READ_ONLY FOR      SELECT DBName,TbName,Size_MB,[Rows] from #lynn_tbinfo with(nolock)     group by  DBName,TbName,Size_MB,[Rows]    order by DBName,TbName,Size_MB,[Rows]OPEN lynn      FETCH NEXT FROM lynn INTO @fdb ,@ftb,@fSize_MB,@fRowsWHILE @@fetch_status = 0        BEGIN      --Put your code here to use @your_variable    select @altersql=‘‘,@dropsql=‘‘,@addsql=‘‘;    --drop full index    if exists (select top(1) 1 from   #fullindexinfo  where dbname=@fdb and tbname=@ftb )    begin         select @dropsql=@dropsql+‘--***** drop full index *****‘+char(10)        select @dropsql=@dropsql+dropscript+char(10) from #fullindexinfo where dbname=@fdb and tbname=@ftb;    end     --drop index    if exists(select top(1) 1 from  #indexinfo  where dbname=@fdb and tbname=@ftb )    begin         select @dropsql=@dropsql+‘--***** drop index *****‘+char(10)        ;with  lynnix        as( select distinct ix_name,type_desc,dropscript from #indexinfo  where dbname=@fdb and tbname=@ftb)        select @dropsql=@dropsql+dropscript+char(10)        from lynnix order by type_desc desc    end    --drop sta    if exists (select top(1) 1 from   #stinfo  where dbname=@fdb and tbname=@ftb )    begin         select @dropsql=@dropsql+‘--***** drop state *****‘+char(10)        ;with  lynnst        as(select distinct  dropscript from   #stinfo  where dbname=@fdb and tbname=@ftb )        select @dropsql=@dropsql+dropscript+char(10) from lynnst    end    --drop check    if exists (select top(1) 1 from   #ckinfo  where dbname=@fdb and tbname=@ftb )    begin         select @dropsql=@dropsql+‘--***** drop check *****‘+char(10)        select @dropsql=@dropsql+dropscript+char(10) from #ckinfo where dbname=@fdb and tbname=@ftb    end    --drop default    if exists (select top(1) 1 from   #dfinfo  where dbname=@fdb and tbname=@ftb )    begin         select @dropsql=@dropsql+‘--***** drop default *****‘+char(10)        select @dropsql=@dropsql+dropscript+char(10) from #dfinfo where dbname=@fdb and tbname=@ftb;    end     --drop full index    --if exists (select top(1) 1 from   #fullindexinfo  where dbname=@fdb and tbname=@ftb )    --begin     --    select @dropsql=@dropsql+char(10)+‘--***** drop full index *****‘    --    select @dropsql=@dropsql+char(10)+dropscript from #fullindexinfo where dbname=@fdb and tbname=@ftb;    --end     --alter column    select @altersql=@altersql+‘--***** alter column *****‘+char(10)    select @tempcounter =cast( count(1) as varchar(20)) from #lynn_tbinfo  where dbname=@fdb and tbname=@ftb         ;with lynnalter    as(        select             tbname,colname,alterscript,ROW_NUMBER() OVER(ORDER BY colname ) as id,@tempcounter as coun         from #lynn_tbinfo         where dbname=@fdb and tbname=@ftb                 ),lynnalter1    as(    select *,‘t‘+CAST(id as varchar(20))+‘a‘ as t1, ‘t‘+CAST(id as varchar(20))+‘b‘ as t2  from lynnalter    )    select @altersql = @altersql+    ‘declare @‘+t1+‘ datetime=getdate(),@‘+t2+‘ varchar(20)‘    +char(10)+alterscript+char(10)    +‘select @‘+t2+‘=cast( cast( DATEDIFF(second ,@‘+t1+‘,getdate())/60.0  as decimal(12,1) )  as varchar(20))‘    +char(10)+‘RAISERROR(‘‘Col[‘+cast(coun as varchar(20))+‘/‘+cast(id as varchar(20))+‘]:    [‘+[colname] +‘] is done.        %s minutes‘‘,10,1,@‘+t2+‘) WITH NOWAIT  ‘+char(10)+‘GO‘+char(10)    from lynnalter1    --add df    if exists (select top(1) 1 from   #dfinfo  where dbname=@fdb and tbname=@ftb )    begin         select @addsql=@addsql+‘--***** add default *****‘+char(10)        select @addsql=@addsql+createscript+char(10) from #dfinfo where dbname=@fdb and tbname=@ftb;    end     --add ck    if exists (select top(1) 1 from   #ckinfo  where dbname=@fdb and tbname=@ftb )    begin         select @addsql=@addsql+‘--***** add check *****‘+char(10)        select @addsql=@addsql+createscript+char(10) from #ckinfo where dbname=@fdb and tbname=@ftb    end    --add index    if exists(select top(1) 1 from  #indexinfo  where dbname=@fdb and tbname=@ftb )    begin         select @addsql=@addsql+‘--***** add index *****‘+char(10)        --;with  lynnix        --as( select distinct ix_name,type_desc,createscript from #indexinfo  where dbname=@fdb and tbname=@ftb)        --select @addsql=@addsql+char(10)+createscript        --from lynnix order by type_desc             select @tempcounter=cast( count(1) as varchar(20))        from (select distinct dbname, ix_name  from #indexinfo  where dbname=@fdb and tbname=@ftb          ) as aa                    ;with ly        as        (            select distinct  ix_name,type_desc,createscript             from #indexinfo  where dbname=@fdb and tbname=@ftb        )         ,lynnix        as(             select   ix_name,type_desc,createscript,ROW_NUMBER() OVER(ORDER BY type_desc ) as id,@tempcounter as coun              from ly  )        ,lynnix1        as(select *,‘t‘+cast(id as varchar(10))+‘a‘ as t1, ‘t‘+cast(id as varchar(10))+‘b‘ as t2  from  lynnix         )        select @addsql=@addsql+        ‘declare @‘+t1+‘ datetime=getdate(),@‘+t2+‘ varchar(20)‘        +char(10)+createscript+char(10)        +‘select @‘+t2+‘=cast( cast( DATEDIFF(second ,@‘+t1+‘,getdate())/60.0  as decimal(12,1) )  as varchar(20))‘        +char(10)+‘RAISERROR(‘‘IX[‘+cast(coun as varchar(20))+‘/‘+cast(id as varchar(20))+‘]:    [‘+ix_name +‘] is done.    %s minutes‘‘,10,1,@‘+t2+‘) WITH NOWAIT  ‘+char(10)+‘GO‘+char(10)        from lynnix1 order by type_desc     end    --add sta    if exists (select top(1) 1 from   #stinfo  where dbname=@fdb and tbname=@ftb )    begin         select @addsql=@addsql+‘--***** add state *****‘+char(10)        ;with  lynnst        as(select distinct  createscript from   #stinfo  where dbname=@fdb and tbname=@ftb )        select @addsql=@addsql+createscript+char(10) from lynnst    end    --add full index    if exists (select top(1) 1 from   #fullindexinfo  where dbname=@fdb and tbname=@ftb )    begin         select @addsql=@addsql+‘--***** add full index *****‘+char(10)        select @addsql=@addsql+createscript+char(10) from #fullindexinfo where dbname=@fdb and tbname=@ftb;    end     --select @dropsql,@altersql,@addsql        update #lynn_result    set Size_MB=@fSize_MB,    Rows=@fRows,    dropscript=@dropsql, alterscript=@altersql,  addscript=@addsql    where dbname=@fdb and tbname =@ftb;    FETCH NEXT FROM lynn INTO @fdb ,@ftb,@fSize_MB,@fRows  END  CLOSE lynn      DEALLOCATE lynnDECLARE @engine varchar(2)DECLARE @rebuild varchar(10)SELECT @engine = CONVERT(varchar(2),SERVERPROPERTY(‘EngineEdition‘))IF @engine = 3    SET @rebuild = ‘ON‘ELSE    SET @rebuild = ‘OFF‘select DBName,TbName,Size_MB,Rows,inputcolumns,altercolumns,alter_script=‘RAISERROR(‘‘=====Processing table:‘+tbname+‘,‘+cast(Size_MB as varchar(20))+ ‘ MB,‘+CAST(Rows as varchar(20))+‘ Rows============‘‘,10,1) WITH NOWAIT ‘+char(10)+char(10)+‘/***** drop *****/‘+char(10)+‘USE [‘+DBName+‘]‘+char(10)+dropscript+char(10)+char(10)+‘/***** alter *****/‘+char(10)+‘USE [‘+DBName+‘]‘+char(10)+alterscript+char(10)+char(10)+‘/***** add *****/‘+char(10)+‘USE [‘+DBName+‘]‘+char(10)+addscript+case when Size_MB*1.0/1024>1.0  then ‘ declare @ta datetime=getdate(),@tb varchar(20)‘    +char(10)+ ‘ALTER INDEX ALL ON ‘+TbName+‘ REBUILD WITH(ONLINE=‘+@rebuild+‘)‘    +char(10)+‘select @tb=cast( cast( DATEDIFF(second ,@ta,getdate())/60.0  as decimal(12,1) )  as varchar(20))‘    +char(10)+‘RAISERROR(‘‘Rebuild is done.    %s minutes‘‘,10,1,@tb) WITH NOWAIT‘    else ‘‘ end+char(10)+char(10)+‘RAISERROR(‘‘=====End Process table:‘+tbname+‘============‘‘,10,1) WITH NOWAIT ‘,dropscript,alterscript,addscriptfrom  #lynn_resultorder by dbname IF OBJECT_ID(‘tempdb.dbo.#getsize‘) IS NOT NULL    DROP TABLE #getsizeif OBJECT_ID(‘tempdb..#currenttype‘,‘U‘) is not null    drop table #currenttypeIF OBJECT_ID(‘tempdb.dbo.#indexinfo‘) IS NOT NULL    DROP TABLE #indexinfoIF OBJECT_ID(‘tempdb.dbo.#dfinfo‘) IS NOT NULL    DROP TABLE #dfinfoIF OBJECT_ID(‘tempdb.dbo.#stinfo‘) IS NOT NULL    DROP TABLE #stinfoIF OBJECT_ID(‘tempdb.dbo.#ckinfo‘) IS NOT NULL    DROP TABLE #ckinfoIF OBJECT_ID(‘tempdb.dbo.#fullindexinfo‘) IS NOT NULL    DROP TABLE #fullindexinfoIF OBJECT_ID(‘tempdb.dbo.#lynn_tbinfo‘) IS NOT NULL    DROP TABLE #lynn_tbinfoIF OBJECT_ID(‘tempdb.dbo.#lynn_result‘) IS NOT NULL    DROP TABLE #lynn_result