DBIx::TableReferences::MSSQL -- Perl extension for getting information about declared referential constraints in MS SQL Server 2000 databases.
use DBIx::TableReferences::MSSQL; $tr = DBIx::TableReferences::MSSQL->new( $dbh ); my $table = 'sales'; @reftables = $tr->reftables($table); print "'$table' references these tables: @reftables\n";
Output (assuming $dbh is connected to the pubs database)
$dbh
pubs
'sales' references these tables: dbo.stores dbo.titles
Want more information?
$refdetails = $tr->references('sales'); for $ref (@{$refdetails}) { # The owner.name of the referenced table print "$ref->{refowner}.$ref->{reftable}\n"; # columns in the referential constraint @fkeys = @{$ref->{cols}}; # FK @rkeys = @{$ref->{refcols}}; # PK while ($fkey = shift @fkeys, $rkey = shift @rkeys) { print "\t$fkey -> $rkey\n" } }
Output (showing the columns involved in the referential constraints)
dbo.stores stor_id -> stor_id dbo.titles title_id -> title_id
DBIx::TableReferences::MSSQL aims to provide information about declared table relationships, aka table references, in MS SQL Server 2000 databases.
To say that table A references table B is a shortcut for saying table A has a foreign key that exists as a primary key in table B.
$tr = DBIx::TableReferences::MSSQL->new($dbh);
Instantiates and returns the object. Retrieves all table reference information from the database and stores it internally, ready for querying via tablerefs or references.
tablerefs
references
You must pass this method a valid DBI connection to a MS SQL Server 2000 database, shown here as $dbh.
@tables = $tr->tablerefs('titles'); print $_,"\n" for @tables;
Returns a list of tables that are referenced by the table 'dbo.titles'. If you do not specify the owner of the table, 'dbo' is assumed.
tablerefs is just a wrapper method that calls references in list context.
@tables = $tr->references('titles'); # list context
Returns a list of table names exactly as though you had called $tr->tablerefs('titles').
$refs = $tr->references('titles'); # scalar context
Returns a reference to an array of hashes with the following structure -
$refs = [ { 'owner' => 'dbo', 'table' => 'titles', 'cols' => [ 'pub_id' ], 'refowner' => 'dbo', 'reftable' => 'publishers', 'refcols' => [ 'pub_id' ], 'sql_add' => 'ALTER TABLE [dbo].[titles] ADD CONSTRAINT ...', 'sql_drop' => 'ALTER TABLE [dbo].[titles] DROP CONSTRAINT ...', 'constraint' => 'FK__titles__pub_id__619B8048' } ];
owner
The owner of the table in question. Often 'dbo', since that is the default owner.
table
The name of the table in question.
cols
A list of columns in table that participate in the referential constraint. There is often just one column, but there can be up to sixteen. These columns constitute a foreign key in table.
refowner
The owner of the related table.
reftable
The name of the related table.
refcols
A list of columns in reftable that participate in the referential constraint. There is often just one column but there can be up to sixteen. These columns constitute the primary key in reftable.
sql_add
A runnable SQL statement that will add (create) the referential constraint.
Examples -
ALTER TABLE [dbo].[titles] ADD CONSTRAINT [FK__titles__pub_id__619B8048] FOREIGN KEY ( [pub_id] ) REFERENCES [dbo].[publishers] ( [pub_id] )
Two columns in this relationship -
ALTER TABLE [dbo].[titles] ADD CONSTRAINT [FK__titles__contrived] FOREIGN KEY ( [pub_id], [title_id] ) REFERENCES [dbo].[contrived_table] ( [pub_id], [title_id] )
sql_drop
A runnable SQL statement that will drop the referential constraint.
Example -
ALTER TABLE [dbo].[titles] DROP CONSTRAINT [FK__titles__pub_id__619B8048]
constraint
The name of the foreign key constraint. If a name was not supplied to MSSQL when the constraint was first created, it will tend to look a bit random, like this: FK__titles__pub_id__619B8048.
FK__titles__pub_id__619B8048
This example prints all the referential constraints as DROP statements and then as ALTER statements.
use strict; use warnings; use DBIx::TableReferences::MSSQL; use DBC; # my custom database connector my $dbh = DBC->connect({database => 'pubs'}); my $tr = DBIx::TableReferences::MSSQL->new( $dbh ); my $sql = "select user_name(uid), name from sysobjects where xtype='U'"; my @tables = map {"$_->[0].$_->[1]"} @{$dbh->selectall_arrayref($sql)}; for my $table (@tables) { my $r = $tr->references('titles'); for my $ref (@{$tr->references($table)}) { print $ref->{sql_drop},"\n"; } } for my $table (@tables) { for my $ref (@{$tr->references($table)}) { print $ref->{sql_add},"\n"; } }
Edward Guiness <EdwardG@cpan.org>
The structure of the MS SQL 2000 sysreferences table.
None by default.
Copyright (C) 2004 by Edward Guiness
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.3 or, at your option, any later version of Perl 5 you may have available.
To install DBIx::TableReferences::MSSQL, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::TableReferences::MSSQL
CPAN shell
perl -MCPAN -e shell install DBIx::TableReferences::MSSQL
For more information on module installation, please visit the detailed CPAN module installation guide.