To create an index on a table in SQL Server

To create an index on a table in SQL Server

#!/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 ' is testing '. $data [0]. ' The index of the table '. $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 ' table '. $data [0]. ' Index already exists '. $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 ' index creation over all tables '. n ";

# #获取所有的列

Sub Get_columns

{

$DBH 2=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 = $dbh 2-> 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 ' starts creating '. $_[0]. ' The index of the table '. $_[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 ' Create '. $_[0]. ' Index of the table '. $_[1]. ' End '. ' n ";

}