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

声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn核实处理。

专题推荐：nbsp name object nvarchar table

上一篇：sql delete语句及同时删除多表数据实现方法 下一篇：自己动手写 PHP MVC 框架(40节精讲/巨细/新人进阶必看)