统一更新字段长度,类型

Question

想要统一检查数据库中一批字段的类型,  长度,  是否允许空值.等三个属性的设置是否正确,  
如果不正确要修改成统一的正确设置;该怎么做.  
譬如,数据库中的很多表中都有一个字段Fname,我希望的字段设置是  
Fname  nvarchar(20)  not  null  
但在实际操作中可能出现很多问题;导致现在数据表中的设置不一致;  
我想在系统表中统一更改.  
该怎么做?

Answer 1

系統表syscolumns 
name 字段名稱, 
xtype 字段類型
typeStat  是否為空 1 – not null , 0 null

Answer 2

--實例--允許系統表寫
exec sp_configure 'Allow updates',1 reconfigure with override            --
go begin tran UpdateFieldLength     --開啟事務declare 
  @Fieldname nvarchar(64), 
  @FieldLength int  , 
  @Fieldisnull int    , 
  @FieldType int    
select @FieldName = 'FName'   ,      --字段名稱
          @FieldLength = 40   ,     --字段類型長度
          @Fieldisnull  =1   ,      --是否允許為空, 1-- not null  0-- null
          @FieldType = 62          -- 231-- nvarchar, 62-- float,   61 -- datetime, --更新字段長度, 是否為null, 類型
if 1 =  0 
begin 
update t1 set t1.length = @FieldLength ,  t1.typeStat = @Fieldisnull , t1.xtype = @FieldType                   
from syscolumns t1 
left join sysobjects t2 on t1.id = t2.id 
where t1.name = @FieldName and t2.xtype = 'U'
          
if @@Error <> 0 
begin
rollback tran UpdateFieldLength       --出錯時事務回滾
end
end--顯示更新後的結果
select  t2.Name , t1.name, t1.xtype,t1.typestat,  t1.length, t1.colid, t1.Status, t1.type, t3.name
from syscolumns t1 
left  join sysobjects t2 on t2.id = t1.id
left join systypes t3 on t1.xtype = t3.xtype
where t1.name = @FieldName   and t2.xtype = 'U' 
order by t1.namecommit tran UpdateFieldLength   --事務執行--關閉系統表寫
exec sp_configure 'Allow updates' , 0 reconfigure with override
go

Answer 3

syscolumns.typeStat为内部保留使用字段,对它修改会有甚幺问题吗? 另外,它的值好象也不止是0,1,当出现2,3的时候又分别代表甚幺意思?

Answer 4

回复人： 47522341(睡到8：30)(最是她一低头的温柔) ( ) 信誉：113  2004-8-26 10:20:34  得分: 1  
 
 
   
--測試1
select distinct t1.typeStat , t1.xtype, t1.type, t2.name
from syscolumns t1 
left join systypes t2 on t1.xtype = t2.xtype 
order by t1.typestat
--執行結果
typestat xtype    type     typename
0 35 35 text
0 56 38 int
0 108 108 numeric
0 61 111 datetime
0 165 37 varbinary
0 62 109 float
0 104 50 bit
0 34 34 image
0 48 38 tinyint
0 231 39 nvarchar
0 52 38 smallint
0 231 39 sysname
0 99 35 ntext
0 98 39 sql_variant
1 56 56 int
1 60 60 money
1 231 39 nvarchar
1 231 39 sysname
1 173 45 binary
1 62 62 float
1 127 63 bigint
1 165 37 varbinary
1 239 47 nchar
1 52 52 smallint
1 61 61 datetime
1 48 48 tinyint
1 175 47 char
2 239 39 nchar
2 231 39 nvarchar
2 173 37 binary
2 167 39 varchar
2 165 37 varbinary
2 175 39 char
2 231 39 sysname
3 231 39 sysname
3 165 37 varbinary
3 167 39 varchar
3 239 47 nchar
3 231 39 nvarchar
3 175 47 char
  
 

Answer 5

回复人： 47522341(睡到8：30)(最是她一低头的温柔) ( ) 信誉：113  2004-8-26 10:22:39  得分: 1  
 
 
   
--測試2
select distinct t1.typeStat , t1.xtype, t1.type, typename = t2.name
into #Table
from syscolumns t1 
left join systypes t2 on t1.xtype = t2.xtype 
order by t1.typestatselect * from #Table 
order by typename, typestat
--測試結果
typeStat     Xtype       Type             TypeName 
1 127 63 bigint
1 173 45 binary
2 173 37 binary
0 104 50 bit
1 175 47 char
2 175 39 char
3 175 47 char
0 61 111 datetime
1 61 61 datetime
0 62 109 float
1 62 62 float
0 34 34 image
0 56 38 int
1 56 56 int
1 60 60 money
1 239 47 nchar
2 239 39 nchar
3 239 47 nchar
0 99 35 ntext
0 108 108 numeric
0 231 39 nvarchar
1 231 39 nvarchar
2 231 39 nvarchar
3 231 39 nvarchar
0 52 38 smallint
1 52 52 smallint
0 98 39 sql_variant
0 231 39 sysname
1 231 39 sysname
2 231 39 sysname
3 231 39 sysname
0 35 35 text
0 48 38 tinyint
1 48 48 tinyint
0 165 37 varbinary
1 165 37 varbinary
2 165 37 varbinary
3 165 37 varbinary
2 167 39 varchar
3 167 39 varchar
  
 

Answer 6

對系統表的更新很難保證不出問題;特別是在對字段不十分了解的情況下.
有一個比較穩託的方式是疊加執行語句,進行更新;
但這時需要注意的是,如果數據表中已經有紀錄存在,將原來null的字段更新為not null
時會出錯.--測試3
begin tran UpdateFieldLength1  --啟動事務declare 
  @ExecSql nvarchar(4000), 
  @SqlStr nvarchar(200), 
  @Fieldname nvarchar(64), 
  @TableName nvarchar(64) , 
  @FieldLength int select  @ExecSql = '  ',  
          @FieldName = 'FProdID'   , 
          @FieldType = '  nvarchar(64) not null  '  , 
          @FieldLength = 128if 1 =  0   
begindeclare update_cursor cursor for select distinct t2.name 
from syscolumns t1 
left join sysobjects t2 on t1.id = t2.id 
where t1.name = @FieldName  and t2.xtype = 'U'     and   t1.Length <> @FieldLength  
order by t2.name open update_cursorfetch next from update_cursor into @TableName
while @@Fetch_Status = 0 
begin
set  @SqlStr = ' Alter table [' + @TableName + ']   alter column [' + @FieldName + '] ' + @FieldType  + '
  'set @ExecSql = @ExecSql + @SqlStrfetch next from update_cursor into @TableName  
endclose update_cursor
deallocate update_cursorprint @ExecSql 
--exec(@ExecSql)
if @@Error <> 0 
begin 
  rollback tran UpdateFieldLength1  --出錯時事務回滾
endend--顯示更新後的結果
select  t2.Name , t1.name, t1.xtype,t1.typestat,  t1.length, t1.colid, t1.Status, t1.type, t3.name
from syscolumns t1 
left  join sysobjects t2 on t2.id = t1.id
left join systypes t3 on t1.xtype = t3.xtype
where t1.name = @FieldName    and t2.xtype = 'U'   --  and  t1.Length <> @FieldLength 
order by t2.name, t1.name
commit tran UpdateFieldLength1          --事務執行