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,
 -- @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_autodefaultdroql]    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_aut