Sybase-Metadata version 0.01 ============================ The README is used to introduce the module and provide instructions on how to install the module, any machine dependencies it may have (for example C compilers and installed libraries) and any other information that should be provided before the module is installed. A README file is required for CPAN modules since CPAN extracts the README file from a module distribution so that people browsing the archive can use it get an idea of the modules uses. It is usually a good idea to provide version information here so that people can decide whether fixes for the module are worth downloading. INSTALLATION To install this module type the following: perl Makefile.PL make make test make install DEPENDENCIES This module requires these other modules and libraries: DBI DBD:Sybase Examples - NB: You MUST have DBI and DBD:Sybase insatlled to use this module! #################################################################### # # new # + Construct object and return handle # # + Input: None # # + Output: Object handle/pointer # #################################################################### Example: my $mdHandle = Sybase::Metadata->new(); #################################################################### # # Initialize # + Initialize desired db connection and return global handle # NB: You MUST have DBI and DBD:Sybase insatlled to use this module! # # + Input: Pointer to hash of DB properties containing: # - Server # - User # - Password # - Database # # + Output: None but initializes db handle to be used internally # #################################################################### Example: my %dbHash = ( SERVER => 'BIGDB_SERVER', USER => 'SOME_USER', PASSWORD => 'changeme', DATABASE => 'BIGDB_DEV'); my $hashPtr = \%dbHash; my $mdHandle = Sybase::Metadata->new(); $mdHandle->Initialize($hashPtr); #################################################################### # # GetDatabases # + Get a list of all databases and their space usage info # + Input : None # + Output : Ref to array of hashes containing: # DBName # DBID # Owner # CreateDate # # #################################################################### Example: print "Testing GetDatabases ... \n"; my $dbListRef = $mdHandle->GetDatabases(); foreach ( @{$dbListRef}) { print "DBName = $_->{DBName}, DBID = $_->{DBID}, Owner = $_->{Owner}, CreateDate = $_->{CreateDate} \n"; } #################################################################### # # GetTables # + Get a list of tables in present database, Useful for drill # down to table details # + Input : None # + Output : Ref to array of hashes containing: # Name (name of table) # TableOID # Owner # CreateDate # # #################################################################### Example: print "\n Testing GetTables ... \n"; my $dbListRef = $mdHandle->GetTables(); foreach ( @{$dbListRef}) { print "Name = $_->{Name}, TableOID = $_->{TableOID}, Owner = $_->{Owner}, CreateDate = $_->{CreateDate} \n"; } #################################################################### # # GetProcs # + Get a list of tables in present database, Useful for drill # down to table details # + Input : None # + Output : Ref to array of hashes containing: # Name (name of proc) # ProcOID # Owner # CreateDate # #################################################################### Example: print "\n Testing GetProcs ... \n"; my $dbListRef = $mdHandle->GetProcs(); foreach ( @{$dbListRef}) { print "Name = $_->{Name}, ProcOID = $_->{ProcOID}, Owner = $_->{Owner}, CreateDate = $_->{CreateDate} \n"; } #################################################################### # # GetViews # + Get a list of views in present database, Useful for drill # down to view details # + Input : None # + Output : Ref to array of hashes containing: # Name (name of view) # ViewOID # Owner # CreateDate # #################################################################### Example: print "\n Testing GetViews ... \n"; my $dbListRef = $mdHandle->GetViews(); foreach ( @{$dbListRef}) { print "Name = $_->{Name}, ViewOID = $_->{ViewOID}, Owner = $_->{Owner}, CreateDate = $_->{CreateDate} \n"; } #################################################################### # # GetTriggers # + Get a list of views in present database, Useful for drill # down to view details # + Input : None # + Output : Ref to array of hashes containing: # TriggerName # TriggerOID # TableName # TableOID # #################################################################### Example: print "\n Testing GetTriggers ... \n"; my $dbListRef = $mdHandle->GetTriggers(); foreach ( @{$dbListRef}) { print "TriggerName = $_->{TriggerName}, TriggerOID = $_->{TriggerOID}, TableName = $_->{TableName}, TableOID = $_->{TableOID} \n"; } #################################################################### # # GetRIs # + Get a list of referential integrities in present database # + Input : None # + Output : Ref to array of hashes containing: # Name (name of referential inegtrity) # RIOID # FromTable # FromTableOID # ToTable # ToTableOID # #################################################################### Example: print "\n Testing GetRIs ... \n"; my $dbListRef = $mdHandle->GetRIs(); foreach ( @{$dbListRef}) { print "Name = $_->{Name}, RIOID = $_->{RIOID}, FromTable = $_->{FromTable}, FromTableOID = $_->{FromTableOID}, ToTable = $_->{ToTable}, ToTableOID = $_->{ToTableOID} \n"; } #################################################################### # # GetIndexes # + Get a list of indexes in present database # + Input : None # + Output : Ref to array of hashes containing: # Name (name of index) # OnTable # CreateDate # #################################################################### Example: print "\n Testing GetIndexes ... \n"; my $dbListRef = $mdHandle->GetIndexes(); foreach ( @{$dbListRef}) { print "Name = $_->{Name}, OnTable = $_->{OnTable}, CreateDate = $_->{CreateDate} \n"; } #################################################################### # # DescribeTable # + Get table details including column names, types, null/not null # + Input : Table Name # + Output : Ref to array of hashes containing: # Name (of column) # Type # NullType (NULL/NOT NULL) # #################################################################### Example: print "\n Testing DescribeTable ... \n"; my $dbListRef = $mdHandle->DescribeTable("MkEqTrade"); foreach ( @{$dbListRef}) { print "Name -> $_->{Name}, Type = $_->{Type}, NullType = $_->{NullType} \n"; } #################################################################### # # DescribeProc # + Get stored proc text excluding create statements, etc # + Input : Proc Name # + Output : Ref to array containing lines of text # #################################################################### Example: print "\n Testing DescribeProc ... \n"; my $dbListRef = $mdHandle->DescribeProc("MkGetEqProduct"); foreach ( @{$dbListRef}) { print "$_ "; } #################################################################### # # DescribeTrigger # + Get trigger text excluding create statements, etc # + Input : Trigger Name # + Output : Ref to array containing lines of text # #################################################################### Example: print "\n Testing DescribeTrigger ... \n"; my $dbListRef = $mdHandle->DescribeTrigger("trigEqProdUpd"); foreach ( @{$dbListRef}) { print "$_ "; } #################################################################### # # DescribeView # + Get view text excluding create statements, etc # + Input : ViewName # + Output : Ref to array containing lines of text # #################################################################### Example: print "\n Testing DescribeView ... \n"; my $dbListRef = $mdHandle->DescribeView("vEqHeaders"); foreach ( @{$dbListRef}) { print "$_ "; } #################################################################### # # GetUsers # + Get names/groups of all users in this database # + Input : None # + Output : Ref to array of hashes containing: # UserName # UserID # GroupName # GroupID # # #################################################################### Example: print "\n Testing GetUsers ... \n"; my $dbListRef = $mdHandle->GetUsers(); foreach ( @{$dbListRef}) { print "UserName = $_->{UserName}, UserID = $_->{UserID}, GroupName = $_->{GroupName}, GroupID = $_->{GroupID} \n"; } #################################################################### # # GetLogins # + Get names of all server level logins # + Input : None # + Output : Ref to hash containing: # LoginName # LoginID # DefaultDB # #################################################################### Example: print "\n Testing GetLogins ... \n"; my $dbListRef = $mdHandle->GetLogins(); foreach ( @{$dbListRef}) { print "LoginName = $_->{LoginName}, LoginID = $_->{LoginID}, DefaultDB = $_->{DefaultDB} \n"; } #################################################################### # # GetGroups # + Get names of all groups in present database # + Input : None # + Output : Ref to hash containing: # GroupName # GroupID # #################################################################### Example: print "\n Testing GetGroups ... \n"; my $dbListRef = $mdHandle->GetGroups(); foreach ( @{$dbListRef}) { print "GroupName = $_->{GroupName}, GroupID = $_->{GroupID} \n"; } #################################################################### # # GetGroupMembers # + Get list of all members of a given group # + Input : GroupName # + Output : Ref to hash containing: # UserName # UserID # #################################################################### Example: print "\n Testing GetGroupMembers ... \n"; my $dbListRef = $mdHandle->GetGroupMembers("app_group"); foreach ( @{$dbListRef}) { print "UserName = $_->{UserName}, UserID = $_->{UserID} \n"; } #################################################################### # # ExtractTableSQL # + Get entire stored proc with drop/create statements # + Input : Table Name # + Output : Ref to array containing text # #################################################################### Example: print "\n Testing ExtractTableSQL ... \n"; my $dbListRef = $mdHandle->ExtractTableSQL("MkEqProductLog"); foreach ( @{$dbListRef}) { print "$_ "; } #################################################################### # # ExtractViewSQL # + Get entire view with drop/create statements # + Input : View Name or View OID # + Output : Ref to array containing text # #################################################################### Example: print "\n Testing ExtractViewSQL ... \n"; my $dbListRef = $mdHandle->ExtractViewSQL("vEqHeaders"); foreach ( @{$dbListRef}) { print "$_ "; } #################################################################### # # ExtractProcSQL # + Get entire stored procedure with drop/create statements # + Input : Proc Name or Proc OID # + Output : Ref to array containing text # #################################################################### Example: print "\n Testing ExtractProcSQL ... \n"; my $dbListRef = $mdHandle->ExtractProcSQL("MkGetEqProduct"); foreach ( @{$dbListRef}) { print "$_ "; } #################################################################### # # ExtractTriggerSQL # + Get entire trigger with drop/create statements # + Input : Trigger Name or Trigger OID # + Output : Ref to array containing text # #################################################################### Example: print "\n Testing ExtractTriggerSQL ... \n"; my $dbListRef = $mdHandle->ExtractTriggerSQL("trigEqProdUpd"); foreach ( @{$dbListRef}) { print "$_ "; } #################################################################### # # ExtractRISQL # + Get entire referential integrity with drop/create statements # + Input : RI Name # + Output : Ref to array containing text # #################################################################### Example: print "\n Testing ExtractRISQL ... \n"; my $dbListRef = $mdHandle->ExtractRISQL("FK_EQTRDATTR_TRDID"); foreach ( @{$dbListRef}) { print "$_ "; } #################################################################### # # SearchProcNames # + Search proc names for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # ProcName # ProcOID # #################################################################### Example: print "\n Testing SearchProcNames ... \n"; my $dbListRef = $mdHandle->SearchProcNames("%Get%"); foreach ( @{$dbListRef}) { print "ProcName = $_->{ProcName}, ProcOID = $_->{ProcOID} \n"; } #################################################################### # # SearchProcText # + Search proc text for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # ProcName # ProcOID # Snippett (text within proc containing pattern) # #################################################################### Example: print "\n Testing SearchProcText ... \n"; my $dbListRef = $mdHandle->SearchProcText("%select%"); foreach ( @{$dbListRef}) { print "ProcName = $_->{ProcName}, ProcOID = $_->{ProcOID}, Snippett = $_->{Snippett} \n"; } #################################################################### # # SearchTriggerNames # + Search trigger names for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # TriggerName # TriggerOID # TableName # TableOID # #################################################################### Example: print "\n Testing SearchTriggerNames ... \n"; my $dbListRef = $mdHandle->SearchTriggerNames("%[Uu]pd%"); foreach ( @{$dbListRef}) { print "TriggerName = $_->{TriggerName}, TriggerOID = $_->{TriggerOID}, TableName = $_->{TableName}, TableOID = $_->{TableOID} \n"; } #################################################################### # # SearchTriggerText # + Search trigger text for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # TriggerName # TriggerOID # TableName # TableOID # Snippett (piece of code containing pattern) # #################################################################### Example: print "\n Testing SearchTriggerText ... \n"; my $dbListRef = $mdHandle->SearchTriggerText("%ISIN%"); foreach ( @{$dbListRef}) { print "TriggerName = $_->{TriggerName}, TriggerOID = $_->{TriggerOID}, TableName = $_->{TableName}, TableOID = $_->{TableOID}, Snippett = $_->{Snippett} \n"; } #################################################################### # # SearchColumns # + Search column names for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # ColumnName # TableName # TableOID # #################################################################### Example: print "\n Testing SearchColumns ... \n"; my $dbListRef = $mdHandle->SearchColumns("%Product%"); foreach ( @{$dbListRef}) { print "ColumnName = $_->{ColumnName}, TableName = $_->{TableName}, TableOID = $_->{TableOID} \n"; } #################################################################### # # SearchTableNames # + Search table names for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # TableName # TableOID # #################################################################### Example: print "\n Testing SearchTableNames ... \n"; my $dbListRef = $mdHandle->SearchTableNames("%Product%"); foreach ( @{$dbListRef}) { print " TableName = $_->{TableName}, TableOID = $_->{TableOID} \n"; } #################################################################### # # SearchViewNames # + Search view names for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # ViewName # ViewOID # #################################################################### Example: print "\n Testing SearchViewNames ... \n"; my $dbListRef = $mdHandle->SearchViewNames("%Eq%"); foreach ( @{$dbListRef}) { print " ViewName = $_->{ViewName}, ViewOID = $_->{ViewOID} \n"; } #################################################################### # # SearchViewText # + Search view names for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # ViewName # ViewOID # Snippett (bit of view containing pattern) # #################################################################### Example: print "\n Testing SearchViewText ... \n"; my $dbListRef = $mdHandle->SearchViewText("%[Ss]elect%"); foreach ( @{$dbListRef}) { print " ViewName = $_->{ViewName}, ViewOID = $_->{ViewOID}, Snippett = $_->{Snippett} \n"; } #################################################################### # # SearchIndexNames # + Search index names for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # IndexName # TableName # TableOID # #################################################################### Example: print "\n Testing SearchIndexNames ... \n"; my $dbListRef = $mdHandle->SearchIndexNames("%EQ%"); foreach ( @{$dbListRef}) { print " IndexName = $_->{IndexName}, TableName = $_->{TableName}, TableOID = $_->{TableOID} \n"; } #################################################################### # # SearchUsers # + Search user names for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # UserName # UserID # #################################################################### Example: print "\n Testing SearchUsers ... \n"; my $dbListRef = $mdHandle->SearchUsers("%app%"); foreach ( @{$dbListRef}) { print " UserName = $_->{UserName}, UserID = $_->{UserID} \n"; } #################################################################### # # SearchGroups # + Search group names for a given text pattern or sybase # regular expression. Will validate regular expression first. # + Input : Pattern (string with Sybase Reg Ex optional) # + Output : Ref To Array of hashes containing: # GroupName # GroupID # #################################################################### Example: print "\n Testing SearchGroups ... \n"; my $dbListRef = $mdHandle->SearchGroups("%app%"); foreach ( @{$dbListRef}) { print " GroupName = $_->{GroupName}, GroupID = $_->{GroupID} \n"; } #################################################################### # # CloseConnection # + Clean up and close DB handle # + Input: None needed # #################################################################### Example: $mdHandle->CloseConnection(); =head1 SEE ALSO For more information on this and other modules written by the author see the website - http://www.bmobrien.net or email to mikeob723@gmail.com =head1 AUTHOR Mike O\'Brien, Emikeob723@gmail.com =head1 COPYRIGHT AND LICENSE Copyright (C) 2009 by Mike O\'Brien This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.10.0 or, at your option, any later version of Perl 5 you may have available. =cut COPYRIGHT AND LICENCE Put the correct copyright and licence information here. Copyright (C) 2010 by Mike O\'Brien This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.10.0 or, at your option, any later version of Perl 5 you may have available.