NAME
DBIx::TableReferences::MSSQL -- Perl extension for getting information about declared referential constraints in MS SQL Server 2000 databases.
SYNOPSIS
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)
'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
DESCRIPTION
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.
METHODS
new
$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
.
You must pass this method a valid DBI connection to a MS SQL Server 2000 database, shown here as $dbh
.
tablerefs
@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.
references
@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 intable
.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 inreftable
.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
.
EXAMPLES
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";
}
}
AUTHOR
Edward Guiness <EdwardG@cpan.org>
DEPENDENCIES
The structure of the MS SQL 2000 sysreferences table.
EXPORTS
None by default.
COPYRIGHT AND LICENSE
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.