【www.gdgbn.com--今天更新】
---主要入口: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_namewhile @@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)
endif @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
asset @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
asselect 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_namewhile @@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_nameend
------------------
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
asset @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_nameif @executeflag=1
execute sp_executesql @defaultsqlelse
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)
endif @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 intdeclare @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 dbgoif @is_go is null or @is_go=""
begin
set @is_go=char(13)+char(10)+"go"+char(13)+char(10)
endset @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)
------ endif @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
endif @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