Saturday, June 02, 2012

Creating a SQL Server view - part 1

One of the things I do on a regular basis at work is query tables in databases that are identical in multiple databaseson the same instance of SQL Server.  For instance, there's 30 databases with a transaction table in each database that is identical in all 30 databases.  We need to create a unified view across all 30 databases that gives us the overall picture of transaction activity across the organization.

To do this, I use something like the following:


use warnings;
use strict;

use DBI();

my $database_count = 0;
my $TheView = 'ViewName'; # ----- what you'd like to call the resulting view.

my $SQL_Server = 'Fred';  # ----- your database server name here
my $user = 'barney';      # ----- your SQL Server user name goes here
my $password = 'sesame';  # ----- your SQL Server password goes here

my $query = join("", "CREATE VIEW [dbo].[",  $TheView, "] AS ");
my $base_table = 'table_name';  # ----- Some Table That Exists identically In All databases

# ----- Connect to database
my $dbh = DBI->connect("DBI:ODBC:$SQL_Server", "$user", "$password", {PrintError => 0, RaiseError => 1}) or die "Can't connect to $SQL_Server database: $DBI::errstr\n"; #$dbh->trace(1);;

# ----- get list of databases to act on
my $sth = $dbh->prepare("SELECT name FROM master.sys.databases WHERE name LIKE 'DB%' ORDER BY name ASC;") or die "Can't prepare SQL statement: $DBI::errstr\n"; #$sth->trace(2, 'trace1.lis');;
$sth->execute() or die "Can't execute SQL statement: $DBI::errstr\n";
while (my $ref = $sth->fetchrow_hashref()) {

    my $DataBase = $ref->{'name'};

    # ----- get ready to munge with the next database
    if ($database_count > 0) {
        $query = join("", $query, "\nUNION ALL\n\n");

    $query = join('', $query, 'SELECT ');

    # ----- Pull the column names from the master database in the SQL Server Instance
    my $dbh1 = DBI->connect("DBI:ODBC:$SQL_Server", "$user", "$password", {PrintError => 0, RaiseError => 0}) or die "Can't connect to $SQL_Server_Master database: $DBI::errstr\n"; #$dbh->trace(1);
    my $sth1 = $dbh1->prepare("SELECT name FROM master.sys.columns WHERE OBJECT_NAME(object_id) = '$base_table'") or die "Can't prepare SQL statement: $DBI::errstr\n"; #$sth->trace(2, 'trace.lis');
    $sth1->execute() or die "Can't execute SQL statement: $DBI::errstr\n";
    my $column_count = 0;
    while (my $ref1 = $sth1->fetchrow_hashref()) {

        my $column = $ref1->{'name'};

            # ---- here I put together a select query that includes every column from the table


I plan on adding detail to the select query and the code that actually drops the view and loads the new version next time.


No comments: