Home >Database >Mysql Tutorial >给sqlserver中的表创建索引

给sqlserver中的表创建索引

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
2016-06-07 16:21:551458browse

给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=33

   给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";

  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";

  }

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn