【997755.com澳门葡京】找到超长字段脚本,sql存款和储蓄进程中使用

一.sql存款和储蓄进程中选取 output

平常开支系列时有时会赶上数据超长导致往数据库中保留时出错。

毕生付出种类时有时会遇见数据超长导致往数据库中保存时出错。

语法

[Directory(directory)]

[IndexOnly] –Optional

[ForceCollectCount(count)] -Optional

[DBTableName(table name)] –Optional

[DBAdapter(adapter name)]

[DBConnect(connection string)]

CREATE TABLE

table_name

(

{ < column_definition >
        | column_name AS computed_column_expression         | < table_constraint > } [ ,n ]

)

 

CREATE PROCEDURE [dbo].[P_Max]
@a int, -- 输入
@b int, -- 输入
@Returnc int output --输出
AS 

if (@a>@b) 
   set @Returnc =@a
else
   set @Returnc =@b

-- 调用
declare @Returnc int 
exec P_Max  2,3,@Returnc output 
select @Returnc

采纳上面的剧本能够便宜的寻觅超长的字段。

使用上边的台本能够便宜的寻找超长的字段。

属性

 二.Sql Server
充实字段、修改字段、修改类型、修改暗许值

一.通过标准表创立目前表,修改权且表中varchar、nvarchar的长度为max

一.通过规范表创制暂时表,修改权且表中varchar、nvarchar的尺寸为max

[Directory(directory)]

那性子格钦赐全文索引在本地的地方。那特本性是必选属性。

全文索引将被确立在这一个地点下。

 

declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='TableName';--正式表表名:此处需要修改
set @temp_table_name = @table_name+'_temp';--临时表表名:此处需要修改

--根据正式表创建临时表
set @sql = 'select * into '+@temp_table_name+' from '+@table_name +' where 1<>1;';
exec(@sql);

--修改varchar/nvarchar临时表字段长度为max
set @sql = '';
select @sql=@sql+('alter table '+@temp_table_name+' alter column '+b.name+' '+c.name+'(max);')  
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@temp_table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

exec(@sql);

--手动往临时表中写入数据
declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='TableName';--正式表表名:此处需要修改
set @temp_table_name = @table_name+'_temp';--临时表表名:此处需要修改

--根据正式表创建临时表
set @sql = 'select * into '+@temp_table_name+' from '+@table_name +' where 1<>1;';
exec(@sql);

--修改varchar/nvarchar临时表字段长度为max
set @sql = '';
select @sql=@sql+('alter table '+@temp_table_name+' alter column '+b.name+' '+c.name+'(max);')  
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@temp_table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

exec(@sql);

--手动往临时表中写入数据

[IndexOnly] –Optional

其壹天性钦点全文数据表为仅仅索引。那特性情是可选属性。

若果设置为单纯索引,在执行插入,修改和删除语句时只修改索引,而不修改数据自己。这些装置在Rebuild
也许数据库驱动索引时卓有成效。

 ①修改字段名:

  alter table 表名 rename column A to B

 ②修改字段类型:

  alter table 表名 alter column 字段名 type not null

 ③修改字段默认值
  alter table 表名 add default (0) for 字段名 with values

  如果字段有默认值,则需要先删除字段的约束,在添加新的默认值,

  select c.name from sysconstraints a 
  inner join syscolumns b on a.colid=b.colid 
  inner join sysobjects c on a.constid=c.id
  where a.id=object_id('表名') 
  and b.name='字段名'

  根据约束名称删除约束

  alter table 表名 drop constraint 约束名

  根据表名向字段中增加新的默认值

  alter table 表名 add default (0) for 字段名 with values

④增加字段:

  alter table 表名 add 字段名 type not null default 0

⑤删除字段:

  alter table 表名 drop column 字段名;

贰.数据手动写入一时半刻表后,查找超长字段

二.数码手动写入一时表后,查找超长字段

[ForceCollectCount(count)] –Optional

强制回收数据的记录数,可选属性。

当批量布署时,哈勃勒.net并不会即时更新索引,而是要到插入数量超越ForceCollectionCount时才履新索引,那样做是为着尽量升高索引的频率。ForecCollectionCount的私下认可值为陆仟.
得以修改这么些值为随机正整数。

 

declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='TableName';--正式表表名:此处需要修改
set @temp_table_name = @table_name+'_temp';--临时表表名:此处需要修改
--校验临时表是哪个字段超长
create table #col_tab
(
    id int,
    col_name varchar(100),
    col_condition varchar(500)
);

insert into #col_tab(id,col_name,col_condition)
select ROW_NUMBER() over(order by b.colid) id,
b.name,
(case c.name when 'nvarchar' then 'len' when 'varchar' then 'datalength' end)+
'('+b.name+')>'+cast((case c.name when 'nvarchar' then b.length/2 when 'varchar' then b.length end) as varchar) 
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

select * from #col_tab ;

declare @cnt int ;
select @cnt = COUNT(*) from #col_tab;

declare @index int;
declare @col_condition varchar(500);
declare @col_name varchar(100);

set @index=1;
while @index<=@cnt
begin
    select @col_condition = col_condition,@col_name=col_name from #col_tab where id = @index;
    set @sql = 'declare @condition_cnt int;';
    set @sql = @sql+'select @condition_cnt=COUNT(*) from '+@temp_table_name+' where '+@col_condition+';';
    --set @sql = @sql+'print @condition_cnt;';
    set @sql = @sql+'if(@condition_cnt>0)
    begin
        print ''['+@col_name+']字段超长!'';
    end;';
exec(@sql);
    set @index=@index+1;
end;

drop table #col_tab;
declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='TableName';--正式表表名:此处需要修改
set @temp_table_name = @table_name+'_temp';--临时表表名:此处需要修改
--校验临时表是哪个字段超长
create table #col_tab
(
    id int,
    col_name varchar(100),
    col_condition varchar(500)
);

insert into #col_tab(id,col_name,col_condition)
select ROW_NUMBER() over(order by b.colid) id,
b.name,
(case c.name when 'nvarchar' then 'len' when 'varchar' then 'datalength' end)+
'('+b.name+')>'+cast((case c.name when 'nvarchar' then b.length/2 when 'varchar' then b.length end) as varchar) 
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

select * from #col_tab ;

declare @cnt int ;
select @cnt = COUNT(*) from #col_tab;

declare @index int;
declare @col_condition varchar(500);
declare @col_name varchar(100);

set @index=1;
while @index<=@cnt
begin
    select @col_condition = col_condition,@col_name=col_name from #col_tab where id = @index;
    set @sql = 'declare @condition_cnt int;';
    set @sql = @sql+'select @condition_cnt=COUNT(*) from '+@temp_table_name+' where '+@col_condition+';';
    --set @sql = @sql+'print @condition_cnt;';
    set @sql = @sql+'if(@condition_cnt>0)
    begin
        print ''['+@col_name+']字段超长!'';
    end;';
exec(@sql);
    set @index=@index+1;
end;

drop table #col_tab;

[DBTableName(table name)]【997755.com澳门葡京】找到超长字段脚本,sql存款和储蓄进程中使用。 –Optional

数据库中对应表的表名,可选属性。

出于哈勃勒.net全文索引是和数据库关联的,Hubble.net负责索引,数据库负责储存。假如指望哈勃勒.net中的表名和数据库的表名不雷同,能够经过那性格格来安装。暗中认可情状下,数据库表名和Hubble.net的表名相同。

 2.nvarchar(max)一坑

 三.新建测试表

 3.新建测试表

[DBAdapter(adapter name)]

数据库适配器名,必选属性

在那里内定选用哪种数据库适配器。哈勃勒.net 近年来默许提供MS SQLSEQX56VECRUISER贰零零柒/二零零六 的数据库适配器。用户能够通过落到实处 IDBAdapter
接口,并经过SP_AddExternalReference
系统存款和储蓄进度将协调编辑的数据库适配器安装到哈勃勒.net中。

 

CREATE TABLE [dbo].[USERS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](30) NULL,
    [password] [varchar](30) NULL,
    [roleid] [int] NULL,
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]
CREATE TABLE [dbo].[USERS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](30) NULL,
    [password] [varchar](30) NULL,
    [roleid] [int] NULL,
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]

[DBConnect(connection string)]

连年字符串,必选属性

以此天性钦赐连接数据库适配器对应的数据库的连接字符串。

  监察和控制邮件中有个变量使用了nvarchar(max),有一天数据多了,总字节有150048,不过接连读出来玖仟五个。郁闷,查了查,才知道是甚难点。

表截图如下:

表截图如下:

数据类型

此时此刻扶助的数据类型有

Int, TinyInt, SmallInt, BigInt, Float, DateTime, SmallDateTime, Date,
NVarchar, Varchar, NChar, Char

其中

datetime 的范围为 .net 中 datetime 数据类型的时间范围,精度为微秒

smalldatetime 的限定是 一九八〇年七月一日 0:0:0 – 20肆七年11月1三十一日 二三:5九:5玖,
精度为秒

date 的限定是 一年八月二二十八日 – 588351陆年3月二15日,进程为日

nvarchar, varchar, nchar, char
那三种字符串类型能够支持钦点长度和不限长度,不限长度时写成 (max) ,如
nvarchar(max)

假定字符串类型作为非全文方式索引(Untokenized index) 则长度最大为3二。

 

       

997755.com澳门葡京 1

997755.com澳门葡京 2

column_definition

  关于 varchar max的误区

修改表名,运维一.透过规范表创立权且表,修改临时表中varchar、nvarchar的长度为max 脚本

修改表名,运维壹.通过标准表创设权且表,修改最近表中varchar、nvarchar的长短为max 脚本

列名

同 T-SQL, 要是是主要字,用[]内定列名。

declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='USERS';--正式表表名:此处需要修改
set @temp_table_name = @table_name+'_temp';--临时表表名 

--根据正式表创建临时表
set @sql = 'select * into '+@temp_table_name+' from '+@table_name +' where 1<>1;';
exec(@sql);

--修改varchar/nvarchar临时表字段长度为max
set @sql = '';
select @sql=@sql+('alter table '+@temp_table_name+' alter column '+b.name+' '+c.name+'(max);')  
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@temp_table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

exec(@sql);

--手动往临时表中写入数据
declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='USERS';--正式表表名:此处需要修改
set @temp_table_name = @table_name+'_temp';--临时表表名 

--根据正式表创建临时表
set @sql = 'select * into '+@temp_table_name+' from '+@table_name +' where 1<>1;';
exec(@sql);

--修改varchar/nvarchar临时表字段长度为max
set @sql = '';
select @sql=@sql+('alter table '+@temp_table_name+' alter column '+b.name+' '+c.name+'(max);')  
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@temp_table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

exec(@sql);

--手动往临时表中写入数据

数据类型

见上一节

变化一时表如下:

变动近来表如下:

索引类型-可选

997755.com澳门葡京 3

997755.com澳门葡京 4

Tokenized

Tokenized
索引类型表示该字段为全文索引字段,如若钦命这些项目,必选内定Analyzer,即分析器的名称

能够看到varchar的长度修改为了max.

能够看看varchar的长度修改为了max.

Untokenized

Untokenized
索引类型表示该字段为非全文索引字段,常常为一些价位,面积,分类等元数据字段,倘诺要求在寻找全文是还要过滤元数据,必须将相应元数据字段设置为Untokenized
类型索引字段。

None

暗中认可索引类型,仅存款和储蓄。

四.修改表名后运维脚本二

四.修改表名后运维脚本二

Analyzer

1旦索引类型是 Tokenized, 必须经过Analyzer 关键字钦点分析器的名号。

如 Analyzer ‘SimpleAnalyzer’,
用户能够透过达成IAnalyzer接口,并透过SP_AddExternalReference
系统存款和储蓄进度将协调编辑的分析器安装到哈勃勒.net中。

 

declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='USERS';--正式表表名:此处需要修改
set @temp_table_name = @table_name+'_temp';--临时表表名 
--校验临时表是哪个字段超长
create table #col_tab
(
    id int,
    col_name varchar(100),
    col_condition varchar(500)
);

insert into #col_tab(id,col_name,col_condition)
select ROW_NUMBER() over(order by b.colid) id,
b.name,
(case c.name when 'nvarchar' then 'len' when 'varchar' then 'datalength' end)+
'('+b.name+')>'+cast((case c.name when 'nvarchar' then b.length/2 when 'varchar' then b.length end) as varchar) 
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

select * from #col_tab ;

declare @cnt int ;
select @cnt = COUNT(*) from #col_tab;

declare @index int;
declare @col_condition varchar(500);
declare @col_name varchar(100);

set @index=1;
while @index<=@cnt
begin
    select @col_condition = col_condition,@col_name=col_name from #col_tab where id = @index;
    set @sql = 'declare @condition_cnt int;';
    set @sql = @sql+'select @condition_cnt=COUNT(*) from '+@temp_table_name+' where '+@col_condition+';';
    --set @sql = @sql+'print @condition_cnt;';
    set @sql = @sql+'if(@condition_cnt>0)
    begin
        print ''['+@col_name+']字段超长!'';
    end;';
exec(@sql);
    set @index=@index+1;
end;

drop table #col_tab;
declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='USERS';--正式表表名:此处需要修改
set @temp_table_name = @table_name+'_temp';--临时表表名 
--校验临时表是哪个字段超长
create table #col_tab
(
    id int,
    col_name varchar(100),
    col_condition varchar(500)
);

insert into #col_tab(id,col_name,col_condition)
select ROW_NUMBER() over(order by b.colid) id,
b.name,
(case c.name when 'nvarchar' then 'len' when 'varchar' then 'datalength' end)+
'('+b.name+')>'+cast((case c.name when 'nvarchar' then b.length/2 when 'varchar' then b.length end) as varchar) 
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

select * from #col_tab ;

declare @cnt int ;
select @cnt = COUNT(*) from #col_tab;

declare @index int;
declare @col_condition varchar(500);
declare @col_name varchar(100);

set @index=1;
while @index<=@cnt
begin
    select @col_condition = col_condition,@col_name=col_name from #col_tab where id = @index;
    set @sql = 'declare @condition_cnt int;';
    set @sql = @sql+'select @condition_cnt=COUNT(*) from '+@temp_table_name+' where '+@col_condition+';';
    --set @sql = @sql+'print @condition_cnt;';
    set @sql = @sql+'if(@condition_cnt>0)
    begin
        print ''['+@col_name+']字段超长!'';
    end;';
exec(@sql);
    set @index=@index+1;
end;

drop table #col_tab;

NULL, NOT NULL

Tokenized, Untokenized 类型索引字段必须为 NOT NULL, None
类型索引字段能够为NULL.

 

生成where条件是非同一般,运营后如下图:

生成where条件是最重要,运转后如下图:

Default-可选

钦定字段的暗中认可值

 

997755.com澳门葡京 5

997755.com澳门葡京 6

PRIMARY KEY

可以在字段定义中钦定该字段为主键字段。注意通过哈勃勒.net设置的主键字段不是聚集索引,而是非聚集唯一性索引。哈勃勒.net
中表的聚集索引字段为DocId.

 

从此循环where条件查找临时表中多少超长字段,使用print打字与印刷出超长字段的名字。

而后循环where条件查找权且表中数量超长字段,使用print打字与印刷出超长字段的名字。

DocId 字段

DocId 字段是哈勃勒.net
为每张表私下认可建立的字段,用户在编写建表语句是不要求钦点这么些字段。那么些字段为自拉长字段且是唯一性聚集性索引。这么些字段用于固定指定的记录。

 

此脚本在字段较多的图景下,排查难点非凡有利。

此脚本在字段较多的情事下,排查难点充裕便宜。

其余约束

哈勃勒.net
为了简化建表语句,在建表语句中不提供任何约束的定义,固然要那只别的的自律,比如字段的限量等,能够经过SP_ExcuteSql系统存款和储蓄进程来设置。

 

 

 

示例

997755.com澳门葡京,一. 大致的音信表

 

[Directory(‘d:\test\news\’)]

[DBAdapter(‘SQLSERVER2005’)]

[DBConnect(‘Data Source=(local);Initial Catalog=Test;Integrated
Security=True’)]

Create table News

(

Title nvarchar(max) Tokenized Analyzer ‘SimpleAnalyzer’ NOT NULL Default
”,

Content nvarchar(max) Tokenized Analyzer ‘SimpleAnalyzer’ NOT NULL
Default ”,

Time SmallDateTime Untokenized NOT NULL Default ‘1990-01-01’,

Url    nvarchar(max)

);

exec SP_ExcuteSql  ‘News’, ‘Create index I_Time on news(time) ‘;

 

如上建表语句建立三个News全文索引表,全文索引建立在d:\test\news
目录下,这些表和当地SQLSE中华VVEKoleos2007或二〇〇玖 数据库的默许实例中的Test
库中的News表关联。

其一表有八个字段,在那之中Title, Content 为全文索引字段,Time
为非全文索引字段,Url 为仅存储字段。

表建立实现后,大家经过 SP_ExcuteSql 直接操作SQLSELANDVE奥迪Q32006数据库中的Test 数据库,在数据库中创立为Time 字段建立1个非聚集索引,

其一目录在单纯对Time 实行界定查询时有用。

 

二. 点名主键

单字段主键

 

[Directory(‘d:\test\news\’)]

[DBAdapter(‘SQLSERVER2005’)]

[DBConnect(‘Data Source=(local);Initial Catalog=Test;Integrated
Security=True’)]

Create table News

(

NewsId int NOT NULL PRIMARY KEY,

Title nvarchar(max) Tokenized Analyzer ‘SimpleAnalyzer’ NOT NULL Default
”,

Content nvarchar(max) Tokenized Analyzer ‘SimpleAnalyzer’ NOT NULL
Default ”,

Time SmallDateTime Untokenized NOT NULL Default ‘1990-01-01’ ,

Url    nvarchar(max)

);

那个表中,大家钦点NewsId 字段为主键字段

多字段主键

 

[Directory(‘d:\test\news\’)]

[DBAdapter(‘SQLSERVER2005’)]

[DBConnect(‘Data Source=(local);Initial Catalog=Test;Integrated
Security=True’)]

Create table News

(

NewsId int NOT NULL PRIMARY KEY,

NewsType nvarchar(16) UnTokenized NOT NULL PRIMARY KEY,

Title nvarchar(max) Tokenized Analyzer ‘SimpleAnalyzer’ NOT NULL Default
”,

Content nvarchar(max) Tokenized Analyzer ‘SimpleAnalyzer’ NOT NULL
Default ”,

Time SmallDateTime Untokenized NOT NULL Default ‘1990-01-01’ ,

Url    nvarchar(max)

);
 

 

 

其一表中,大家内定NewsId 和NewsType字段为主键字段

仅索引

 

[Directory(‘d:\test\news\’)]

[IndexOnly]

[DBTableName(‘MyNews’)]

[DBAdapter(‘SQLSERVER2005’)]

[DBConnect(‘Data Source=(local);Initial Catalog=Test;Integrated
Security=True’)]

Create table News

(

NewsId int NOT NULL PRIMARY KEY,

Title nvarchar(max) Tokenized Analyzer ‘SimpleAnalyzer’ NOT NULL Default
”,

Content nvarchar(max) Tokenized Analyzer ‘SimpleAnalyzer’ NOT NULL
Default ”,

Time SmallDateTime Untokenized NOT NULL Default ‘1990-01-01’ ,

Url    nvarchar(max)

);

 

 

该建表语句钦点该表仅仅举行索引,并且该表关联的数据库中的表名钦赐为MyNews.

点名称叫仅索引后,大家得以因而SP_Rebuild
存款和储蓄进程,对数据库表MyNews中的索引记录重复成立索引。那种艺术一般用于全文索引丢失后的重建。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website