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

给sqlserver中的表创建索引

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2016-06-07 16:21:55
Original
1465 people have browsed it

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

  }

Related labels:
Statement of this Website
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
Latest Issues
Problem with tp6 connecting to sqlserver database
From 1970-01-01 08:00:00
0
0
0
Unable to connect to SQL Server in Laravel
From 1970-01-01 08:00:00
0
0
0
Methods of parsing MYD, MYI, and FRM files
From 1970-01-01 08:00:00
0
0
0
SQLSTATE: User login failed
From 1970-01-01 08:00:00
0
0
0
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template