sql 自动更新数据库语句 sql server 2008

---主要入口:dbobject_outputsysobjects
----@object_name nvarchar(1024),----输出对象的名称(必填）
----   @object_type  nvarchar(2),---输出对象的类型（允许为空，自动在sys.objects视图获得(type))
   --@drop_add int,---输出类型 是drop还是 add 对象  1=drop 2.=add （一般等于2)
   --@replaceflag int,  ---更新选项  0.1 系统默认  3. 强制更新 （不过只是智能加大modify_date)
   --@executeflag int,  ---输出类型  1.输出可执行的语法    0.只输出用来print 语法（一般是1）
   --@objectsql nvarchar(max) output --返回sql 语句  （在前台获得sql 就可以执行  只要有连个连接sqlca 就可以更新两个数据库教程的对象）

--返回sql 语句  （在前台获得sql 就可以执行  只要有连个连接sqlca 就可以更新两个数据库的对象）
--通过截取char(13)+char(10)+'go'+char(13)+char(10) +  sqlcode+char(13)+char(10)+'go'+char(13)+char(10)
--获得sqlcode   然后通过execute immediate :sqlcode using use ;
---通过循环执行实现自动更新

go
/****** object:  storedprocedure [dbo].[dbobject_tablegetindexcolumns]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure  [dbo].[dbobject_tablegetindexcolumns]
  @table_name nvarchar(300),
  @indexes_name nvarchar(1000),
  @indexes_columns  nvarchar(4000) output
as

     ---返回某个表某个索引的列名称
if @table_name is null    
   set @table_name =''

 

---得到表的索引列号
  --a.object_id,
 --select @c_name = @c_name + '',['' + a.name + (case b.is_descending_key when 1 then ''] desc'' else ''] asc'' end)
  declare @ls_target_column_name nvarchar(1000)
 
  set @ls_target_column_name  =''
 
 declare @column_name nvarchar(500)
---------------------------
 declare @my_cursor  cursor
     set @my_cursor=cursor for   select
   a.name
   from sys.columns a ,sys.index_columns b , sys.indexes c
     where a.object_id = b.object_id and
       a.column_id = b.column_id  and
          b.object_id = c.object_id and
        b.index_id = c.index_id
     and a.object_id = object_id(@table_name)
     and c.name=(@indexes_name) 
 
 
 
-----------------
  open @my_cursor

------------------------------
  fetch  from @my_cursor into @column_name

while  @@fetch_status = 0
   begin
     if @ls_target_column_name  =''
          set @ls_target_column_name  =@column_name
     else
        set @ls_target_column_name  =@ls_target_column_name  +','  +@column_name
         fetch  from @my_cursor into   @column_name
     end
    
    
 set  @indexes_columns= @ls_target_column_name

------------------
  close @my_cursor

----------------------
 deallocate @my_cursor
go
/****** object:  storedprocedure [dbo].[dbobject_tablecolumngetvalues]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure [dbo].[dbobject_tablecolumngetvalues]
    @table_name nvarchar(300),
    @column_name nvarchar(300) ,
    @datatype nvarchar(200) output ,
    @is_computed int output,
    @is_nullable int output,
    @is_identity int output ,
    @default_definition nvarchar(max) output ,
    @computer_definition nvarchar(max) output ,
    @identity_sql nvarchar(1000) output
as

--返回某个表某列的[修改列数值]
  select  
   @datatype = (type_name(a.system_type_id) +
     case when a.system_type_id in (167,175) then '(' + (case a.max_length when -1 then 'max' else rtrim(a.max_length) end) + ')'
     when a.system_type_id in (231,239) then '(' + (case a.max_length when -1 then 'max' else rtrim(a.max_length / 2) end) + ')'
     when a.system_type_id in (106,108) then '(' + rtrim(a.precision) + ',' + rtrim(a.scale) + ')'
     else '' end  ) ,
     @is_computed = a.is_computed,
     @is_nullable = a.is_nullable ,
     @is_identity = a.is_identity
    from sys.columns a where object_id = object_id(@table_name)
    and a.name =@column_name
    
 
    if @is_computed=1
     begin
       select @computer_definition  =' as ' + definition + case c.is_persisted when '1' then ' persisted' else '' end
            from sys.computed_columns  c where c.name =  @column_name  and c.object_id = object_id( @table_name)
     end
   
     if @is_identity =1
     begin
       select top 1  @identity_sql = ' identity(' + cast(e.seed_value as varchar(10)) + ',' + cast(e.increment_value as varchar(10)) + ')'
         from sys.identity_columns e where e.name = @column_name and  e.object_id = object_id( @table_name)
     end

    if   @computer_definition  is null
          set  @computer_definition =''
 
    if @identity_sql is null
          set  @identity_sql =''
      
 
return

 

 ----------------------
go
/****** object:  storedprocedure [dbo].[dbobject_tablecolumngetdefaultname]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create   procedure  [dbo].[dbobject_tablecolumngetdefaultname]
   @default_check int,
   @default_name nvarchar(500) output,
   @table_name  nvarchar(300) ,
   @column_name nvarchar(300) ,
   @default_value nvarchar(4000) output,
   @modify_date datetime output,
   @existflag int output
   
 
as

set @existflag = 0

if @default_check =2
begin  
   --从表与列得到check对象的名称与数值
  select  @default_name= d.name, @default_value = convert(nvarchar(4000), d.definition )  ,
    @modify_date =d.modify_date ,
    @existflag=1
    from sys.check_constraints d, sys.columns c
    where d.parent_object_id = c.object_id
    and d.parent_column_id =c.column_id
    and  ( object_name(d.parent_object_id)=@table_name )
    and  ( c.name=@column_name )

end
else
begin
  --从表与列得到缺省对象的名称与数值
  select  @default_name= d.name, @default_value = convert(nvarchar(4000), d.definition )  ,
    @modify_date =d.modify_date ,
    @existflag=1
    from sys.default_constraints d, sys.columns c
    where d.parent_object_id = c.object_id
    and d.parent_column_id =c.column_id
    and  ( object_name(d.parent_object_id)=@table_name )
    and  ( c.name=@column_name )
  
end
  
if @existflag is null
  set @existflag = 0
    
return
    
    
-- begin transaction
--go
--alter table dbo.account add constraint  df_account_name default '2001' for name
--go
--alter table dbo.account set (lock_escalation = table)
--go
--commit
go
/****** object:  table [dbo].[pbcatcol]    script date: 04/20/2011 08:57:04 ******/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go
create table [dbo].[pbcatcol](
 [pbc_tnam] [varchar](100) not null,
 [pbc_tid] [int] null,
 [pbc_ownr] [char](30) null,
 [pbc_cnam] [varchar](50) not null,
 [pbc_cid] [smallint] null,
 [pbc_labl] [varchar](254) null,
 [pbc_lpos] [smallint] null,
 [pbc_hdr] [varchar](254) null,
 [pbc_hpos] [smallint] null,
 [pbc_jtfy] [smallint] null,
 [pbc_mask] [varchar](31) null,
 [pbc_case] [smallint] null,
 [pbc_hght] [smallint] null,
 [pbc_wdth] [smallint] null,
 [pbc_ptrn] [varchar](31) null,
 [pbc_bmap] [varchar](1) null,
 [pbc_init] [varchar](254) null,
 [pbc_cmnt] [varchar](254) null,
 [pbc_edit] [varchar](31) null,
 [pbc_tag] [varchar](254) null,
 [create_date] [datetime] null,
 [modify_date] [datetime] null,
 [flag] [int] null,
 [datatype] [varchar](100) null,
 [newdatatype] [varchar](100) null,
 [deleteflag] [int] null,
 [selectflag] [int] null,
 [existflag] [int] null,
 [isnullable] [int] null,
 constraint [pk_pbcatcol] primary key clustered
(
 [pbc_tnam] asc,
 [pbc_cnam] asc
)with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary]
) on [primary]
go
set ansi_padding off
go
/****** object:  storedprocedure [dbo].[dbobject_tableprimaryname]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbobject_tableprimaryname]
      @table_name nvarchar(300),
   @indexes_name nvarchar(500) output ,
   @type_desc nvarchar(200 ) output
as

select  top 1 @indexes_name  =  i.name ,@type_desc=i.type_desc from   sys.indexes  i where   object_id = object_id(@table_name)  and is_primary_key=1
  
 return
go
/****** object:  storedprocedure [dbo].[dbobject_defaultgettablename]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure  [dbo].[dbobject_defaultgettablename]
   @default_check int ,
   @default_name nvarchar(500),
   @table_name  nvarchar(300) output,
   @column_name nvarchar(300) output ,
   @default_value nvarchar(4000) output,
   @modify_date datetime output,
   @existflag int output
   
 
as

 

if @default_check=2
   begin

         --check名称 得到 表名 列名

           set @existflag =0

            select  @table_name = object_name(d.parent_object_id) ,
              @default_value = convert(nvarchar(4000), d.definition ) ,
              @modify_date =d.modify_date  ,
              @existflag=1
              from sys.check_constraints d
              where ( d.name=@default_name )
         ---alter table dbo.abc add constraint df_abc_name default '23' for name
          
          select  @column_name=c.name
             from sys.check_constraints d, sys.columns c
             where d.parent_object_id = c.object_id
             and d.parent_column_id =c.column_id
             and  (d.name=@default_name)
             and  (object_name(d.parent_object_id)=@table_name )
             
          if  @existflag  is null
               set @existflag =0
           if @default_value is null
                  set @default_value=space(0)
   end    
else
    begin
     --缺省名称 得到 表名 列名

      set @existflag =0

       select  @table_name = object_name(d.parent_object_id) ,
         @default_value = convert(nvarchar(4000), d.definition ) ,
         @modify_date =d.modify_date  ,
         @existflag=1
         from sys.default_constraints d
         where ( d.name=@default_name )
    ---alter table dbo.abc add constraint df_abc_name default '23' for name
     
     select  @column_name=c.name
        from sys.default_constraints d, sys.columns c
        where d.parent_object_id = c.object_id
        and d.parent_column_id =c.column_id
        and  (d.name=@default_name)
        and  (object_name(d.parent_object_id)=@table_name )
        
     if  @existflag  is null
          set @existflag =0
     
     
     
      if @default_value is null
             set @default_value=space(0)
       
    end    
return
    
    
-- begin transaction
--go
--alter table dbo.account add constraint  df_account_name default '2001' for name
--go
--alter table dbo.account set (lock_escalation = table)
--go
--commit
go
/****** object:  storedprocedure [dbo].[dbobject_defaultclearbracket]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure  [dbo].[dbobject_defaultclearbracket]
     @default_value nvarchar(4000) output
as

 

---删除掉第一个'(' 和最后一个')'
 set @default_value = ltrim(rtrim( @default_value ))
 
declare @start_bracket nchar(1)
declare @end_bracket nchar(1)

 set @start_bracket=substring(@default_value,1,1)
   set @end_bracket=substring(@default_value,len(@default_value),1)
 
 
 if (  @start_bracket='(' and @end_bracket=')' )
    begin
      set @default_value=stuff(@default_value,1,1,'')
      set @default_value=stuff(@default_value,len(@default_value),1,'')
    end   
    
--replace(@default_value ,'((','(')
--replace(@default_value ,'))',')')

return
go
/****** object:  storedprocedure [dbo].[dbobject_autotabledropindexes]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbobject_autotabledropindexes]
   @table_name nvarchar(500),
   @indexes_name nvarchar(800) ,
   @indexes_columns  nvarchar(4000) ,
   @type_desc nvarchar(100), 
   @is_unique int,
   @is_primary_key int,
   @is_unique_constraint int  ,
    @modify_date datetime,
    @executeflag int 
as
---删除数据库对象  自动处理 @executeflag=1  立即执行

--------------------------------------------键和索引的创建方法不一样-----------------------------------------------------------------------------
 declare @exists_sql nvarchar(4000)
 declare @dropindexes nvarchar(4000)
 
 
 
 
 
 

--declare @modify_date_sql nvarchar(300)
--   set @modify_date_sql =' modify_date>'+''''+ @modify_date_sql +''''

 if @is_primary_key=1
      begin
          if   exists (select * from sys.objects  where  name=@indexes_name )
              set @dropindexes =' alter  table ' + @table_name + '   drop constraint  '  +  @indexes_name
      end
else   ----唯一键
      begin
           if @is_unique_constraint = 1
               begin
                    if   exists (select * from sys.objects  where  name=@indexes_name )
                     set @dropindexes =' alter  table ' + @table_name + '   drop constraint  '  +  @indexes_name
               end 
          else
               begin
                   if   exists (select * from sys.indexes  where  object_id =object_id (@table_name) and name=@indexes_name )
                     set  @dropindexes ='   drop  index  '  +  @indexes_name  + '  on  ' +  @table_name
               end
      end        
 if @executeflag =1
    begin
        if not ( @dropindexes is null or  @dropindexes='')
             execute sp_executesql @dropindexes
    end
 else
        print char(10)+'go'+char(10)+  @dropindexes  + char(10)+'go'+char(10)

return
--
--begin transaction
--go
--alter table dbo.account  drop constraint df_account_name
--go
--alter table dbo.account add constraint  df_account_name default ( ' 1234' ) for name
--go
--alter table dbo.account set (lock_escalation = table)
--go
--commit
--
--
--drop index ix_abc on dbo.abc
--go
--alter table dbo.abc
-- drop constraint pk_abc

--
--
-----普通索引
--create nonclustered index ix_abc on dbo.abc
-- (
-- name
-- ) with( statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
--go
-----唯一索引
--create unique nonclustered index ix_abc_id on dbo.abc
-- (
-- id
-- ) with( statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
--go
--alter table dbo.abc set (lock_escalation = table)
--go
--commit
----
go
/****** object:  table [dbo].[dbgo]    script date: 04/20/2011 08:57:04 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create table [dbo].[dbgo](
 [id] [nvarchar](20) not null,
 [newline] [nchar](2) not null,
 constraint [pk_dbgo] primary key clustered
(
 [id] asc
)with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary]
) on [primary]
go
/****** object:  storedprocedure [dbo].[dbobject_foreigngetcolumns]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure  [dbo].[dbobject_foreigngetcolumns]
   @foreign_name nvarchar(300) ,
   @table_columns nvarchar(4000) output ,
   @referenced_columns nvarchar(4000) output
as

---得到表的索引列号
  --a.object_id,
 --select @c_name = @c_name + '',['' + a.name + (case b.is_descending_key when 1 then ''] desc'' else ''] asc'' end)
 
 declare @table_column_name nvarchar(500)
 declare @referenced_column_name  nvarchar(500)
 
 
set  @table_columns =''
set  @referenced_columns =''
 
--select  object_name(f.parent_object_id) as parent_name  , object_name( f.constraint_object_id ),
--   object_name(f.referenced_object_id) as referenced_object_name,

---------------------------
 declare @my_cursor  cursor
     set @my_cursor=cursor for   select
    c2.name  as table_column_name ,c1.name  as referenced_column_name
    from sys.foreign_key_columns f, sys.columns c2, sys.columns c1
    where f.parent_object_id=c2.object_id
    and   f.referenced_object_id=c1.object_id
    and  f.parent_column_id=c2.column_id
    and  f.referenced_column_id=c1.column_id
    and ( object_name( f.constraint_object_id ) = @foreign_name )
 
  open @my_cursor
  fetch  from @my_cursor into @table_column_name,@referenced_column_name

while  @@fetch_status = 0
   begin
     if @table_columns  =''
          set @table_columns  =@table_column_name
     else
        set @table_columns  =@table_columns  +','  +@table_column_name
        
     if @referenced_columns  =''
          set @referenced_columns  =@referenced_column_name
     else
        set @referenced_columns  =@referenced_columns  +','  +@referenced_column_name
        
      fetch  from @my_cursor into @table_column_name,@referenced_column_name

     end
------------------
  close @my_cursor
----------------------
 deallocate @my_cursor

  
  
  
 return
 
 ----select  object_name( f.constraint_object_id ),object_name(f.parent_object_id) as parent_name ,object_name(f.referenced_object_id) as referenced_object_name
 ----     from sys.foreign_key_columns f
 ----  where ( @table_name ='' or ( f.parent_object_id= object_id(@table_name) ) )
 ---- and ( @foreign_name='' or ( object_name( f.constraint_object_id ) = @foreign_name ) )
go
/****** object:  storedprocedure [dbo].[dbobject_gettableindexesvalues]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure  [dbo].[dbobject_gettableindexesvalues]
   @table_name nvarchar(300),
   @indexes_name nvarchar(300),
   @old_indexes_columns  nvarchar(4000) output ,
   @old_type_desc nvarchar(100)  output,
   @old_is_unique int output, 
   @old_is_primary_key int output,
   @old_is_unique_constraint int  output
as

set @old_indexes_columns=''

--自动得到原来的数据建立索引对象的程序代码
   select @old_type_desc =type_desc,@old_is_unique =abs(is_unique) ,@old_is_primary_key =abs(is_primary_key),@old_is_unique_constraint = abs(is_unique_constraint)
     from  sys.indexes  i
     where   (object_id = object_id(@table_name) )
     and (i.name=(@indexes_name) )
   
     
          ---返回某个表某个索引的列名称组
         exec dbobject_tablegetindexcolumns
           @table_name =@table_name,
           @indexes_name =@indexes_name,
           @indexes_columns  =@old_indexes_columns  output
  

  if @old_is_unique is null
 set @old_is_unique=0
 
 if   @old_is_primary_key  is null
  set @old_is_primary_key=0
 
 if   @old_is_unique_constraint  is null
   set @old_is_unique_constraint=0
 
 
 
 if @old_indexes_columns is null or  @old_indexes_columns=''
   begin
      set @old_indexes_columns=''
   end     
 
 if @old_type_desc is null or @old_type_desc=''
   set  @old_type_desc ='nonclustered'
 
 
 
 return
go
/****** object:  storedprocedure [dbo].[dbobject_foreigngettablecolumns]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure  [dbo].[dbobject_foreigngettablecolumns]
 @foreign_name nvarchar(1000),
 @drop_add int,
 @table_name nvarchar(300) output,
 @table_columns nvarchar(4000) output,
 @referenced_table_name nvarchar(300) output,
 @referenced_columns nvarchar(4000) output,
 @modify_date datetime output,
 @existflag int output
 
as

---从foreign_name 获得相关的表名 相关表名  相关列   修改日期
set @existflag =0
 
 
  
 select @table_name=object_name(f.parent_object_id) ,
     @referenced_table_name = object_name(f.referenced_object_id),
     @modify_date=f.modify_date,
     @existflag=1
  from sys.foreign_keys f
 where name=@foreign_name
 
 
 
 
 set  @table_columns =''
 set  @referenced_columns =''
 
 
 if @drop_add =2
 begin
  execute dbobject_foreigngetcolumns
   @foreign_name =@foreign_name  ,
   @table_columns =@table_columns output ,
   @referenced_columns =@referenced_columns output
   
end
 
 
       return
go
/****** object:  storedprocedure [dbo].[dbobject_defaultvalue]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbobject_defaultvalue]
  @default_value nvarchar(max) output
as

---删除掉第一个'(' 和最后一个')'
 execute  dbobject_defaultclearbracket
    @default_value =@default_value  output

--清除空格
    set @default_value=replace(@default_value,' ',space(0) )

declare @semicolon nchar(1)
set @semicolon=''''

if charindex(@semicolon,@default_value,1)>=1
 begin
      ---set @default_value =''''+''''+replace(@default_value ,@semicolon,''''+'''' ) )
      set @default_value =''''+''''+@default_value +''''+''''
  end
 
else
 begin
      set @default_value =''''+@default_value +''''
  end
 
return
go
/****** object:  storedprocedure [dbo].[dbobject_autodefaultdrops教程ql]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure   [dbo].[dbobject_autodefaultdropsql]
   @default_check int,
   @default_name nvarchar(500),
   @table_name  nvarchar(300) ,
   @column_name nvarchar(300)  ,
   @default_value nvarchar(4000),
   @modify_date datetime ,
   @executeflag int
 as
 
 ---智能删除删除缺值对象
 
  declare @defaultsql nvarchar(max)
 
 declare @old_table_name  nvarchar(300) ,
   @old_column_name nvarchar(300)  ,
   @old_default_value nvarchar(4000),
   @old_modify_date datetime ,
   @old_executeflag int
 
 
--获得相关的表名与列名
   execute   dbobject_defaultgettablename
       @default_check =@default_check ,
       @default_name = @default_name ,
       @table_name  = @old_table_name  output,
       @column_name = @old_column_name output ,
       @default_value = @old_default_value output,
       @modify_date  =@old_modify_date output,
       @existflag  = @old_executeflag output
   
 if @old_executeflag=1
   set @defaultsql=' alter table '+ @old_table_name  + ' drop constraint ' + @default_name

if @executeflag=1
   execute sp_executesql    @defaultsql

else
    print @defaultsql
 

 
 
 
 ---exec  dboject_autodefaultaddsql 'df_planorder2_released_billquantity','planorder2','released_billquantity','((0))','2011-03-15 15:55:16.040',1
go
/****** object:  storedprocedure [dbo].[dbobject_outputcreatetable]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure   [dbo].[dbobject_outputcreatetable]
    @table_name nvarchar(300),
    @columnsetflag int  ,
    @createtablesql nvarchar(max) output
    
as

 --- 自动输出建立表的语句  
  declare  @exists_sql  nvarchar(500)
  declare  @column_name varchar(100)
  declare  @datatype varchar(100)
   
   declare  @is_computed int,
     @is_nullable int,
     @is_identity int
  
declare @li_continue int  
  
declare  @computer_definition nvarchar(max)
declare  @identity_sql nvarchar(1000)

declare @columnsql nvarchar(max)

 

---------设置换行符号----------------------
declare @is_newline nchar(2)
select @is_newline =newline from dbgo
if @is_newline is null or @is_newline=''
 begin
     set @is_newline=char(13)+char(10)
 end
-----------------------------------------------

set @createtablesql='     create table ' + @table_name  +  @is_newline + ' ( ' + @is_newline

---------------------------
 declare @my_cursor  cursor
 
 
 
 
 
    
   set @my_cursor=cursor for 
    select   a.name ,
    (type_name(a.system_type_id) +
     case when a.system_type_id in (167,175) then '(' + (case a.max_length when -1 then 'max' else rtrim(a.max_length) end) + ')'
     when a.system_type_id in (231,239) then '(' + (case a.max_length when -1 then 'max' else rtrim(a.max_length / 2) end) + ')'
     when a.system_type_id in (106,108) then '(' + rtrim(a.precision) + ',' + rtrim(a.scale) + ')'
     else '' end  ) as datatype,
     a.is_computed,
     a.is_nullable ,
     a.is_identity
    from sys.columns a where object_id = object_id(@table_name)
    order by column_id
 
    open @my_cursor 
    fetch  from @my_cursor into  @column_name , @datatype, @is_computed , @is_nullable ,@is_identity

-------------------------
while  @@fetch_status = 0
   begin
    --select @column_name , @datatype, @is_computed , @is_nullable ,@is_identity
    
 set @li_continue=0
  if @columnsetflag >=1
    begin
     ---已删除掉的列不出现
     if exists (select * from    pbcatcol b where b.pbc_tnam=@table_name and b.pbc_cnam=@column_name   and isnull(b.deleteflag,0) =1 )
     begin
        set @li_continue=1
     end     
    
     --强制必须有登记在pbccatcol里面的列才进入系统
     if @columnsetflag =1 
      begin
        if not  exists (select * from    pbcatcol b where b.pbc_tnam=@table_name and b.pbc_cnam=@column_name  )
         begin
             set @li_continue=1
         end
       end
  
    
 
    if @li_continue=1
       begin
         fetch  from @my_cursor into  @column_name , @datatype, @is_computed , @is_nullable ,@is_identity
         continue
     end      
  end   
 
 
 
 
 
 
      set  @computer_definition =''
      set  @identity_sql=''
     
    if @is_computed=1
     begin
       select @computer_definition  =' as ' + definition + case c.is_persisted when '1' then ' persisted' else '' end
            from sys.computed_columns  c where c.name =  @column_name  and c.object_id = object_id( @table_name)
     end
   
     if @is_identity =1
     begin
       select top 1  @identity_sql = ' identity(' + cast(e.seed_value as varchar(10)) + ',' + cast(e.increment_value as varchar(10)) + ')'
         from sys.identity_columns e where e.name = @column_name and  e.object_id = object_id( @table_name)
     end

    if   @computer_definition  is null
          set  @computer_definition =' '
 
    if @identity_sql is null
          set  @identity_sql =' '
      
    set @columnsql=''
    if @is_computed=1
     set @columnsql=@column_name + '  ' +  @computer_definition   +  ( case @is_nullable when 0 then ' not null'    else   ' null'  end  )  +  @identity_sql
    else
     set @columnsql=@column_name + '  ' + @datatype  +  ( case @is_nullable when 0 then ' not null'    else   ' null'  end  )  + @identity_sql
    
 
  ------if @createtablesql is null or  @createtablesql=''
  ------    set @createtablesql=  '      ' +@columnsql +','+@is_newline
  ------else
      set @createtablesql= @createtablesql +  '      ' +  @columnsql  +','+@is_newline
   fetch  from @my_cursor into  @column_name , @datatype, @is_computed , @is_nullable ,@is_identity
 
end
 
   close @my_cursor
 deallocate @my_cursor
 
----------------------------------输出到前台--------------------------------------------------------------------
  ---去掉','+@is_newline
 set @createtablesql= stuff(@createtablesql,  len(@createtablesql) - 2 ,2,'')
 set @createtablesql= @createtablesql +@is_newline+'      )'
 
 
 ------
 ------ ---print @createtablesql
 ------set @exists_sql = ' if  not  exists (select * from ' + 'sys.objects where name =' +''''+ @table_name +''''+' and type =' + ''''+ 'u' +''''+' ) '
 ------set @createtablesql =@exists_sql+@is_newline+'   begin '  +@is_newline +@createtablesql +@is_newline+'   end '+@is_newline

-----------------------------------------------------------------------------------------------------------------

 

 

 

--------
 return
go
/****** object:  storedprocedure [dbo].[dbobject_outputtableindexes]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure  [dbo].[dbobject_outputtableindexes]
   @table_name nvarchar(500),
   @indexes_name nvarchar(1000) ,
   @drop_add int ,
   @replaceflag int,
   @executeflag int ,
   @createindexessql  nvarchar(max) output

  
as
 --自动输出建立索引对象的程序代码
 if @table_name is null or @table_name =''
   begin
      --得到表名称
       select  @table_name=object_name(object_id)
        from  sys.indexes  i
        where  (i.name=(@indexes_name) )
   end

 

--- declare @proce_name nvarchar(1000)
 declare @indexes_columns  nvarchar(4000)
 declare @type_desc nvarchar(100) 
 
  declare @is_unique int,
   @is_primary_key int,
   @is_unique_constraint int

declare @modify_date datetime
declare @modify_date_str nvarchar(30)

 ----object_name(object_id) as table_name, name,
  
 select  @modify_date=b.modify_date  from   sys.objects   b where  b.name= @indexes_name
 

  
   select @type_desc =type_desc,@is_unique =abs(is_unique) ,@is_primary_key =abs(is_primary_key),@is_unique_constraint = abs(is_unique_constraint)
     from  sys.indexes  i
     where   (object_id = object_id(@table_name) )
     and (i.name=(@indexes_name) )
     
     
     
     set @indexes_columns=''
     
  if @drop_add = 2
      begin
          ---返回某个表某个索引的列名称组
         exec dbobject_tablegetindexcolumns
           @table_name =@table_name,
           @indexes_name =@indexes_name,
           @indexes_columns  =@indexes_columns  output
      end     
  

  if @is_unique is null
 set @is_unique=0
 
 if   @is_primary_key  is null
  set @is_primary_key=0
 
 if   @is_unique_constraint  is null
   set @is_unique_constraint=0
 
 
 
 if @indexes_columns is null or  @indexes_columns=''
   begin
      set @createindexessql=''
      if @drop_add= 0
      begin
        return
      end
   end     
 
 if @type_desc is null or @type_desc=''
   set  @type_desc ='nonclustered'
 
 
 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 @createindexessql= '     exec dbobject_autotabledropindexes  ' +''''+@table_name+''''+ ',' + ''''+ @indexes_name +''''+ ',' + ''''+ @indexes_columns +''''+','+ ''''+@type_desc+''''+','+
           convert(nvarchar(30), @is_unique )+','+ convert(nvarchar(30), @is_primary_key )+','+convert(nvarchar(30),  @is_unique_constraint )+','+''''+@modify_date_str+''''+','+convert(nvarchar(30), @executeflag )
      
    else
         set @createindexessql= '     exec dbobject_autotableaddindexes  ' +''''+@table_name+''''+ ',' + ''''+ @indexes_name +''''+ ','+ ''''+ @indexes_columns +''''+',' + ''''+@type_desc+''''+','+
         convert(nvarchar(30), @is_unique )+','+ convert(nvarchar(30), @is_primary_key )+','+convert(nvarchar(30),  @is_unique_constraint )+','+''''+@modify_date_str+''''+','+convert(nvarchar(30), @executeflag )
 
 
 
 
 return

 

----
----begin transaction
----go
----alter table dbo.account  drop constraint df_account_name
----go
----alter table dbo.account add constraint  df_account_name default ( ' 1234' ) for name
----go
----alter table dbo.account set (lock_escalation = table)
----go
----commit
----
----
----drop index ix_abc on dbo.abc
----go
----alter table dbo.abc
---- drop constraint pk_abc

----
----
-------普通索引
----create nonclustered index ix_abc on dbo.abc
---- (
---- name
---- ) with( statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
----go
-------唯一索引
----create unique nonclustered index ix_abc_id on dbo.abc
---- (
---- id
---- ) with( statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
----go
----alter table dbo.abc set (lock_escalation = table)
----go
----commit
--------
go
/****** object:  storedprocedure [dbo].[dbgo_printsql]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbgo_printsql]
   @objectsql nvarchar(max)
as
--输出加---char(10)+char(13)+'go'+char(10)+char(13) )
if @objectsql is null or  @objectsql=''
      return

 

declare @is_go nvarchar(20)
select @is_go=id from dbgo
set  @objectsql =@is_go + @objectsql +@is_go
print @objectsql
go
/****** object:  storedprocedure [dbo].[dbgo_outputsql]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbgo_outputsql]
   @objectsql nvarchar(max) output
 as

--输出加---char(10)+char(13)+'go'+char(10)+char(13) )
   if @objectsql is null or  @objectsql=''
      return

 

declare @is_go nvarchar(20)
select @is_go=id from dbgo

if @is_go is null or @is_go=''
 begin
    set @is_go=char(13)+char(10)+'go'+char(13)+char(10)
 end

set  @objectsql = @is_go + @objectsql +@is_go

return

--declare @objectsql nvarchar(max)  ,
--  objectsql nvarchar(max)
  
--  set @objectsql ='select * from product'
  
--execute  dbgo_outputsql
--   @objectsql =@objectsql ,
--   objectsql= objectsqloutput
--print @outputsql
go
/****** object:  storedprocedure [dbo].[dbobject_tableindexes]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create   procedure [dbo].[dbobject_tableindexes]
   @table_name nvarchar(1024),
   @column_name nvarchar(300),
   @object_name nvarchar(300),
   @addgo int ,
   @drop_add int,
   @replaceflag int,
   @executeflag int,
   @objectsql nvarchar(max) output ,
   @execute_output int
as

  -- @table_column_object int,
  -- @table_column_object 参数方式  1.table  2.column 3.default   12-21 table+column  (任意组合)
  --  @execute_output int 是否执行  还是输出

declare  @ls_table_name nvarchar(1024),
   @ls_column_name nvarchar(300),
   @ls_object_name nvarchar(300)
   ---convert(varchar(8000), d.definition ) as default_value,   
  
declare @column_id int
declare @modify_date datetime
 

declare @ls_objectsql nvarchar(max)

---------------设置换行符号----------------------
------declare @is_newline nchar(2)
------select @is_newline =newline from dbgo
------if @is_newline is null or @is_newline=''
------ begin
------     set @is_newline=char(13)+char(10)
------ end

   if @table_name is null
    set @table_name=''
   if @column_name  is null
    set @column_name=''
   if  @object_name is null
    set @object_name=''

 

set @objectsql=''

---------------------------
 declare @my_cursor  cursor
 
  if  not ( @column_name  is null or @column_name='' )
   begin
       set @my_cursor=cursor for   select
       object_name(i.object_id) ,  i.name
       from sys.columns a ,sys.index_columns b , sys.indexes i
         where a.object_id = b.object_id and
        a.column_id = b.column_id  and
        b.object_id = i.object_id and
        b.index_id = i.index_id
        and  ( @table_name='' or  object_name(i.object_id)=@table_name )
        and  ( @object_name='' or  i.name=@object_name )
        and   a.object_id = i.object_id
        and   a.name=@column_name 
         group by object_name(i.object_id) , i.name
         order by object_name(i.object_id) , i.name
    end    
  else
   begin
    set @my_cursor=cursor for   select
       object_name(i.object_id) ,i.name 
       from   sys.indexes  i
           where    ( @table_name='' or object_id = object_id(@table_name) )
        and  ( @object_name='' or  i.name=@object_name )    
        order by  1,2
  end
   
    open @my_cursor 
    fetch  from @my_cursor into @ls_table_name,@ls_object_name
  
  
-------------------------
while  @@fetch_status = 0
 begin
 
 set @ls_objectsql=''
 
      --输出的是立即可执行代码
      if @execute_output=1  and @drop_add=1
        begin
            set @executeflag=1
        end
   
   
   set @ls_objectsql=''
   
   ---开始循环输出[生成索引对象]存储过程
    execute  dbobject_outputtableindexes @table_name =@ls_table_name,@indexes_name  =@ls_object_name,@drop_add =@drop_add ,@replaceflag =@replaceflag ,@executeflag =@executeflag ,@createindexessql =@ls_objectsql output
 
   ---立即执行删除操作 
      if @execute_output=1  and @drop_add=1
         begin
            execute sp_executesql  @ls_objectsql
         end
         
     if @addgo=1
        begin
          --添加go
           execute dbgo_outputsql  @objectsql=@ls_objectsql output
        end
 
       if @objectsql=''
        set @objectsql=@ls_objectsql
       else
        set @objectsql=@objectsql + @ls_objectsql
        
   fetch  from @my_cursor into @ls_table_name,@ls_object_name
 end
 
   close @my_cursor
 deallocate @my_cursor
 

 

return
go
/****** object:  storedprocedure [dbo].[dbobject_autoaltertableaddcolumn]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure [dbo].[dbobject_autoaltertableaddcolumn]
      @table_name nvarchar(200),
      @column_name nvarchar(200) ,
      @datatype nvarchar(200) ,
      @is_computed int,
      @is_nullable int,
      @is_identity int ,
      @existdefault int,
      @default_definition nvarchar(max),
      @computer_definition nvarchar(max),
      @identity_sql nvarchar(1000),
      @executeflag int  
 as
 
 
 --智能功能-表自动添加列以及缺省数值
 declare @columnsql nvarchar(max)

  if @default_definition is null or  @default_definition=''
     set @default_definition =''
  else
      if @existdefault=1
       begin
         set  @default_definition =' default ' + @default_definition
       end

  if @is_computed=1
     set @columnsql=@column_name + '  ' +  @computer_definition   +  ( case @is_nullable when 0 then ' not null'    else   ' null'  end  )  +  @identity_sql   +  @default_definition
    else
     set @columnsql=@column_name + '  ' + @datatype  +  ( case @is_nullable when 0 then ' not null'    else   ' null'  end  )  + @identity_sql  + @default_definition
    
      set @columnsql = ' alter  table ' +@table_name + ' add ' + @columnsql
    
  
----立即执行
  if @executeflag =1
     execute sp_executesql @columnsql  
  else
     execute dbgo_printsql  @columnsql
  
     
 return
go
/****** object:  storedprocedure [dbo].[dbobject_autocreatetable]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure  [dbo].[dbobject_autocreatetable]
 @table_name nvarchar(300),
 @executeflag int,
 @createtablesql nvarchar(max)
as

---自动建立表
 if  not  exists (select * from sys.objects where name = @table_name and type ='u' )
 begin
       if @executeflag=1
               execute sp_executesql   @createtablesql

       else
               execute dbgo_printsql @createtablesql
 end
 
 
return  

---
go
/****** object:  storedprocedure [dbo].[dbobject_autotabledropforeign]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbobject_autotabledropforeign]
   @foreign_name nvarchar(1000) ,
    @table_name nvarchar(300),
     @table_columns nvarchar(4000) ,
   @referenced_table_name nvarchar(300),
   @referenced_columns nvarchar(4000),
   @modify_date datetime ,
   @executeflag int
  
as

declare  @objectsql nvarchar(max)
 

 declare  @old_table_name nvarchar(300),
      @old_table_columns nvarchar(4000) ,
    @old_referenced_table_name nvarchar(300),
    @old_referenced_columns nvarchar(4000),
    @old_modify_date datetime ,
    @existflag int

 
--获得相关的表名与列名
  ---从foreign_name 获得相关的表名 相关表名  相关列   修改日期
    exec  dbobject_foreigngettablecolumns
         @foreign_name =@foreign_name,
         @drop_add = 1 ,
         @table_name =@old_table_name output,
         @table_columns =@old_table_columns  output,
         @referenced_table_name=@old_referenced_table_name output,
         @referenced_columns  =@old_referenced_columns  output,
         @modify_date=@old_modify_date output,
         @existflag =@existflag  output
 
 if   @existflag=1
      set @objectsql  =' alter  table ' +  @old_table_name + '   drop constraint  '  +  @foreign_name

 

if @executeflag=1
   execute sp_executesql   @objectsql

else
   execute dbgo_printsql @objectsql
go
/****** object:  storedprocedure [dbo].[dbobject_autotableaddindexes]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure [dbo].[dbobject_autotableaddindexes]
   @table_name nvarchar(500),
   @indexes_name nvarchar(800) ,
   @indexes_columns  nvarchar(4000) ,
   @type_desc nvarchar(100), 
   @is_unique int,
   @is_primary_key int,
   @is_unique_constraint int  ,
    @modify_date datetime,
    @executeflag int
as

---自动加上索引对象  自动处理 @executeflag=1  立即执行

  declare  @old_indexes_columns  nvarchar(4000)  ,
   @old_type_desc nvarchar(100) ,
   @old_is_unique int , 
   @old_is_primary_key int ,
   @old_is_unique_constraint int 
 

--------------------------------------------键和索引的创建方法不一样-----------------------------------------------------------------------------
 declare @exists_sql nvarchar(4000)
 declare @addindexes_sql nvarchar(4000)
 
 
  
 declare   @old_indexes_name nvarchar(500) ,
     @new_type_desc nvarchar(200 )
  
 declare @li_existsflag int
 declare @li_needexecute int
 
    set @li_needexecute=0
    set @li_existsflag=0
  
declare @unique_key   nvarchar(200)
  set @unique_key='  '
 
 if @is_primary_key=1
      begin
          if    exists (select * from sys.objects  where  name=@indexes_name )
                 set @li_existsflag=1
      end
 else   ----唯一键
      begin
           if @is_unique_constraint = 1
               begin
                    if    exists (select * from sys.objects  where  name=@indexes_name )
                        set @li_existsflag=1
               end 
          else
               begin
                   if   exists (select * from sys.indexes  where  object_id =object_id (@table_name) and name=@indexes_name )
                            set @li_existsflag=1
               end
      end
   

 
 if  @li_existsflag=1
    begin
           exec dbobject_gettableindexesvalues
                @table_name ,
                @indexes_name ,
                @old_indexes_columns  output ,
                @old_type_desc  output,
                @old_is_unique output, 
                @old_is_primary_key output,
                @old_is_unique_constraint  output
               
  if   (@old_indexes_columns<>@indexes_columns or  @old_type_desc <> @type_desc or @old_is_unique<> @is_unique or 
        @old_is_primary_key <>@is_primary_key  or  @old_is_unique_constraint <> @is_unique_constraint)
             begin        
                set @li_needexecute=1
             end
    end
else
           --不存在必须加入
                set @li_needexecute=1  

 

       
---需要自动删除掉主关键字  或主索引  ---------比较特殊的东西-----------------------
 if @is_primary_key=1
   begin
       if  @li_existsflag=0
            begin
                 exec   dbobject_tableprimaryname
                      @table_name=@table_name ,
                      @indexes_name = @old_indexes_name  output ,
                      @type_desc  = @new_type_desc output
                 
                
                 
                if not ( @old_indexes_name    is null or @old_indexes_name ='' )
                  begin
                     exec  dbobject_autotabledropindexes
                      @table_name ,
                      @old_indexes_name ,
                      @old_indexes_columns ,
                      @new_type_desc , 
                      @old_is_unique ,
                      1 ,
                      @old_is_unique_constraint   ,
                       @modify_date  ,
                       @executeflag
                  end
          set @li_needexecute=1
     end
   end

 --else   ----唯一键
 --   if @is_unique_constraint = 1       
   
 --   end

 

  --是否需要执行更新语句      
       if @li_needexecute=0
                  return

if  @li_existsflag=1
       begin
          exec  dbobject_autotabledropindexes
             @table_name ,
             @indexes_name ,
             @old_indexes_columns ,
             @old_type_desc , 
             @old_is_unique ,
             @old_is_primary_key ,
             @old_is_unique_constraint   ,
              @modify_date ,
              @executeflag
       end
       
       

--declare @modify_date_sql nvarchar(300)
--   set @modify_date_sql =' modify_date>'+''''+ @modify_date_sql +''''

 

 set @indexes_columns = ' ( ' + @indexes_columns  +' ) '

 if @is_primary_key=1
      begin
          ---if not  exists (select * from sys.objects  where  name=@indexes_name )
              set @addindexes_sql=' alter  table ' +@table_name + '   add constraint  ' +@indexes_name + ' primary key  ' + @type_desc  +' '+   @indexes_columns + ' with( statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on)  on [primary] '
      end
else   ----唯一键
      begin
           if @is_unique_constraint = 1
               begin
                  --  if  not exists (select * from sys.objects  where  name=@indexes_name )
                         set @addindexes_sql=' alter  table ' +@table_name + '   add constraint  ' +@indexes_name + ' unique  ' + @type_desc  + ' ' + @indexes_columns + ' with( statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on)  on [primary] '
               end 
          else
               begin
                   ---唯一索引
                    if  @is_unique =1
                        set @unique_key=' unique '
                
                 
                   ---if   not exists (select * from sys.indexes  where  object_id =object_id (@table_name) and name=@indexes_name )
                         set @addindexes_sql=' create ' + @unique_key  +    @type_desc + ' index ' +  @indexes_name +  ' on '+  @table_name   +  @indexes_columns   + ' with( statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] '
               end
      end
   
   
      begin
                   
          if @executeflag = 1
             begin
               if not ( @addindexes_sql is null or  @addindexes_sql='')
                 execute sp_executesql @addindexes_sql
             end
          else
                 execute  dbgo_printsql  @addindexes_sql
      end

return
--
--begin transaction
--go
--alter table dbo.account  drop constraint df_account_name
--go
--alter table dbo.account add constraint  df_account_name default ( ' 1234' ) for name
--go
--alter table dbo.account set (lock_escalation = table)
--go
--commit
--
--
--drop index ix_abc on dbo.abc
--go
--alter table dbo.abc
-- drop constraint pk_abc

--
--
-----普通索引
--create nonclustered index ix_abc on dbo.abc
-- (
-- name
-- ) with( statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
--go
-----唯一索引
--create unique nonclustered index ix_abc_id on dbo.abc
-- (
-- id
-- ) with( statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
--go
--alter table dbo.abc set (lock_escalation = table)
--go
--commit
----
go
/****** object:  storedprocedure [dbo].[dbobject_outputtablecolumnaltersql]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure   [dbo].[dbobject_outputtablecolumnaltersql]
  @table_name nvarchar(300),
  @column_name nvarchar(500),
  @columnsetflag int  ,
  @executeflag int,
  @altertablesql nvarchar(max) output
as

--- 自动输出建立某个表某一列的[修改存储过程]的语句  
 declare @datatype varchar(100) 
 declare @is_computed int,
   @is_nullable int,
   @is_identity int

declare @default_definition  nvarchar(max)
declare  @computer_definition nvarchar(max)
declare  @identity_sql nvarchar(1000)
  

-----------设置换行符号----------------------
--declare @is_newline nchar(2)
--select @is_newline =newline from dbgo
--if @is_newline is null or @is_newline=''
-- begin
--     set @is_newline=char(13)+char(10)
-- end
-----------------------------------------------
  execute dbobject_tablecolumngetvalues
       @table_name = @table_name,
       @column_name = @column_name  ,
       @datatype= @datatype  output ,
       @is_computed=@is_computed  output,
       @is_nullable =@is_nullable  output,
       @is_identity  =@is_identity output ,
       @default_definition = @default_definition  output ,
       @computer_definition  =@computer_definition output ,
       @identity_sql =@identity_sql  output

 

declare @default_name nvarchar(1000)
declare @modify_date datetime
declare @existdefault int

   execute  dbobject_tablecolumngetdefaultname
   @default_check =1,
   @default_name =@default_name output,
   @table_name =@table_name ,
   @column_name =@column_name ,
   @default_value =@default_definition  output,
   @modify_date =@modify_date  output,
   @existflag =@existdefault  output

 

 if @existdefault=1
   begin
     ---处理一下default_values
       execute dbobject_defaultvalue @default_value=@default_definition output
   end

 if @default_definition is null
      set @default_definition=+''''+space(0)+''''
    

 

set @altertablesql = ' execute '+ 'dbobject_autoaltertable '+'''' + @table_name +''''+',' +
      '''' +  @column_name +''''+',' +
      '''' +  @datatype +''''+',' +
        convert(nvarchar(20),@is_computed ) +',' +
        convert(nvarchar(20),@is_nullable )+ ',' +
        convert(nvarchar(20), @is_identity ) +',' +
        convert(nvarchar(20), @existdefault )  +',' +
      @default_definition +',' +
      '''' +  @computer_definition +''''+',' +
      '''' +   @identity_sql +''''+',' +
       convert(nvarchar(20), @executeflag )

return

 

 return
go
/****** object:  storedprocedure [dbo].[dbobject_outputsqlmodules]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure  [dbo].[dbobject_outputsqlmodules]
      @object_name nvarchar(1000),
      @object_type  nvarchar(2),
      @drop_add int,
      @replaceflag int,
      @executeflag int,
      @objectsql  nvarchar(max) output
as

declare @dropsql nvarchar(max)

if @object_type is null or @object_type=''
 begin
       select @object_type=type from sys.objects where name=@object_name 
 end

declare @modify_date datetime
--获得修改日期
 select @modify_date=modify_date from sys.objects where name=@object_name 

 

 

declare @procename nvarchar(500)
  set @procename=' dbobject_autodropsqlmodules '
   
  ----if  @drop_add=1
  ---- begin
  
  ---- end
  ----else
  ---- begin
  ----   set @procename=' dbobject_autoaddsqlmodules '
  ---- end
  
 if @modify_date is null
     set @modify_date=getdate()
  
  
--强制替换
   if @replaceflag = 3
     begin
          set @modify_date = getdate() + 2000
     end

       set @dropsql  =' execute ' +@procename +
            ''''+@object_name +''''+','+
            ''''+convert(nvarchar(2),@object_type  )+''''+','+
            ''''+convert(nvarchar(23),@modify_date ,121) +''''+','+
            convert(nvarchar(28),@executeflag )

    if @drop_add =1
       begin
         set @objectsql = @dropsql 
       end
    else
      begin
        ---sys.all_sql_modules  
         select @objectsql=definition from sys.sql_modules where object_id = object_id(@object_name  )
         if @objectsql  is null
             set @objectsql=''
          --加上go
          execute dbgo_outputsql @objectsql =@dropsql output
          execute dbgo_outputsql @objectsql =@objectsql output
           set @objectsql= @dropsql  +@objectsql
      end
return
    
    
-- begin transaction
--go
--alter table dbo.account add constraint  df_account_name default '2001' for name
--go
--alter table dbo.account set (lock_escalation = table)
--go
--commit
go
/****** object:  storedprocedure [dbo].[dbobject_outputdefaultsql]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create   procedure  [dbo].[dbobject_outputdefaultsql]
      @default_check int,
      @default_name nvarchar(1000),
      @drop_add int,
      @replaceflag int,
      @executeflag int,
      @defaultsql  nvarchar(max) output
as

   declare  @table_name  nvarchar(300) ,
   @column_name nvarchar(300)  ,
   @default_value nvarchar(4000) ,
   @modify_date datetime ,
   @existflag int
   

--获得相关的表名与列名
    exec   dbobject_defaultgettablename
       @default_check ,
       @default_name ,
       @table_name   output,
       @column_name  output ,
       @default_value output,
       @modify_date  output,
       @existflag output
   
declare @procename nvarchar(500)
   
   
  if  @drop_add=1
   begin
     set @procename=' dbobject_autodefaultdropsql '
   end
  else
   begin
     set @procename=' dbobject_autodefaultaddsql '
   end
  
  
---处理一下default_values
execute dbobject_defaultvalue @default_value=@default_value output

-------删除掉第一个'(' 和最后一个')'
---- execute  dbobject_defaultclearbracket
----    @default_value =@default_value  output

------清除空格
----    set @default_value=replace(@default_value,' ',space(0) )

----declare @semicolon nchar(1)
----set @semicolon=''''

----if charindex(@semicolon,@default_value,1)>=1
---- begin
----      ---set @default_value =''''+''''+replace(@default_value ,@semicolon,''''+'''' ) )
----      set @default_value =''''+''''+@default_value +''''+''''
----  end
 
----else
---- begin
----         set @default_value =''''+@default_value +''''
----  end

 

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

 

  if @existflag=1
   begin
      set @defaultsql  =' execute ' +@procename +
           convert(nvarchar(2),@default_check )+','+
        ''''+@default_name +''''+','+
        ''''+@table_name  +''''+','+
        ''''+@column_name +''''+','+
        @default_value +','+
        ''''+convert(nvarchar(23),@modify_date ,121) +''''+','+
         convert(nvarchar(28),@executeflag )
    end
     
   
   
       
return
    
    
-- begin transaction
--go
--alter table dbo.account add constraint  df_account_name default '2001' for name
--go
--alter table dbo.account set (lock_escalation = table)
--go
--commit
go
/****** object:  storedprocedure [dbo].[dbobject_dropsqlmodules]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbobject_dropsqlmodules]
 @object_name nvarchar(1000),
 @object_type nvarchar(2) ,
 @executeflag int
as

--if exists (select * from sys.objects where object_id = object_id('+"'"+as_object_name+"'"+') '+   ls_modify_date_sql + " ) "  + '~r~n'  +' begin '+'~r~n'
declare @dropsql nvarchar(1000)

if @object_type= 'p' or @object_type='x'
  set @dropsql   =' drop procedure '
else if @object_type= 'v'
 set @dropsql =' drop view '
else if @object_type= 'tr'
 set @dropsql =' drop trigger '
else if @object_type =  'fn' or @object_type = 'fs'  or @object_type =  'ft'  or @object_type =  'if'  or @object_type = 'tf'
 set @dropsql =' drop function ' 
 
 
set @dropsql = @dropsql  + ' '+@object_name

if @executeflag=1
   execute sp_executesql    @dropsql

else
   execute dbgo_printsql  @dropsql
go
/****** object:  storedprocedure [dbo].[dbobject_dropforeignname]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure   [dbo].[dbobject_dropforeignname]   
   @foreign_name nvarchar(500),
   @table_name  nvarchar(300),
   @executeflag int
 as
 
 ---强制删除foreign对象
     
declare @foreignsql nvarchar(4000)
     
 if  (@table_name is null or @table_name='' )
    begin
      select  @table_name = object_name(d.parent_object_id)  from sys.foreign_keys d  where ( d.name=@foreign_name )
   end
     

 set @foreignsql=' alter  table '+ @table_name  + ' drop constraint ' + @foreign_name

if @executeflag=1
   execute sp_executesql    @foreignsql

else
   execute dbgo_printsql  @foreignsql
 

 
 
 
 ---exec  dboject_auto@foreignaddsql 'df_planorder2_released_billquantity','planorder2','released_billquantity','((0))','2011-03-15 15:55:16.040',1
go
/****** object:  storedprocedure [dbo].[dbobject_autodefaultaddsql]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure   [dbo].[dbobject_autodefaultaddsql]
      @default_check int,
   @default_name nvarchar(500),
   @table_name  nvarchar(300) ,
   @column_name nvarchar(300)  ,
   @default_value nvarchar(4000),
   @modify_date datetime ,
   @executeflag int
 as
 
 ---智能删除删除缺值对象
 declare @li_needexecute int
  declare @defaultsql nvarchar(max)
 
   declare @old_default_name nvarchar(500)
 
 declare @old_table_name  nvarchar(300) ,
   @old_column_name nvarchar(300)  ,
   @old_default_value nvarchar(4000),
   @old_modify_date datetime ,
   @existflag int
 
  set @li_needexecute  = 0
 
  declare @havedeleteflag int
  set @havedeleteflag=0
 
 --501010-此缺省对象是否是不相同表名或列名
--获得相关的表名与列名
    execute  dbobject_defaultgettablename
       @default_check =@default_check ,
       @default_name = @default_name ,
       @table_name  = @old_table_name  output,
       @column_name = @old_column_name output ,
       @default_value = @old_default_value output,
       @modify_date  =@old_modify_date output,
       @existflag  = @existflag output
   
   
 if @existflag=1
   begin
     ----[501010]不相同的表名或列名 强制删除
       if @table_name<>@old_table_name or @column_name<>@old_column_name
        begin
                 execute dbobject_dropdefaultname @default_check ,default_name ,@old_table_name,@executeflag
                 set @li_needexecute=1
        end
       else
           begin
           if @modify_date>@old_modify_date
            begin
               ----[501020] 相同的表名或列名 比较日期是否大于目的对象日期
                 
                    execute dbobject_dropdefaultname @default_check ,@default_name ,@table_name,@executeflag
                    set @havedeleteflag=1
                    set @li_needexecute=1
                  
            end
           end  
   end
 else
         ----[503030] 缺省对象名称不存在
   begin
           set @li_needexecute=1
   end
   
     
   
     

  set @old_default_value =''
  set @old_modify_date =getdate()
  set @existflag = 0

 --获得相关的表名与列名 对应的缺省对象名称
    execute   dbobject_tablecolumngetdefaultname
       @default_check =@default_check ,
       @default_name = @old_default_name output ,
       @table_name  = @table_name  ,
       @column_name = @column_name  ,
       @default_value = @old_default_value output,
       @modify_date  =@old_modify_date output,
       @existflag  = @existflag output   
   
   
  if @existflag=1  
  begin
    ----[502010] 缺省对象名称不相同
     if @default_name<>@old_default_name
       begin
           ---[502010] 缺省对象名称不相同
            execute dbobject_dropdefaultname @default_check , @old_default_name ,@table_name,@executeflag
            set @li_needexecute=1
       end
    else
          begin
          if @modify_date>=@old_modify_date
           begin
              ----[502020] 相同的表名或列名 比较日期是否大于目的对象日期
                if  @havedeleteflag=0
                  begin
                    execute dbobject_dropdefaultname @default_check , @default_name ,@table_name,@executeflag
                     set @havedeleteflag=1
                  end  
                     set @li_needexecute=1 
           end
          end 
   end  
 else
     ----[503030] 缺省对象名称不存在
    begin
            set @li_needexecute=1
    end
  
       
   
   
   
    if @li_needexecute=0
        return
  
  
if @default_check=1
  begin
   if @default_value  is null or @default_value=space(0) or @default_value=''''
  begin
      set @default_value='space(0)'
  end
    
   
  set @defaultsql=' alter table '+ @table_name  + ' add constraint ' + @default_name + ' default '+@default_value  +' for ' + @column_name
     if @executeflag=1
        execute sp_executesql    @defaultsql

     else
        execute dbgo_printsql @defaultsql
 
 end
 
 else
 begin
  set @defaultsql=' alter table '+ @table_name  +' with nocheck ' +  ' add constraint ' + @default_name + ' check '+' ( ' + @default_value  +' ) '
   ---+' for ' + @column_name
  
     if @executeflag=1
        execute sp_executesql    @defaultsql

     else
        execute dbgo_printsql @defaultsql
 
 
 end
return

 
 
 
 ---exec  dboject_autodefaultaddsql 'df_planorder2_released_billquantity','planorder2','released_billquantity','((0))','2011-03-15 15:55:16.040',1
 
---- create table dbo.doc_exz ( column_a int, column_b int) ;
----go
----insert into dbo.doc_exz (column_a)values ( 7 ) ;
----go
----alter table dbo.doc_exz
----add constraint col_b_def
----default 50 for column_b ;
----go
----insert into dbo.doc_exz (column_a) values ( 10 ) ;
----go
----select * from dbo.doc_exz ;
----go
----drop table dbo.doc_exz ;
----go
go
/****** object:  storedprocedure [dbo].[dbobject_autotableaddforeign]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbobject_autotableaddforeign]
   @foreign_name nvarchar(1000) ,
    @table_name nvarchar(300),
     @table_columns nvarchar(4000) ,
   @referenced_table_name nvarchar(300),
   @referenced_columns nvarchar(4000),
   @modify_date datetime ,
   @executeflag int
  
as

declare  @objectsql nvarchar(max)
 declare @havedeleteflag int
declare @li_needexecute int

 declare  @old_table_name nvarchar(300),
      @old_table_columns nvarchar(4000) ,
    @old_referenced_table_name nvarchar(300),
    @old_referenced_columns nvarchar(4000),
    @old_modify_date datetime ,
    @existflag int

set @havedeleteflag=0
 set  @li_needexecute = 0
--获得相关的表名与列名
  ---从foreign_name 获得相关的表名 相关表名  相关列   修改日期
    exec  dbobject_foreigngettablecolumns
         @foreign_name =@foreign_name,
         @drop_add = 2 ,
         @table_name =@old_table_name output,
         @table_columns =@old_table_columns  output,
         @referenced_table_name=@old_referenced_table_name output,
         @referenced_columns  =@old_referenced_columns  output,
         @modify_date=@old_modify_date output,
         @existflag =@existflag  output

if  @existflag=1
 begin
  ---[600010] 存在任何foreign对象 但看是否相等
  
  if ( @table_name<>@old_table_name or  @table_columns <>@old_table_columns or @referenced_table_name<>@old_referenced_table_name or  @referenced_columns<> @old_referenced_columns )
    begin
  
       ---强制删除旧对象
           execute dbobject_dropforeignname  @foreign_name,  @old_table_name,  @executeflag
           set @li_needexecute = 1
           set @havedeleteflag=1
    end
 end
 else
  begin
     ---[600030] 不存在任何foreign对象
           set @li_needexecute = 1
  end

 

---不执行任何处理
  if @li_needexecute = 0
      return

 
 if   @existflag=1 and @havedeleteflag=1
    begin
      ---强制删除旧对象
       execute dbobject_dropforeignname  @foreign_name,  @old_table_name,  @executeflag
    end

          
----alter table [dbo].[makesendin2]  with nocheck add  constraint [fk_makesendin2_makesendin1] foreign key([id])  references [dbo].[makesendin1] ([id])

   

   set @objectsql=' alter  table ' +@table_name + '  with  nocheck  add constraint  ' +@foreign_name + ' foreign key  ' +  '(' +  @table_columns + ')' +'  references  '  + @referenced_table_name  + ' ' + '('+ @referenced_columns  +')'

if @executeflag=1
   execute sp_executesql   @objectsql

else
   execute dbgo_printsql @objectsql
   
 ---激活外键约束
set @objectsql =' alter  table ' +@table_name + '  check constraint '  + @foreign_name

if @executeflag=1
   execute sp_executesql   @objectsql

else
   execute dbgo_printsql @objectsql
return
go
/****** object:  storedprocedure [dbo].[dbobject_autodropsqlmodules]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure  [dbo].[dbobject_autodropsqlmodules]
      @object_name nvarchar(1000),
      @object_type  nvarchar(2),
      @modify_date datetime,
      @executeflag int
      
as

---智能删除数据库的sqlmodules(代码模块)
declare @dropsql nvarchar(max)
declare @old_modify_date datetime
declare @old_object_type nvarchar(2)
declare @existflag int

set @existflag=0
select @existflag=1,@old_object_type=type ,@old_modify_date=modify_date from sys.objects where name=@object_name 

if @existflag=1
 begin
     --目的数据的日期比较新 不能删除 （要保留）
      if ( @modify_date<=@old_modify_date )
         begin
               return
         end
 end

if @existflag=1
 begin
  execute dbobject_dropsqlmodules @object_name=@object_name ,@object_type=@old_object_type ,@executeflag=@executeflag
 end

return
go
/****** object:  storedprocedure [dbo].[dbobject_outputaltertable]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure   [dbo].[dbobject_outputaltertable]
  @table_name nvarchar(300),
  @columnsetflag int  ,
  @addgo int,
  @executeflag int,
  @altertablesql nvarchar(max) output
as
--- 自动输出建立表的语句  
declare @exists_sql  nvarchar(500)

 

 declare @column_name varchar(100)
 declare @datatype varchar(100) 
 declare @is_computed int,
   @is_nullable int,
   @is_identity int
  
declare @li_continue int  
  
declare  @computer_definition nvarchar(max)
declare  @identity_sql nvarchar(1000)

declare @columnsql nvarchar(max)

 

---------设置换行符号----------------------
declare @is_newline nchar(2)
select @is_newline =newline from dbgo
if @is_newline is null or @is_newline=''
 begin
     set @is_newline=char(13)+char(10)
 end
-----------------------------------------------

--set @altertablesql='     create table ' + @table_name + @is_newline+ '      (' +@is_newline

set @altertablesql=''

---------------------------
 declare @my_cursor  cursor
 
     
   set @my_cursor=cursor for 
    select   a.name
    from sys.columns a where object_id = object_id(@table_name)
    order by column_id
 
    open @my_cursor 
    fetch  from @my_cursor into  @column_name

-------------------------
while  @@fetch_status = 0
   begin
    
    
 set @li_continue=0
  if @columnsetflag >=1
   begin
     ---已删除掉的列不出现
          if exists (select * from    pbcatcol b where b.pbc_tnam=@table_name and b.pbc_cnam=@column_name   and isnull(b.deleteflag,0) =1 )
           begin
                set @li_continue=1
           end
               
          --强制必须有登记在pbccatcol里面的列才进入系统
            if @columnsetflag =1 
             begin
               if not  exists (select * from    pbcatcol b where b.pbc_tnam=@table_name and b.pbc_cnam=@column_name  )
                begin
                   set @li_continue=1
                end
            end
     
            if @li_continue=1
            begin
              fetch  from @my_cursor into  @column_name
              continue
          end      
  end   
 
     execute dbobject_outputtablecolumnaltersql
         @table_name =@table_name,
         @column_name =@column_name,
         @columnsetflag =@columnsetflag  ,
         @executeflag =@executeflag ,
         @altertablesql = @columnsql output
         
         
if @columnsql is null or @columnsql=''
         set @columnsql=''
   
   
if @addgo=1
   begin
     execute dbgo_outputsql  @objectsql=@columnsql output
   end
            
  if  @altertablesql is null or  @altertablesql =''
   set @altertablesql =@columnsql
  else
   set @altertablesql =@altertablesql + @columnsql
            
 
 fetch  from @my_cursor into  @column_name
 
end
 
   close @my_cursor
 deallocate @my_cursor
 
 
 return
go
/****** object:  storedprocedure [dbo].[dbobject_tabledefaultcheck]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure [dbo].[dbobject_tabledefaultcheck]
   @table_name nvarchar(1024),
   @column_name nvarchar(300),
   @object_name nvarchar(300),
   @default_check int,
   @addgo int ,
   @drop_add int,
   @replaceflag int,
   @executeflag int,
   @objectsql nvarchar(max) output ,
   @execute_output int
as

  -- @table_column_object int,
  -- @table_column_object 参数方式  1.table  2.column 3.default   12-21 table+column  (任意组合)
  --  @execute_output int 是否执行  还是输出

declare  @ls_table_name nvarchar(1024),
   @ls_column_name nvarchar(300),
   @ls_object_name nvarchar(300)
   ---convert(varchar(8000), d.definition ) as default_value,   
  
declare @column_id int
declare @modify_date datetime
 

declare @ls_objectsql nvarchar(max)

---------------设置换行符号----------------------
------declare @is_newline nchar(2)
------select @is_newline =newline from dbgo
------if @is_newline is null or @is_newline=''
------ begin
------     set @is_newline=char(13)+char(10)
------ end

   if @table_name is null
    set @table_name=''
   if @column_name  is null
    set @column_name=''
   if  @object_name is null
    set @object_name=''

 

set @objectsql=''

---------------------------
 declare @my_cursor  cursor
 
 if @default_check = 1
    begin
      set @my_cursor=cursor for
       select
       object_name(d.parent_object_id) as table_name,
       c.name as column_name ,
       d.name  as object_name,
       d.parent_column_id as column_id,
       d.modify_date
       from sys.default_constraints d, sys.columns c
       where d.parent_object_id = c.object_id
       and   d.parent_column_id =c.column_id
       and  ( @object_name='' or  d.name=@object_name )
       and  ( @table_name='' or  object_name(d.parent_object_id)=@table_name )
       and  (@column_name = '' or  c.name=@column_name )
       order by  table_name,column_id,object_name
    end
 else
     begin
      set @my_cursor=cursor for
        select
        object_name(d.parent_object_id) as table_name,
        c.name as column_name ,
        d.name  as object_name,
        d.parent_column_id as column_id,
        d.modify_date
        from sys.check_constraints d, sys.columns c
        where d.parent_object_id = c.object_id
        and   d.parent_column_id =c.column_id
        and  ( @object_name='' or  d.name=@object_name )
        and  ( @table_name='' or  object_name(d.parent_object_id)=@table_name )
        and  (@column_name = '' or  c.name=@column_name )
        order by  table_name,column_id,object_name
    end
    
    open @my_cursor 
    fetch  from @my_cursor into @ls_table_name ,@ls_column_name ,@ls_object_name,@column_id,@modify_date
  
  
-------------------------
while  @@fetch_status = 0
 begin
 
 set @ls_objectsql=''
 
      --输出的是立即可执行代码
      if @execute_output=1  and @drop_add=1
        begin
            set @executeflag=1
        end
        
   ---开始循环输入[生成缺省对象]存储过程
        execute dbobject_outputdefaultsql @default_check=@default_check,@default_name=@ls_object_name, @drop_add = @drop_add , @replaceflag =@replaceflag , @executeflag =@executeflag, @defaultsql=@ls_objectsql  output
 
   ---立即执行删除操作 
      if @execute_output=1  and @drop_add=1
         begin
            execute sp_executesql  @ls_objectsql
         end
         
     if @addgo=1
        begin
          --添加go
          execute dbgo_outputsql  @objectsql=@ls_objectsql output
        end
 
       if @objectsql=''
        set @objectsql=@ls_objectsql
       else
        set @objectsql=@objectsql + @ls_objectsql
        
     fetch  from @my_cursor into @ls_table_name ,@ls_column_name ,@ls_object_name,@column_id,@modify_date
 end
 
   close @my_cursor
 deallocate @my_cursor
 

 

return
go
/****** object:  storedprocedure [dbo].[dbobject_tableforeign]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create    procedure [dbo].[dbobject_tableforeign]
   @table_name nvarchar(1024),
   @column_name nvarchar(300),
   @object_name nvarchar(300),
   @addgo int ,
   @drop_add int,
   @replaceflag int,
   @executeflag int,
   @objectsql nvarchar(max) output ,
   @execute_output int
as

  -- @table_column_object int,
  -- @table_column_object 参数方式  1.table  2.column 3.default   12-21 table+column  (任意组合)
  --  @execute_output int 是否执行  还是输出

declare  @ls_table_name nvarchar(1024),
   @ls_column_name nvarchar(300),
   @ls_object_name nvarchar(300)
   ---convert(varchar(8000), d.definition ) as default_value,   
  
declare @column_id int
declare @modify_date datetime
 

declare @ls_objectsql nvarchar(max)

---------------设置换行符号----------------------
------declare @is_newline nchar(2)
------select @is_newline =newline from dbgo
------if @is_newline is null or @is_newline=''
------ begin
------     set @is_newline=char(13)+char(10)
------ end

   if @table_name is null
    set @table_name=''
   if @column_name  is null
    set @column_name=''
   if  @object_name is null
    set @object_name=''

 

set @objectsql=''

---------------------------
 declare @my_cursor  cursor
 
 ----c2.name
  if  not ( @column_name  is null or @column_name='' )
   begin
     set @my_cursor=cursor for 
       select   object_name(c2.object_id) , object_name( f.constraint_object_id )
      from sys.foreign_key_columns f, sys.columns c2
        where f.parent_object_id=c2.object_id
        and  f.parent_column_id=c2.column_id
        and ( @table_name  ='' or (object_name(c2.object_id)  = @table_name  )  )
        and ( c2.name=@column_name )
        and ( @object_name  ='' or ( object_name( f.constraint_object_id ) = @object_name  )  )
        group by   object_name(c2.object_id) ,object_name( f.constraint_object_id )
        order by 1,2
    end    
  else
   begin
   
   ----space(0) as column_name,
    set @my_cursor=cursor for 
       select  object_name(f.parent_object_id) ,
        f.name
      from sys.foreign_keys f
      where   ( @table_name  ='' or object_name(f.parent_object_id) =@table_name )
      and ( @object_name  ='' or  f.name=@object_name )
      order by 1,2
    end
      
      open @my_cursor 
      fetch  from @my_cursor into @ls_table_name,@ls_object_name
     
-------------------------
while  @@fetch_status = 0
 begin
 
 set @ls_objectsql=''
 
      --输出的是立即可执行代码
      if @execute_output=1  and @drop_add=1
        begin
            set @executeflag=1
        end
   
   
   set @ls_objectsql=''
   
    ---@table_name =@ls_table_name,
   ---开始循环输出[生成索引对象]存储过程
    execute  dbobject_outputtableforeign @foreign_name =@ls_object_name,@drop_add =@drop_add ,@replaceflag =@replaceflag ,@executeflag =@executeflag ,@objectsql =@ls_objectsql output
 
   ---立即执行删除操作 
      if @execute_output=1  and @drop_add=1
         begin
            execute sp_executesql  @ls_objectsql
         end
         
     if @addgo=1
        begin
          --添加go
           execute dbgo_outputsql  @objectsql=@ls_objectsql output
        end
 
       if @objectsql=''
        set @objectsql=@ls_objectsql
       else
        set @objectsql=@objectsql + @ls_objectsql
        
   fetch  from @my_cursor into @ls_table_name,@ls_object_name
 end
 
   close @my_cursor
 deallocate @my_cursor
 

 

return
go
/****** object:  storedprocedure [dbo].[dbobject_tabledropaddrelation]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create   procedure   [dbo].[dbobject_tabledropaddrelation]
     @table_name nvarchar(1024),
     @column_name nvarchar(1024),
     @addgo int ,
     @drop_add  int ,
     @replaceflag int,
     @executeflag int ,
     @objectdropaddsql  nvarchar(max) output ,
     @execute_output int
as

declare  @object_name nvarchar(1024),
   @default_check int
   
declare @objectindexessql nvarchar(max)   
declare @objectforeignsql nvarchar(max)   
declare @objectdefaultsql nvarchar(max)   
declare @objectchecksql nvarchar(max)   

 

 

set @object_name=''
---1.删除或建立主关键字与索引
execute dbobject_tableindexes @table_name =@table_name,@column_name =@column_name,@object_name =@object_name, @addgo =@addgo , @drop_add =@drop_add, @replaceflag =@replaceflag, @executeflag =@executeflag,@objectsql=@objectindexessql output ,@execute_output=@execute_output

 

---2.删除或建立foreign对象
set @object_name=''
execute dbobject_tableforeign @table_name =@table_name,@column_name =@column_name,@object_name =@object_name, @addgo =@addgo , @drop_add =@drop_add, @replaceflag =@replaceflag, @executeflag =@executeflag,@objectsql=@objectforeignsql output ,@execute_output=@execute_output
 

---3。删除或建立缺省对象
set @object_name=''   
set @default_check = 1
execute dbobject_tabledefaultcheck @table_name =@table_name,@column_name =@column_name,@object_name =@object_name, @default_check =@default_check, @addgo =@addgo , @drop_add =@drop_add, @replaceflag =@replaceflag, @executeflag =@executeflag,@objectsql=@objectdefaultsql output ,@execute_output=@execute_output
   
   
---4。删除或建立缺省对象
set @object_name=''
set @default_check = 2
execute dbobject_tabledefaultcheck  @table_name =@table_name,@column_name =@column_name,@object_name =@object_name, @default_check =@default_check, @addgo =@addgo , @drop_add =@drop_add, @replaceflag =@replaceflag, @executeflag =@executeflag,@objectsql=@objectchecksql output ,@execute_output=@execute_output
 
 
 
 
   
if  @objectindexessql is null
 set @objectindexessql=''

if @objectforeignsql is null
 set @objectforeignsql=''

if  @objectdefaultsql  is null
 set @objectdefaultsql =''
if  @objectchecksql is null
 set @objectchecksql=''
 
 
 --if @addgo = 1
 --begin
 --   execute dbgo_outputsql @objectsql=@objectindexessql output
 --   execute dbgo_outputsql @objectsql=@objectforeignsql output
 --   execute dbgo_outputsql @objectsql=@objectdefaultsql output
 --   execute dbgo_outputsql @objectsql=@objectchecksql output
   
 --end
 
 
 set  @objectdropaddsql= @objectindexessql + @objectforeignsql + @objectdefaultsql + @objectchecksql
 
 
 if @executeflag =0
   begin
     execute dbgo_printsql @objectdropaddsql
   end
 
 
return
go
/****** object:  storedprocedure [dbo].[dbobject_tabledefault]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbobject_tabledefault]
  
   @table_name nvarchar(1024),
   @column_name nvarchar(300),
   @object_name nvarchar(300),
   @drop_add int,
   @replaceflag int,
   @executeflag int,
   @objectsql nvarchar(max) output,
   @execute_output int
   
as
   ---@table_column_object int,
 -- @table_column_object 参数方式  1.table  2.column 3.default   12-21 table+column  (任意组合)
 
---输入所有表 所有列
     ---@table_column_object =@table_column_object ,
    exec [dbobject_tabledefaultcheck]
   @table_name =@table_name ,
   @column_name =@column_name,
   @object_name =@object_name ,
   @default_check=1,
   @drop_add =@drop_add ,
   @replaceflag =@replaceflag,
   @executeflag =@executeflag ,
   @objectsql =@objectsql output,
   @execute_output=@execute_output
   
   return
go
/****** object:  storedprocedure [dbo].[dbobject_tablecheck]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure [dbo].[dbobject_tablecheck]
   @table_name nvarchar(1024),
   @column_name nvarchar(300),
   @object_name nvarchar(300),
   @drop_add int,
   @replaceflag int,
   @executeflag int,
   @objectsql nvarchar(max) output,
   @execute_output int
   
as
 ---@table_column_object int,
 -- @table_column_object 参数方式  1.table  2.column 3.default   12-21 table+column  (任意组合)
---输入所有表 所有列
 ---@table_column_object =@table_column_object ,
    exec [dbobject_tabledefaultcheck]
   @table_name =@table_name ,
   @column_name =@column_name,
   @object_name =@object_name ,
   @default_check=2,
   @drop_add =@drop_add ,
   @replaceflag =@replaceflag,
   @executeflag =@executeflag ,
   @objectsql =@objectsql output,
   @execute_output=@execute_output
   
   return
go
/****** object:  storedprocedure [dbo].[dbobject_autoaltertablealtercolumn]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure [dbo].[dbobject_autoaltertablealtercolumn]
    @table_name nvarchar(200),
    @column_name nvarchar(200) ,
    @datatype nvarchar(200) ,
    @is_computed int,
    @is_nullable int,
    @is_identity int ,
    @existdefault int,
    @default_definition nvarchar(max),
    @computer_definition nvarchar(max),
    @identity_sql nvarchar(1000),
    @executeflag int
as
 --智能功能-表自动修改列以及缺省数值
 --智能功能-表自动添加列以及缺省数值
 declare @columnsql nvarchar(max)
 declare @objectdropaddsql nvarchar(max)

declare @li_needexecute int
 set @li_needexecute=0

 

 ------if @default_definition is null or  @default_definition=''
 ------   set @default_definition =''
 ------else
 ------   set  @default_definition =' default ' + @default_definition
 
declare   @old_datatype nvarchar(200)  ,
    @old_is_computed int ,
    @old_is_nullable int ,
    @old_is_identity int  ,
    @old_default_definition nvarchar(max)  ,
    @old_computer_definition nvarchar(max)  ,
    @old_identity_sql nvarchar(1000)

execute dbobject_tablecolumngetvalues
    @table_name = @table_name,
    @column_name = @column_name  ,
    @datatype= @old_datatype  output ,
    @is_computed=@old_is_computed  output,
    @is_nullable =@old_is_nullable  output,
    @is_identity  =@old_is_identity output ,
    @default_definition =@old_default_definition  output ,
    @computer_definition  =@old_computer_definition output ,
    @identity_sql =@old_identity_sql  output

---or  @default_definition =@old_default_definition  
 set @li_needexecute=0
 ---判断列的内容是否不相同 如果是就开始执行
 if (  @datatype<> @old_datatype   or   @is_computed<>@old_is_computed   or  @is_nullable <>@old_is_nullable   or   @is_identity <>@old_is_identity    or   @computer_definition  <>@old_computer_definition  or   @identity_sql <>@old_identity_sql )
    begin
           set @li_needexecute=1
    end
    
   if  @li_needexecute=0
            return

   --开始删除关联对象  @li_needexecute=0
   execute dbobject_tabledropaddrelation @table_name =@table_name, @column_name =@column_name,
   @addgo =1 , @drop_add=1,@replaceflag =3 ,  @executeflag = @executeflag , @objectdropaddsql  = @objectdropaddsql output ,@execute_output= @executeflag
   
   
 if @is_computed=1
     set @columnsql=@column_name + '  ' +  @computer_definition   +  ( case @is_nullable when 0 then ' not null'    else   ' null'  end  )
     -- +  @identity_sql
 else
     set @columnsql=@column_name + '  ' + @datatype  +  ( case @is_nullable when 0 then ' not null'    else   ' null'  end  )
     -- + @identity_sql 
    
      set @columnsql = ' alter  table ' +@table_name + ' alter column ' + @columnsql
 
---立即执行
   if @executeflag =1
      execute sp_executesql @columnsql  
   else
      execute dbgo_printsql @columnsql
go
/****** object:  storedprocedure [dbo].[dbobject_outputcreatealtertable]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure   [dbo].[dbobject_outputcreatealtertable]
  @table_name nvarchar(200) ,
  @columnsetflag int ,
  @executeflag int,
  @objectsql nvarchar(max) output
as

 

   declare @createtable_proc nvarchar(max)
 --建立与修改表结构
   declare @createtablesql nvarchar(max)
   declare @altertablesql nvarchar(max)
   declare @objectdropaddsql nvarchar(max)

   set @altertablesql=''

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

 

 

---------设置换行符号----------------------
declare @is_newline nchar(2)
select @is_newline =newline from dbgo
if @is_newline is null or @is_newline=''
 begin
     set @is_newline=char(13)+char(10)
 end
-----------------------------------------------

-----[1]输出建立表的语句
    execute dbobject_outputcreatetable 
       @table_name=@table_name,
       @columnsetflag=@columnsetflag,
       @createtablesql=@createtablesql output
       
  
set @createtable_proc=' execute dbobject_autocreatetable ' +''''+@table_name+''''+','+ convert(nvarchar(10),@executeflag)+','+@is_newline+
      ''''+@createtablesql+''''
      
   
  
  
---自动添加go  
 execute dbgo_outputsql  @objectsql=@createtable_proc output

  
  

-----[2]输出修改表的语句
    execute dbobject_outputaltertable 
       @table_name=@table_name,
       @columnsetflag=@columnsetflag,
       @addgo=1,
       @executeflag=@executeflag,
       @altertablesql=@altertablesql output  
  
 --自动添加go  
--- execute dbgo_outputsql  @objectsql= @altertablesql output
 
 
  --[3]开始添加关联对象  @li_needexecute=0
 execute dbobject_tabledropaddrelation @table_name =@table_name, @column_name ='',
   @addgo =1 , @drop_add=2,@replaceflag = 1,@executeflag = @executeflag , @objectdropaddsql  = @objectdropaddsql output ,@execute_output= @executeflag

  if @altertablesql is null
    set @altertablesql=''
    
   if @objectdropaddsql is null
     set @objectdropaddsql=''
 

 set @objectsql= @createtable_proc  + @altertablesql  + @objectdropaddsql

 return
go
/****** object:  storedprocedure [dbo].[dbobject_outputsysobjects]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[dbobject_outputsysobjects]
   @object_name nvarchar(1024),
   @object_type  nvarchar(2),
   @drop_add 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 @addgoflag =1
 --  begin
   
    if not ( @object_type =  'u' or (@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') )
     begin
      execute dbgo_outputsql @objectsql output
    end
return
go
/****** object:  storedprocedure [dbo].[dbobject_autoaltertable]    script date: 04/20/2011 08:57:01 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create  procedure [dbo].[dbobject_autoaltertable]
       @table_name nvarchar(300),
       @column_name nvarchar(300) ,
       @datatype nvarchar(200) ,
       @is_computed int,
       @is_nullable int,
       @is_identity int ,
       @existdefault int,
       @default_definition nvarchar(max),
       @computer_definition nvarchar(max),
       @identity_sql nvarchar(1000),
       @executeflag int
 as
 
 --自动添加或修改列字段
 --立即执行
  if @executeflag is null
        set @executeflag=1

 if  not  exists (select * from sys.columns where name =@column_name  and object_id =object_id (@table_name )  )
   begin
     ---添加列名
     exec dbobject_autoaltertableaddcolumn
        @table_name ,
        @column_name  ,
        @datatype  ,
        @is_computed ,
        @is_nullable ,
        @is_identity  ,
        @existdefault,
        @default_definition ,
        @computer_definition ,
        @identity_sql ,
        @executeflag
    end
else
   begin
     --修改列名
     exec dbobject_autoaltertablealtercolumn
         @table_name ,
         @column_name  ,
         @datatype  ,
         @is_computed ,
         @is_nullable ,
         @is_identity  ,
         @existdefault ,
         @default_definition ,
         @computer_definition ,
         @identity_sql ,
         @executeflag
   end
go
/****** object:  default [df_pbcatcol_create_date]    script date: 04/20/2011 08:57:04 ******/
alter table [dbo].[pbcatcol] add  constraint [df_pbcatcol_create_date]  default (getdate()) for [create_date]
go
/****** object:  default [df_pbcatcol_modify_date]    script date: 04/20/2011 08:57:04 ******/
alter table [dbo].[pbcatcol] add  constraint [df_pbcatcol_modify_date]  default (getdate()) for [modify_date]
go

 

go

---------------内容等于  char(13)+char(10)+'go'+char(13)+char(10) 先回车再换行
if  not exists( select 1 from  dbgo where id='  go  ' )  insert into dbgo ( id,newline)  values ( '  go  ','  ')  else  update dbgo set  newline='  ' where id='  go  '

补充：数据库,Mssql

上一个：sql2005 jdbc连接mssql数据库实现方法
下一个：sql 随机函数newID()和RAND()详解