Create an index for a table in sqlserver

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

}