Obtain column names via Perl DBI

Question

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) {
    # 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!

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. — ErikE, Jan 21 '11 at 23:06
Nonesense. information_schema is available in many databases including MS SQL Server. — bohica, Jan 24 '11 at 08:56
INFORMATION_SCHEMA.COLUMNS is a mysql5 thing. It isnt available in mysql 4.1 — knb, Jan 24 '11 at 17:38
So apparently @Emtucifor is correct that the collation is case sensitive on my server hence after changing everything to upper case, I got the query working, and I was able to extract the column names that way. Thx everyone. — Zhang18, Jan 24 '11 at 17:59
@DVK I'm sorry to say this, but that's incorrect. INFORMATION_SCHEMA.COLUMNS is a SQL Server thing, too. In the same schema are CHECK_CONSTRAINTS, COLUMN_DOMAIN_USAGE, COLUMN_PRIVILEGES, CONSTRAINT_COLUMN_USAGE, CONSTRAINT_TABLE_USAGE, DOMAIN_CONSTRAINTS, DOMAINS, KEY_COLUMN_USAGE, PARAMETERS, REFERENTIAL_CONSTRAINTS, ROUTINE_COLUMNS, ROUTINES, SCHEMATA, TABLE_CONSTRAINTS, TABLE_PRIVILEGES, TABLES, VIEW_COLUMN_USAGE, and VIEWS. Some of these may be SQL 2005 and up only, but many are available in SQL 2000. — ErikE, Jan 24 '11 at 21:33

Jonathan Leffler · Answer 1 · 2011-01-22T21:25:38.157

5

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).

edited Jan 22 '11 at 21:25

answered Jan 22 '11 at 00:42

Jonathan Leffler

730,956
141
904
1,278

I think $sth->{NAME} must be called at a certain point in time (before data are accessed in a loop). It goes out of scope early, if I remember correctly. – knb Jan 22 '11 at 22:31
@knb: that might depend on the driver. For sure, it doesn't go out of scope in DBD::Informix until the handle is destroyed. – Jonathan Leffler Jan 22 '11 at 23:50
@jonathan-leffler: I was talking about DBD::Sybase in particular. Maybe once I tried to access $sth->{NAME} after the subroutine where $sth was created went out of scope. Wasnt able to look up the code example yesterday (and today, neither). – knb Jan 24 '11 at 17:44
@jonathan-leffler: You have to $sth->execute() before you can get $sth->{NAME}. I usually add a "WHERE 1=0" also if that sort of info is all I want. (I believe) Some db's return the entire result set to some client layer before fetching. – runrig Jan 24 '11 at 19:37
@runrig: you may be right for some DBD's; DBD::Informix does not require execute before you get the column names. – Jonathan Leffler Jan 24 '11 at 20:03
@jonathan-leffler: Ah, right, some DBD's is what the docs say. I did not know that about Informix, though it does make sense for databases that have an actual "prepare" phase. – runrig Jan 24 '11 at 22:52
@runrig: I do know about DBD::Informix - I wrote it. I can't answer for the other DBD modules so definitively. – Jonathan Leffler Jan 24 '11 at 23:18

score 3 · Answer 2 · answered Jan 21 '11 at 22:37

3

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

answered Jan 21 '11 at 22:37

Dave Cross

68,119
3
51
97

score 0 · Accepted Answer · answered Jan 24 '11 at 21:29

0

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.

answered Jan 24 '11 at 21:29

ErikE

48,881
23
151
196

Obtain column names via Perl DBI

3 Answers3