欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

给sqlserver中的表创建索引

程序员文章站 2022-05-11 12:41:43
...

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

  }