mssql_grep

#!perl
# vi:set ts=8 sw=4 et:
#
# mssql_grep  - print MS SQL Server 2000 tables/fields matching a pattern
#
# Author: Scott R. Keszler 
# 
# $Id: mssql_grep.pl,v 1.2 2004-10-23 10:52:39-05 owner Exp owner $
# $Log: mssql_grep.pl,v $
# Revision 1.2  2004-10-23 10:52:39-05  owner
# Added RCS keywords
#
#
# SQL Server 2000 field types:
#
# INTEGER
#   bigint           Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
#   int              Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
#   smallint         Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).
#   tinyint          Integer data from 0 through 255.
#   bit              Integer data with either a 1 or 0 value.
# DECIMAL
#   decimal          Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1. 
#   numeric          Functionally equivalent to decimal.
#   money            Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807)
#   smallmoney       Monetary data values from -214,748.3648 through +214,748.3647
#   float            Floating precision number data from -1.79E + 308 through 1.79E + 308.
#   real             Floating precision number data from -3.40E + 38 through 3.40E + 38.
# DATETIME
#   datetime         Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second
#   smalldatetime    Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
# CHARACTER
#   char             Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
#   varchar          Variable-length non-Unicode data with a maximum of 8,000 characters.
#   nchar            Fixed-length Unicode data with a maximum length of 4,000 characters. 
#   nvarchar         Variable-length Unicode data with a maximum length of 4,000 characters.
#   sysname          functionally equivalent to nvarchar(128) and is used to reference database object names.
# TEXTBLOB
#   text             Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
#   ntext            Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
# BINARY
#   binary           Fixed-length binary data with a maximum length of 8,000 bytes.
#   varbinary        Variable-length binary data with a maximum length of 8,000 bytes.
#   image            Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.
# OTHER
#   cursor           A reference to a cursor.
#   sql_variant      A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.
#   table            A special data type used to store a result set for later processing .
#   timestamp        A database-wide unique number that gets updated every time a row gets updated.
#   uniqueidentifier A globally unique identifier (GUID).
$VERSION = 1.1;
 
use strict;
use warnings;
use DBI;
use DBD::ODBC;
use Getopt::Std;
$Getopt::Std::STANDARD_HELP_VERSION = 1;
our ($opt_h, $opt_d, $opt_u, $opt_p, $opt_t, $opt_v, $opt_l, $opt_L, $opt_i, $opt_T, $opt_N, $opt_D, $opt_S, $opt_s, $opt_f, $opt_r, $opt_R, $opt_I);
our ($r_up, $r_lo, $R_up, $R_lo);

sub HELP_MESSAGE {
    my ($basename) = ($0 =~ /^(?:.*[:\\\/])?(.*)/s);
    print "
usage: $basename -h host -d db -u user -p passwd 
                 -t &| -v 
                 -L &| -l \"SQL like expression\" 
                 [-i] [-T] [-N] [-D] [-s] [-S] [-f file]

  Required:
    -h SQL Server hostname
    -d database name
    -u username (allowed to read host/database)
    -p password

  Use: one or both:
    -t seach tables
    -v search views

  Use: one or both:
    -L list all table and field names found, and distinct types
    -l SQL \"like\" expression for which to search in fields
       (use -l \"\%expression\%\" to find a substring)
       (use \\t for tab, \\r for return, \
 for newline)

  Optional:
    -i perform case-insensitive search
    -T search text/ntext fields also (limited to first 65,535 characters)
    -N search numeric fields also (cast as varchar)
    -D search date/time fields also (cast as varchar)
    -s search system tables/views only
    -S search system tables/views also (overrides -s)
    -f filename for results
    -r search/replace search string (requires -R)
    -R search/replace replace string (requires -r)
    -I search/replace all-upper and all-lower case variants of -r and -R
";
}

getopts('h:d:u:p:tvl:LiITNDSsf:r:R:');
if (!($opt_h and $opt_d and $opt_u and $opt_p)) { die("Values are required for all of -h, -d, -u, and -p\n"); }
if (!($opt_t or $opt_v)) { die("At least one of -t or -v is required\n"); }
if (!($opt_l or $opt_L)) { die("Nothing to do: no -l or -L\n"); }
if ($opt_l) {
    $opt_l =~ s/\\t/\t/g;
    $opt_l =~ s/\\r/\r/g;
    $opt_l =~ s/\\n/\n/g;
}
if (($opt_r and ! $opt_R) or ($opt_R and ! $opt_r)) { die("both -r and -R must be supplied if either is\n"); }
if ($opt_r and $opt_i) { $r_up = uc $opt_r; $r_lo = lc $opt_r; $R_up = uc $opt_R; $R_lo = lc $opt_R; }
if ($opt_f) { open OUT, "> $opt_f"; close OUT; }

my %tables;
my $srcDSN = "driver={SQL Server};Server=$opt_h;database=$opt_d;uid=$opt_u;pwd=$opt_p;";
my $conn  = DBI->connect("dbi:ODBC:$srcDSN", {AutoCommit => 1, PrintError => 0, RaiseError => 1, ShowErrorStatement => 1,}) or die "$DBI::errstr\n";
$conn->{LongReadLen} = 65535;

# setup - get all table names and ids
if ($opt_t) {
    my $count = 0;
    my $types = "= 'U'";
    $types = "= 'S' or o.name = 'dtproperties'" if $opt_s;
    $types = "in ('S','U')" if $opt_S;
    eval {
        my $stmt = $conn->prepare("select o.name, o.id, u.name from sysobjects o join sysusers u on o.uid = u.uid where xtype $types");
        $stmt->execute();
        while (my @list = $stmt->fetchrow_array()) {
            if ($list[0] eq "dtproperties") {
                next unless $opt_s or $opt_S;
            }
            $tables{$list[1]}{'name'} = $list[0];
            $tables{$list[1]}{'TorV'} = "T";
            $tables{$list[1]}{'owner'} = $list[2];
            $count++;
        }
    };
    if ($@) { die("FAIL: getting table names: $@"); }
    print "Table count: $count\n";
}

# setup - get all view names and ids
if ($opt_v) {
    my $count = 0;
    my $sysvw_re = qr/(sysconstraints|syslogins|sysopentapes|sysremotelogins|sysoledbusers|syssegments|systargetservers_view|sysjobs_view|systasks|systasks_view)/i;
    eval {
        my $stmt = $conn->prepare("select o.name, o.id, u.name from sysobjects o join sysusers u on o.uid = u.uid where xtype = \'V\'");
        $stmt->execute();
        while (my @list = $stmt->fetchrow_array()) {
            if ($list[0] =~ $sysvw_re) {
                ## system view
                next unless $opt_s or $opt_S;
            } else {
                ## user view
                next if $opt_s and !$opt_S;
            }
            $tables{$list[1]}{'name'} = $list[0];
            $tables{$list[1]}{'TorV'} = "V";
            $tables{$list[1]}{'owner'} = $list[2];
            $count++;
        }
    };
    if ($@) { die("FAIL: getting view names: $@"); }
    print "View count: $count\n";
}

# setup - get all column names for all tables and views
{
    my $count = 0;
    foreach my $id (keys %tables) {
        eval {
            my $stmt = $conn->prepare("select sc.name, st2.name as type from syscolumns sc INNER JOIN systypes st1 on sc.xusertype=st1.xusertype INNER JOIN systypes st2 on st1.xtype=st2.xusertype where id = $id");
            $stmt->execute();
            while (my ($nam, $typ) = $stmt->fetchrow_array()) {
                $tables{$id}{'cols'}{$nam} = $typ;
                $count++;
            }
        };
        if ($@) {
            die("FAIL: getting column names for $tables{$id}{'name'}: $@");
        }
    }
    print "Table/View * Column count: $count\n";
}

if ($opt_L) {
    my %alltypes;
    open OUT, ">> $opt_f" || die("cannot open $opt_f: $!\n") if $opt_f;
    foreach my $id (sort { $tables{$a}{'name'} cmp $tables{$b}{'name'} } keys %tables) {
        print $tables{$id}{'TorV'} eq "V" ? "view : " : "table: ", $tables{$id}{'name'},$/;
        print OUT $tables{$id}{'TorV'} eq "V" ? "view : " : "table: ", $tables{$id}{'name'},$/ if $opt_f;
        foreach my $col (sort keys %{$tables{$id}{'cols'}}) {
            printf " *** %-16s %s\n", $tables{$id}{'cols'}{$col}, $col;
            printf OUT " *** %-16s %s\n", $tables{$id}{'cols'}{$col}, $col if $opt_f;
            $alltypes{$tables{$id}{'cols'}{$col}}++;
        }
    }
    print "column types found:\n",(map { " *  $_\n" } sort keys %alltypes);
    print OUT "\ncolumn types found:\n",(map { " *  $_\n" } sort keys %alltypes) if $opt_f;
    close OUT if $opt_f;
}

if ($opt_l) {
    open OUT, ">> $opt_f" || die("cannot open $opt_f: $!\n") if $opt_f;
    my $chr_re = qr/(char|varchar|nchar|nvarchar|sysname|sql_variant)/i;
    my $num_re = qr/(bigint|int|smallint|tinyint|decimal|numeric|money|smallmoney|float|real)/i;
    my $dat_re = qr/(datetime|smalldatetime)/i;
    my $txt_re = qr/(text|ntext)/i;
    my $skip_re = qr/(cursor|table|timestamp|uniqueidentifier|bit|binary|varbinary|image)/i;
    my %replace = () if $opt_r;
    foreach my $id (keys %tables) {
        my $TorV = $tables{$id}{'TorV'} eq "V" ? "view " : "table";
        foreach my $col (keys %{$tables{$id}{'cols'}}) {
            next if $tables{$id}{'cols'}{$col} =~ $num_re and !$opt_N;
            next if $tables{$id}{'cols'}{$col} =~ $dat_re and !$opt_D;
            next if $tables{$id}{'cols'}{$col} =~ $txt_re and !$opt_T;
            next if $tables{$id}{'cols'}{$col} =~ $skip_re;
            print "Checking $TorV: $tables{$id}{'name'}  column: $col type: $tables{$id}{'cols'}{$col}\n";
            print OUT "Checking $TorV: $tables{$id}{'name'}  column: $col type: $tables{$id}{'cols'}{$col}\n" if $opt_f;
            eval {
                my ($stmt,$fld,$sfld,$exp);
                if ($tables{$id}{'cols'}{$col} !~ /sql_variant/i and ($tables{$id}{'cols'}{$col} =~ $chr_re or $opt_T and $tables{$id}{'cols'}{$col} =~ $txt_re)) {
                    $fld = $col;
                } else {
                    $fld = "convert(varchar(8000),$col)";
                }
                $sfld = $fld;
                $exp = "'$opt_l'";
                if ($opt_i) {
                    $sfld = "upper($fld)";
                    $exp = "upper($exp)";
                }
                $stmt = $conn->prepare("select a.[$fld] from $tables{$id}{'owner'}.$tables{$id}{'name'} a where a.[$fld] like $exp");
                $stmt->execute();
                while (my $val = $stmt->fetchrow_array()) {
                    print "  FOUND! $TorV : $tables{$id}{'name'}  column: $col  value: $val\n";
                    print OUT "FOUND! $TorV : $tables{$id}{'name'}  column: $col  value: $val\n" if $opt_f;
                    if ($opt_r) {
                        my $repl = $val;
                        if ($opt_I) {
                            $repl =~ s/$r_lo/$R_lo/g;
                            $repl =~ s/$r_up/$R_up/g;
                        } elsif ($opt_i) {
                            $repl =~ s/$opt_r/$opt_R/i;
                        } else {
                            $repl =~ s/$opt_r/$opt_R/;
                        }
                        $val = $conn->quote($val);
                        $repl = $conn->quote($repl);
                        $replace{$tables{$id}{'name'}}{$col}{$repl} = $val unless $repl_up eq $val;
                    }
                }
            };
            if ($@) { die("FAIL: getting value for $tables{$id}{'name'}.$col: $@"); }
        }
    }
    if ($opt_r) {
        print "\n\n";
        print OUT "\n\n" if $opt_f;
        foreach my $tbl (keys %replace) {
            foreach my $col (keys %{$replace{$tbl}}) {
                foreach my $repl (keys %{$replace{$tbl}{$col}}) {
                    print "update $tbl set $col = $repl where $col like $replace{$tbl}{$col}{$repl}\n";
                    print OUT "update $tbl set $col = $repl where $col like $replace{$tbl}{$col}{$repl}\n" if $opt_f;
                    my $stmt = $conn->prepare("update $tbl set $tbl.[$col] = $repl where $tbl.[$col] like $replace{$tbl}{$col}{$repl}");
                    my $rv = $stmt->execute();
                    if ($rv) {
                        print "*** $rv rows updated ***\n";
                        print OUT "*** $rv rows updated ***\n" if $opt_f;
                    } else {
                        print "#!#!#!#! Error executing above update !#!#!#!#\n";
                        print OUT "#!#!#!#! Error executing above update !#!#!#!#\n" if $opt_f;
                        exit 99;
                    }
                }
            }
        }
    }
    $conn->disconnect();
    close OUT if $opt_f;
}
          

This page, and all contents, are Copyright © 1994 by SRK Consulting, Fargo, North Dakota, 58102.