Create an index for a table in 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 );
# Getting all user tables
My $ something = $ dbh-> prepare ("select name, object_id from sys. all_objects where type = 'U' and is_ms_shipped = 0 and name <> 'sysdiagrams '");
$ Something-> execute ();
My $ n = 0;
My $ OK = 0;
My $ sort_column = "";
While (@ data = $ something-> fetchrow_array ())
{
# Print $ data [0]. $ data [1];
$ N + = 1;
$ OK = 0;
# Retrieving Columns
Get_columns ($ data [0], $ data [1]);
If ($ OK = 1 ){
Print 'testing '. $ data [0]. 'table Index'. $ 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-& gt; {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]. 'existing Index'. $ sort_column. "\ n ";
}
Else
{
Do_ SQL ($ data [0], $ sort_column );
Open (FILE, "createtableallindex.txt ");
Syswrite (FILE, "$ n \ n ");
Syswrite (FILE, "$ data [0] \ n ");
Close (FILE );
}
}
}
$ Something-> finish;
$ Dbh-> disconnect;
Print 'end of index creation for all Table'. "\ n ";
# Retrieve all columns
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 'start to create '. $ _ [0]. 'table Index'. $ _ [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-& gt; {LongReadLen} = 1048576;
My $ sth_select = $ dbh_mssql-> prepare ($ SQL _create );
# Open (FILE, "》all_export_data222.txt ");
# Syswrite (FILE, "$ SQL _select \ n ");
# Close (FILE );
$ Sth_select-> execute () or die 'cannot execute: '. $ sth_select-> errstr ();
Print 'create '. $ _ [0]. 'table Index'. $ _ [1]. 'terminal'. "\ n ";
}