OK, Steve. This information should be enough for me.
I tried it and after some days reading a book on my
bed (I was sick), finally I master this metadata.
Thanks Steve. The basic of the perl code command line
for dumping data dictionary and generate xls file
succeeded. Now I step furthermore for doing its GUI.
Thanks.
--- "Steve Howard (PFE)" <sthoward microsoft.com>
wrote:
> I'll see if I can give you a start with this, and
> tell you where to look for the rest of it.
>
> There may be several ways to get the table names,
> column names, etc. but one standardized way that is
> fully support by SQL are the INFORMATION_SCHEMA
> views. A quick and dirty I wrote to query the
> INFORMATION_SCHEMA.TABLES in SQL 2005 is below. I
> included a couple of little tricks I like to use so
> I can associate the column names from the statement
> handle meta-data with the columns themselves. The
> main thing you need to be able to do what you are
> asking is to be able to access the
> INFORMATION_SCHEMA, though, so that is the main
> thing you can concentrate on.
>
>
> use DBI;
>
> my $dbh =
DBI->connect("dbi:ODBC:sql2k5demo",
> 'changed', 'changed') or die $DBI::errstr;
>
> $dbh->do('use adventureworks') || die "Can't
use
> adventureworks: $DBI::errstrn";
>
> my $sth = $dbh->prepare(qq{SELECT * from
> INFORMATION_SCHEMA.TABLES}) || die "Can't prepare
> select of tables: $DBI::errstrn";
>
> $sth->execute() || die "Can't execute select
of
> tables: $DBI::errstrn";
>
> # get the information about the tables:
>
> # here is a trick you can use to get the names of
> columns as you do this:
>
> my columns = {$sth->};
>
> # thanks to the last statement, your column names
> are now stored in columns
> #demo this:
>
> print join(",t", columns) . "n";
>
> # now that we have the column names, we can
> associate the column names to the data by binding
> # the columns into a hash using the column names as
> hash keys:
>
> my %data;
>
> $sth->bind_columns(undef, data{columns});
>
> # fetch the data:
>
> while ($sth->fetch)
> {
> # get the rows by the column names:
> print join(",t", data{columns}) .
"n";
> }
>
>
> Look in SQL Server Books online, and go to the index
> tab. Type in INFORMATION_SCHEMA, and you will see a
> list of INFORMATION_SCHEMA views in the left pane.
> These will tell you pretty much anything you need to
> know about a database. I think the main views you
> might want to look at are the
> INFORMATION_SCHEMA.TABLES and the
> INFORMATION_SCHEMA.COLUMNS views. Look over the
> other view to get the constraint information and key
> information.
>
> Hope this helps,
>
> Steve Howard
>
> From:
> perl-win32-database-bounceslistserv.ActiveState.com
>
[mailto:perl-win32-database-bounceslistserv.ActiveState.com]
> On Behalf Of Patrix Diradja
> Sent: Thursday, February 08, 2007 3:30 PM
> To: Milis perl-win32-database
> Subject: Query metadata (data dictionary) with perl
>
> Dear my friends....
>
> Would you be so kind to give a sample of perl script
> to query (select or whatever) the data dictionary of
> a database.
>
> I use MS SQL Server 2005.
> And the field information I need are:
> Field Name
> Field Type
> Field Size
> Key(primary key or foreign key)
> Mandatory/Optional (is_Nullable)
> Description.
>
> And how can I insert/update the 'Desciption' field?
>
> Thank you very much.
> ________________________________
> Sekarang dengan penyimpanan 1GB
>
http://id.ma
il.yahoo.com/<http://sg.rd.yahoo.com/mail/id/footer/def/*
http:/id.mail.yahoo.com/>
>
________________________________________________________
Sekarang dengan penyimpanan 1GB
http://id.mail.yahoo.com/
_______________________________________________
Perl-Win32-Database mailing list
Perl-Win32-Databaselistserv.ActiveState.com
To unsubscribe: http:/
/listserv.ActiveState.com/mailman/mysubs
|
