修改數據庫中指定類型字段的屬性

Use cnt
Set Nocount On
Go
Select identity(int,1,1) as pk,* Into #Temp From (
SELECT tablename = d.name,
    fename    = a.name,
       IsPKey    = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                                                      SELECT name FROM sysindexes WHERE indid in(
                                                      SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
                                                      ))) then 'Yes' else '' end,
       ftype     = b.name,
       flen      = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
       AllowNull = case when a.isnullable=1 then 'Yes'else '' end,
       fdefault = e.text,d.id,a.colid,a.cdefault as dfid
FROM dbo.syscolumns a
    left join  dbo.systypes      b on a.xusertype=b.xusertype
    inner join dbo.sysobjects    d on a.id=d.id and d.xtype = 'U' and d.name <> 'dtproperties'
    left join  dbo.syscomments   e on a.cdefault=e.id
Where a.name <> 'ukey'
   and a.name <> 'pkey'
   and b.name <> 'bit'
   and b.name <> 'numeric'
   and b.name <> 'datetime'
   and b.name <> 'smalldatetime'
   and b.name <> 'nvarchar'
   and b.name <> 'ntext'
   and b.name <> 'char'
   and a.name <> 'docno'
   and a.name <> 'code'
   and a.name <> 'prodcode'
   and a.name <> 'suppcode'
   and a.name <> 'custcode'
   and a.name <> 'acctcode'
) tmp
Order By tablename,fename

Declare @nRowCount Int
Declare @nRow      Int,@iRow Int
Declare @ctable    varchar(100)
Set @nRow   = 1
Set @iRow = 1
Set @ctable = ''       
Select @nRowCount = Count(1) From #Temp  --記錄Temp臨時表

Declare @cDF  varchar(100)     --構造默認值名稱 DF_表名_字段名
Declare @cIndex  varchar(100)    --索引名稱
Declare @nType  varchar(100)    --構造默認值名稱 DF_表名_字段名
Declare @nObjectId  varchar(50)   --記錄表的編號
Declare @tAllowNull varchar(10)    --記錄NULL 和 NOT NULL
Declare @tCName varchar(500)    --記錄索引的字段名
Set @tCName = ''
Set @tAllowNull = 'NULL'
Set @nObjectId = ''
Set @cDF = ''

Declare @cCurTableName  varchar(100)  --表名
Declare @cCurFieldName  varchar(100)  --字段名
Declare @nCurLength     varchar(100)        --字段長度
Declare @ctype   varchar(50)   --字段類型
Declare @cAllowNull  varchar(50)   --字段是否允許為空
Declare @cObjectId  varchar(50)   --表編號
Declare @cDefault  varchar(100)  --字段默認值
Declare @cDFId   varchar(100)  --字段默認值編號

Declare @tName varchar(50)     --索引名稱
Declare @tICcount int      --索引重數
Declare @cName varchar(50)     --臨時變量，保存索引名稱
Declare @tFieldName varchar(100)   --臨時變量，保存索引名稱
Declare @i int
Set @i = 0
Set @cName = ''
Set @tFieldName = ''
Set @tName = ''
Set @tICcount = 1

While @nRow <= @nRowCount
 Begin
  Select @cCurTableName = tablename,@cCurFieldName = fename,@nCurLength = flen,@ctype = ftype,@cDFId = dfid,
   @cAllowNull = AllowNull,@cObjectId = id,@cDefault = fdefault
  From #Temp Where pk = @nRow
  ----------------------Delete Default Value-----------------------------------------------
  Set @cDF = ''
  If @cDefault is not null
   Begin
    Select @cDF = name  from dbo.sysobjects where id = @cDFId
    Exec('ALTER TABLE ' + @cCurTableName + ' DROP CONSTRAINT ' + @cDF)
   End
  -------------------------------Create Index-------------------------------------------------------------
  If  @ctable <> @cCurTableName and @ctable <> ''
   Begin     
    If  @nObjectId <> ''
     Begin      
      Set @tCName = ''
      Select @iRow = Count(1) From #Temp1
      Set @i = 0
      While @iRow > 0
       Begin
        Select @tName = name,@tICcount = culumncount From #Temp1 Where pk = @iRow
        Select identity(int,1,1) As pk,fieldname Into #Temp3 From #Temp2 Where name = @tName
        Select @i = Count(1) From #Temp3
        Set @tCName = ''
        While @i > 0
         Begin
          Select  @cIndex = fieldname From #Temp3 Where pk = @i
          If @tCName <> ''
           Begin
            Set @tCName = @cIndex + ' ASC,' + @tCName
           End
          Else
           Begin
            Set @tCName = @cIndex + ' ASC'                    
           End 
          Set @i = @i -1
         End
        Drop Table #Temp3
        If @tCName <> ''
         Exec('CREATE INDEX ' + @tName + ' ON ' + @ctable + '(' + @tCName + ') With (STATISTICS_NORECOMPUTE = ON)')
         
        Set @iRow = @iRow - 1      
       End      
     End  
     Drop Table #Temp1 
     Drop Table #Temp2
   End
  -------------------------Delete Index---------------------------------------------------------  
  If  @ctable <> @cCurTableName   
   Begin    
    Select identity(int,1,1) as pk,K.name,S.name as fieldname,d.name as tablename Into #Temp2
       From dbo.syscolumns S Left Join sys.index_columns I On S.id = I.object_id and S.colid = I.column_id
              Left Join dbo.sysindexes K    On S.id = K.id and I.index_id = K.indid
              inner join dbo.sysobjects   d On S.id=d.id and d.xtype = 'U' and d.name <> 'dtproperties'
       Where S.id = @cObjectId and K.name <> '' and S.name <> 'ukey'  and S.name <> 'pkey' And K.name Not Like 'PK_%' And K.status != '4098'
       Order By K.name     
    select identity(int,1,1) as pk,name,count(1) as culumncount into #Temp1 from #Temp2 group by name
    Select @iRow = Count(1) From #Temp1
    While @iRow > 0
     Begin
      Select @cIndex = name From #Temp1 Where pk = @iRow
      Exec('DROP INDEX ' + @cIndex + ' On '+@cCurTableName)
      Set @iRow = @iRow - 1
     End         
   End 
  ----------------------Update Column's Attribute-------------------------------------------------------------------
  If @cAllowNull = 'Yes'
   Set @tAllowNull = 'NUll'
  Else
   Set @tAllowNull = 'NOT NULL'
  If Upper(@ctype) = 'NCHAR'
   Begin
    Set @nType = 'nvarchar(' + @nCurLength + ')'
    Exec('Alter Table ' + @cCurTableName + ' Alter Column ' + @cCurFieldName + ' ' + @nType + ' ' + @tAllowNull )
   End
  If Upper(@ctype) = 'VARCHAR'
   Begin
    Set @nType = 'nvarchar(' + @nCurLength + ')'
    Exec('Alter Table ' + @cCurTableName + ' Alter Column ' +  @cCurFieldName + ' ' + @nType + ' ' + @tAllowNull )
   End
  If Upper(@ctype) = 'TEXT'
   Begin
    Set @nType = @cCurTableName +'.' + @cCurFieldName    
    Exec('Alter Table ' + @cCurTableName + ' Add newcolumn Ntext Null' )     
    Exec('Update '+ @cCurTableName + ' Set newcolumn = ' + @cCurFieldName)      
    Exec('Exec sp_rename '''+@nType+''',''oldcolumn'',''COLUMN''')   
    Exec('Exec sp_rename '''+@cCurTableName+'.newcolumn'','''+@cCurFieldName +''',''COLUMN''')      
    Exec('Alter Table ' + @cCurTableName + ' Drop Column oldcolumn')    
   End
  --------------------Insert Default Value--------------------------------------------------------------------------------
  If @cDefault is not null
   Begin    
    Exec('ALTER TABLE ' + @cCurTableName + ' ADD CONSTRAINT ' + @cDF + ' Default ' + @cDefault + ' For ' + @cCurFieldName)
   End  
  --------------------------------------------------------------------------------------------------------- 
  Set @nRow = @nRow + 1
  Set @ctable = @cCurTableName
  set @nObjectId = @cObjectId  
 End
  
----------------------Create Index In The Last Table-------------------------------------
If  Exists (Select Count(1) From #Temp1 Having Count(1) > 0)
 Begin     
   If  @nObjectId <> ''
    Begin      
     Set @tCName = ''
     Select @iRow = Count(1) From #Temp1
     Set @i = 0
     While @iRow > 0
      Begin
       Select @tName = name,@tICcount = culumncount From #Temp1 Where pk = @iRow       
       Select identity(int,1,1) As pk,fieldname Into #Temp4 From #Temp2 Where name = @tName
       Select @i = Count(1) From #Temp3
       Set @tCName = ''
       While @i > 0
        Begin
         Select  @cIndex = fieldname From #Temp3 Where pk = @i
         If @tCName <> ''
          Begin
           Set @tCName =  @cIndex + ' ASC,' + @tCName           
          End
         Else
          Begin
           Set @tCName = @cIndex + ' ASC'                    
          End 
         Set @i = @i -1
        End
       Drop Table #Temp4
       Exec('CREATE INDEX ' + @tName + ' ON ' + @ctable + '(' + @tCName + ') With (STATISTICS_NORECOMPUTE = ON)')
       Set @iRow = @iRow - 1  
      End      
    End 
   End
----------------------------------------------------------- 
Drop Table #Temp1
Drop Table #Temp2 
Drop Table #Temp
Go