One of my most used and least understood perl snippets

# ----- this perl snippet will remove any leading or trailing white spaces
# ----- all spaces in the variable will NOT be reduced to one space
 
my $variable =~ s/^\s*(.*\S)\s*$/$1/; # trm ld/trl whtspc

Creating a SQL Server view - part 2

We'll need to present several sql commands to the server in order to create/update a view.  So let's talk about what's needed to execute a query from perl on a SQL Server.

First, let's initialize the connection:

use DBI();
my $SQL_Server   = 'changeme';

my $SQL_User     = 'changeme';

my $SQL_password = 'changeme';

my $inputfile = @ARGV[0];

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

# ----- you can enable tracing if you'd like:

#$dbh->trace(1);;
 

# ----- My SQL Server wants a couple of settings set before executing a query, set them here.

 

$dbh->do("SET ANSI_NULLS ON");
$dbh->do("SET ANSI_WARNINGS ON");
 

# ----- set up the query

my $drop_query = "USE $Database; IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[$inputfile]')) DROP VIEW [dbo].[$inputfile]";
 

# ----- execute it

$dbh->do($drop_query);
 

# ----- report any errors

warn "view check terminated early by error: $DBI::errstr\n" if $DBI::err;
 

# ----- disconnect

$dbh->disconnect or warn "Error disconnecting: $DBI::errstr\n";
 

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:

#!/usr/bin/perl

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");
    }
    $database_count++;

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