| 标题 | 给sqlserver中的表创建索引 |
| 范文 | 给sqlserver中的表创建索引 #!/usr/bin/perl use DBI; use Switch; use Encode; use Encode::CN; # my $source_name = "zoe"; # my $source_user_name = "sa"; # my $source_user_psd = "123"; # my $db_name="mysqlDb"; # my $location="192.168.0.46"; # my $port="3306"; # my $db_user="zoe"; # my $db_pass="123"; my $source_name = "zoe"; my $source_user_name = "sa"; my $source_user_psd = "123"; my $dbh=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd); #获取所有的用户表 my $sth=$dbh->prepare("select name,object_id from sys.all_objects where type='U' and is_ms_shipped=0 and name <>'sysdiagrams'"); $sth->execute(); my $n=0; my $ok=0; my $sort_column=""; while (@data=$sth->fetchrow_array()) { #print $data[0].$data[1]; $n+=1; $ok=0; #获取列 get_columns($data[0],$data[1]); if($ok ==1){ print '正在测试'.$data[0].'表的索引'.$sort_column."n"; my $sql_create="select * from sysindexes where id=object_id('$data[0]') and name='$sort_column'"; my $dbh_mssql=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd,{RaiseError =>1}); $dbh_mssql->{LongTruncOk}=1; $dbh_mssql->{LongReadLen}=1048576; my $sth_select=$dbh_mssql->prepare($sql_create); $sth_select->execute() or die 'Cannot execute: '. $sth_select->errstr(); my @select_col; my $select_data; while($select_data=$sth_select->fetchrow_arrayref()) { $select_col[$nn]=[@$select_data]; } my $col=@select_col; if($col !=0) { print '表'.$data[0].'已存在索引'.$sort_column."n"; } else { do_sql($data[0],$sort_column); open(FILE,"》createtableallindex.txt"); syswrite(FILE,"$nn"); syswrite(FILE,"$data[0]n"); close(FILE); } } } $sth->finish; $dbh ->disconnect; print '所有表的索引创建结束'."n"; ##获取所有的列 sub get_columns { $dbh2=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd); my $sql="select col.name,tp.name,col.max_length,col.[precision],col.[scale],col.[is_nullable],col.[is_identity] from sys.all_columns col inner join sys.types tp on col.system_type_id=tp.system_type_id and col.user_type_id=tp.user_type_id where object_id=$_[1]"; my $cols=$dbh2 -> prepare($sql); $cols->execute(); my $cols_str = ""; my $n=0; $sort_column=""; while(@col= $cols->fetchrow_array()) { ($col_name,$type_name,$max_length,$precision,$scale,$is_nullable,$is_identity)=@col; if($is_identity == 1) { $ok=1; $sort_column="$col_name"; } } } sub do_sql { print '开始创建'.$_[0].'表的索引'.$_[1]."n"; my $sql_create="CREATE UNIQUE INDEX $_[1] ON $_[0] ($_[1])"; my $dbh_mssql=DBI->connect("dbi:ODBC:$source_name",$source_user_name,$source_user_psd,{RaiseError =>1}); $dbh_mssql->{LongTruncOk}=1; $dbh_mssql->{LongReadLen}=1048576; my $sth_select=$dbh_mssql->prepare($sql_create); # open(FILE,"》all_export_data222.txt"); # syswrite(FILE,"$sql_selectn"); # close(FILE); $sth_select->execute() or die 'Cannot execute: '. $sth_select->errstr(); print '创建'.$_[0].'表的索引'.$_[1].'结束'."n"; } |
| 随便看 |
|
在线学习网范文大全提供好词好句、学习总结、工作总结、演讲稿等写作素材及范文模板，是学习及工作的有利工具。