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)

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

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.

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.