perlでsql serverのカラム一覧を取得する

・ODBCの設定をする(データソース名→adventureworks2014)
・DBD::ODBCをインストールする

(get_sqlserver_columns.pl)

use DBI;

$dbh = DBI->connect('dbi:ODBC:adventureworks2014','','') || die $!;
$sth = $dbh->prepare("select * from information_schema.columns") || $!;

$sth->execute || die $dbh->errstr;
$length = $sth->{NUM_OF_FIELDS};
while(@row = $sth->fetchrow_array){
  for($i=0; $i<$length-1; $i++){
    print $sth->{NAME}->[$i]."=".$row[$i].",";
  }
  print $sth->{NAME}->[$length-1]."=".$row[$length-1]."\n";
}
$sth->finish();
$dbh->disconnect;

cygwinで実行

$ perl get_sqlserver_columns.pl
TABLE_CATALOG=AdventureWorks2014,TABLE_SCHEMA=Sales,TABLE_NAME=vStoreWithContacts,COLUMN_NAME=BusinessEntityID,ORDINAL_POSITION=1,COLUMN_DEFAULT=,IS_NULLABLE=NO,DATA_TYPE=int,CHARACTER_MAXIMUM_LENGTH=,CHARACTER_OCTET_LENGTH=,NUMERIC_PRECISION=10,NUMERIC_PRECISION_RADIX=10,NUMERIC_SCALE=0,DATETIME_PRECISION=,CHARACTER_SET_CATALOG=,CHARACTER_SET_SCHEMA=,CHARACTER_SET_NAME=,COLLATION_CATALOG=,COLLATION_SCHEMA=,COLLATION_NAME=,DOMAIN_CATALOG=,DOMAIN_SCHEMA=,DOMAIN_NAME=
TABLE_CATALOG=AdventureWorks2014,TABLE_SCHEMA=Sales,TABLE_NAME=vStoreWithContacts,COLUMN_NAME=Name,ORDINAL_POSITION=2,COLUMN_DEFAULT=,IS_NULLABLE=NO,DATA_TYPE=nvarchar,CHARACTER_MAXIMUM_LENGTH=50,CHARACTER_OCTET_LENGTH=100,NUMERIC_PRECISION=,NUMERIC_PRECISION_RADIX=,NUMERIC_SCALE=,DATETIME_PRECISION=,CHARACTER_SET_CATALOG=,CHARACTER_SET_SCHEMA=,CHARACTER_SET_NAME=UNICODE,COLLATION_CATALOG=,COLLATION_SCHEMA=,COLLATION_NAME=SQL_Latin1_General_CP1_CI_AS,DOMAIN_CATALOG=AdventureWorks2014,DOMAIN_SCHEMA=dbo,DOMAIN_NAME=Name
TABLE_CATALOG=AdventureWorks2014,TABLE_SCHEMA=Sales,TABLE_NAME=vStoreWithContacts,COLUMN_NAME=ContactType,ORDINAL_POSITION=3,COLUMN_DEFAULT=,IS_NULLABLE=NO,DATA_TYPE=nvarchar,CHARACTER_MAXIMUM_LENGTH=50,CHARACTER_OCTET_LENGTH=100,NUMERIC_PRECISION=,NUMERIC_PRECISION_RADIX=,NUMERIC_SCALE=,DATETIME_PRECISION=,CHARACTER_SET_CATALOG=,CHARACTER_SET_SCHEMA=,CHARACTER_SET_NAME=UNICODE,COLLATION_CATALOG=,COLLATION_SCHEMA=,COLLATION_NAME=SQL_Latin1_General_CP1_CI_AS,DOMAIN_CATALOG=AdventureWorks2014,DOMAIN_SCHEMA=dbo,DOMAIN_NAME=Name
...