程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 給sqlserver中的表創建索引

給sqlserver中的表創建索引

編輯:關於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";

      }

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved