自动更新补丁20110420

 

go
 update dbgo set id=  char(13)+char(10)+'GO'+char(13)+char(10) ,newline=char(13)+char(10)
GO

GO
 EXECUTE  dbobject_AutoDropSqlModules 'dbobject_OutPutSysObjects','P','2011-04-20 14:07:49.040',1
GO

GO
CREATE Procedure [dbo].[dbobject_OutPutSysObjects]
   @Object_name nvarchar(300),
   @object_type  nvarchar(2),
   @drop_Add int,
   @AddGo int,
   @ReplaceFlag int,
   @executeflag int,
   @objectSql nvarchar(max) output
 
AS

-- @AddGoFlag int ,
  ---@ReplaceFlag =@ReplaceFlag ,

if @drop_Add is null
   set @drop_Add = 2

if @executeflag is null
      set @executeflag =1

--不能删除（Drop)表对象
 if  @object_type =  'U'
      set @drop_Add = 2

---自动输出对象的建立SQL语法
if @object_type is null or @object_type =''
    select @object_type=type from  sys.objects where name=@Object_name
    
 -------------------开始处理---------------------------------------------------------------------------------------
  if @object_type ='P' OR @object_type = 'X' OR @object_type ='V' OR @object_type ='TR' OR @object_type ='FN' OR @object_type ='FS' OR @object_type ='FT' OR @object_type ='IF' OR @object_type ='TF'
     EXECUTE dbobject_OutputSqlModules @Object_Name =@Object_Name ,@Object_type =@Object_type ,@drop_Add =@drop_Add ,@ReplaceFlag =@ReplaceFlag ,  @executeflag =@executeflag ,@ObjectSql =@ObjectSql  output 
 else if  @object_type = 'D'
     EXECUTE dbobject_OutputDefaultSql @default_check=1, @Default_Name=@Object_name, @drop_Add = @drop_Add , @ReplaceFlag =@ReplaceFlag , @executeflag =@executeflag, @DefaultSql=@objectSql  output
 else if  @object_type = 'C'
     EXECUTE dbobject_OutputDefaultSql @default_check=2, @Default_Name=@Object_name, @drop_Add = @drop_Add , @ReplaceFlag =@ReplaceFlag , @executeflag =@executeflag, @DefaultSql=@objectSql  output
 else if  @object_type =  'U'
     EXECUTE dbobject_OutputCreateAlterTable @table_name=@object_name ,@columnsetflag =2 ,@executeflag =@executeflag ,@objectsql =@objectsql output
 else if  @object_type = 'PK' or   @object_type ='UQ'
     EXECUTE  dbobject_OutPutTableIndexes @Table_Name ='',@indexes_Name  =@object_Name,@Drop_Add =@drop_add ,@ReplaceFlag =@ReplaceFlag ,@executeflag =@executeflag ,@CreateIndexesSql =@objectsql output
 else if  @object_type ='F'
     EXECUTE  dbobject_OutPutTableforeign @foreign_Name = @object_Name ,@Drop_Add =@Drop_Add  ,@ReplaceFlag =@ReplaceFlag ,@executeflag =@executeflag ,@ObjectSql = @ObjectSql output
 if @AddGo =1
   begin
      EXECUTE dbgo_OutputSql @objectsql output
   end
return 

 

 

 

GO

GO

GO

GO
 EXECUTE  dbobject_AutoDropSqlModules 'dbobject_OutPutTableForeign','P','2011-04-19 09:02:18.470',1
GO

GO
CREATE  procedure  [dbo].[dbobject_OutPutTableForeign]
   @foreign_Name nvarchar(1000) ,
   @Drop_Add int ,
   @ReplaceFlag int,
   @executeflag int,
   @ObjectSql  nvarchar(max) output
AS

 
 declare  @table_name nvarchar(300),
    @table_columns nvarchar(4000) ,
    @referenced_Table_name nvarchar(300),
    @referenced_Columns nvarchar(4000),
  @modify_Date datetime   ,
  @existflag int
  
declare @proceName nvarchar(500)     
declare @modify_Date_Str nvarchar(28)

     
   ---从Foreign_Name 获得相关的表名 相关表名  相关列   修改日期
    Exec  dbobject_ForeignGetTableColumns
         @Foreign_Name =@Foreign_Name,
         @drop_add =@drop_add ,
         @table_name =@table_name output,
         @table_columns =@table_columns  output,
         @referenced_Table_name=@referenced_Table_name output,
         @referenced_Columns  =@referenced_Columns  output,
         @modify_Date=@modify_Date output,
         @existflag =@existflag  output
         
 

   if @executeflag  is null
    set  @executeflag=1
    
 if @modify_date is null
    set @modify_date='2000-10-10 15:16:01.050'
    

 --强制替换
   if @ReplaceFlag = 3
    begin
      set @modify_date = GETDATE() + 2000
    end

 

    
 set @modify_Date_Str=convert(varchar(23),@modify_date,121)

 

    
    if @Drop_Add = 1
      set @proceName ='dbObject_AutoTableDropForeign'
    else
      set @proceName ='dbObject_AutoTableAddForeign'
    
    
    
    set @ObjectSql= ' EXECUTE ' +    @proceName + ' ' +''''+@Foreign_Name+''''+ ',' + ''''+@table_name +''''+  ',' + ''''+@table_columns +''''+',' + ''''+@referenced_Table_name +''''+','+ ''''+@referenced_Columns +''''+','+
           ''''+@modify_Date_Str+''''+','+CONVERT(nvarchar(30), @executeflag )
 
 
 return

 

GO

GO

GO

GO
 EXECUTE  dbobject_AutoDropSqlModules 'DbObject_DropDefaultName','P','2011-04-18 14:41:28.957',1
GO

GO
CREATE  procedure   [dbo].[DbObject_DropDefaultName]
   @default_check int,
   @Default_Name nvarchar(500),
   @Table_name  nvarchar(300),
   @executeflag int
 AS
 
 ---强制删除缺值对象
     
declare @defaultSql nvarchar(4000)
     
 if @Table_name is null or @Table_name=''
 begin
      if @default_check =1
        Select  @Table_name = object_name(D.parent_object_id)  FROM sys.default_constraints D  Where ( D.name=@Default_Name )
     else
        Select  @Table_name = object_name(D.parent_object_id)  FROM sys.check_constraints D  Where ( D.name=@Default_Name )  
end

 set @defaultSql=' ALTER  TABLE '+ @Table_name  + ' DROP CONSTRAINT ' + @Default_Name

if @executeflag=1
   EXECUTE sp_executesql    @defaultSql

else
   EXECUTE DbGo_PrintSql  @defaultSql
 

 
 
 
 ---exec  dboject_AutoDefaultAddSql 'DF_planorder2_Released_BillQuantity','planorder2','Released_BillQuantity','((0))','2011-03-15 15:55:16.040',1
 
 
GO

GO