Obtain column names via Perl DBI

In SQL Server Management Studio 2008, I can run

sp_columns MY_TABLE

to get all the column names (under COLUMN_NAME). However, how can I obtain the same information using Perl DBI?

In particular, I tried

my $result = $dbh->selectall_hashref("sp_columns MY_TABLE", 'COLUMN_NAME');

hoping that the column names would be the keys of the returned hash.

In fact, even if that's successful, that's not what I want as I need to preserve the order of columns in that table. So I tried

my $sth = $dbh->prepare("sp_columns $table");
$sth->execute();
while (my @row = $sth->fetchrow_array) {
   开发者_StackOverflow # process @row;
}

But neither works. I got the error message of

DBD::Sybase::db selectall_hashref failed: Server message number=102 severity=15 state=1 line=1 server=XXXX text=Incorrect syntax near '.'

I also referenced this post. Apparently the following query does NOT work in my Management Studio:

select * from information_schema.columns where table_name = MY_TABLE

with the error message of

Msg 208, Level 16, State 1, Line 2
Invalid object name 'information_schema.columns'.

Please help? Thx!

The returned column names are an attribute of a prepared statement, so you can use:

my $dbh = DBI->connect('dbi:DBMS:...','','');
my $sth = $dbh->prepare("SELECT * FROM SomeTable");

and now $sth->{NAME} (an array reference) contains the names of the columns (as aliased, etc).

See the catalogue methods that DBI supports. In particular, I expect you want table_info.

If you're getting invalid object name on INFORMATION_SCHEMA.COLUMNS it may be that you don't have permission. If your collation is case sensitive you also need to use all caps. But it definitely exists in SQL Server.