home *** CD-ROM | disk | FTP | other *** search
Text File | 2004-04-21 | 134.3 KB | 3,944 lines |
- Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
- Message-ID: <databases/sybase-faq/part18_1082468590@rtfm.mit.edu>
- Supersedes: <databases/sybase-faq/part18_1074677126@rtfm.mit.edu>
- Expires: 2 Aug 2004 13:43:10 GMT
- References: <databases/sybase-faq/part1_1082468590@rtfm.mit.edu>
- X-Last-Updated: 2003/03/02
- From: dowen@midsomer.org (David Owen)
- Newsgroups: comp.databases.sybase,comp.answers,news.answers
- Subject: Sybase FAQ: 18/19 - ASE Section 9 (3 of 3)
- Reply-To: dowen@midsomer.org (David Owen)
- Followup-To: comp.databases.sybase
- Distribution: world
- Organization: Midsomer Consultants Inc.
- Approved: news-answers-request@MIT.EDU
- Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
- Originator: faqserv@penguin-lust.MIT.EDU
- Date: 20 Apr 2004 13:45:17 GMT
- Lines: 3921
- NNTP-Posting-Host: penguin-lust.mit.edu
- X-Trace: 1082468717 senator-bedfellow.mit.edu 559 18.181.0.29
- Xref: senator-bedfellow.mit.edu comp.databases.sybase:106216 comp.answers:56962 news.answers:270302
-
- Archive-name: databases/sybase-faq/part18
- URL: http://www.isug.com/Sybase_FAQ
- Version: 1.7
- Maintainer: David Owen
- Last-modified: 2003/03/02
- Posting-Frequency: posted every 3rd month
- A how-to-find-the-FAQ article is posted on the intervening months.
-
- #!/usr/bin/perl
-
- # Author: Vincent Yin (umyin@mctrf.mb.ca) Aug 1994 Last Modified: May 1996
-
- chomp($basename = `basename $0`);
-
- $usage = <<EOF;
- USAGE
- $basename database userid passwd pattern [ pattern... ]
-
- DESCRIPTION
- Prints isql scripts that would insert records into the
- tables whose names match any of the patterns in command line. In
- other words, this program reverse engineers the data in a given
- table(s). Roughly, it `select * from <table>', analyses the data
- and table structure, then prints out a bunch of
- insert <table> values ( ... )
- statements that would re-populate the table. It's an alternative
- to `bcp'. `bcp' has its limitations (e.g. one often needs to turn on
- "select into/bulk copy" option in the database before running bcp.)
-
- Table names are matched to <pattern> with Transact-SQL's LIKE clause.
- When more than one pattern is specified on command line, the LIKE
- clauses are OR'ed. In any case, the LIKE clause(s) is logged to
- the beginning of the output as a comment, so that you'll see how this
- program interprets the command line.
-
- The SQL script is printed to stdout. Since it only prints out the SQL
- but doesn't submit it to the SQL server, this procedure is safe to run.
- It doesn't modify database in any way.
-
- EXAMPLES
- To print this usage page:
- % $basename
- To print SQL that populates the table master..sysobjects and systypes:
- % $basename master userid passwd "sysobjects" "systypes"
- To print SQL that populates all system tables in master db:
- % $basename master userid passwd "sys%"
-
- BUGS
- Embedded line breaks in strings are allowed in Sybase's isql, but not
- allowed in SQLAnywhere's isql. So this script converts embedded line
- breaks (both DOS styled and UNIX styled) to blank characters.
-
- EOF
-
- $batchsize = 10; # The number of INSERTs before a `go' is issued.
- # This is to make the output compact.
-
- # .................... No change needed below this line ........................
-
- use Sybase::DBlib;
-
- die $usage unless $#ARGV >= 3;
- ($db, $user, $passwd, @pattern) = @ARGV;
-
- $likeclause = &sql_pattern_to_like_clause("name", @pattern);
-
- print <<EOF;
- -- This script is created by $0.
- -- It would generate INSERT statements for tables whose names match the
- -- following pattern:
- /* $likeclause
- */
-
- set nocount on
- go
- EOF
-
- $dbh = new Sybase::DBlib $user, $passwd;
- $dbh->{dbNullIsUndef} = 1;
- $dbh->dbuse($db);
-
- # Get the list of tables.
- $tablelist = $dbh->sql("select name
- from sysobjects
- where type in (\'S\',\'U\')
- and $likeclause
- order by name
- ");
-
- foreach $tableref (@$tablelist) {
- $table = @$tableref[0];
- print "\n\n/*.............. $table ...............*/\n";
- print "-- ", `date`, "\n";
- print "declare \@d datetime\n";
- print "select \@d = getdate()\n";
- print "print ' %1! $table', \@d\ngo\n\n";
- print "truncate table $table -- Lookout !!!!!!\ngo\n\n";
-
- $dbh->dbcmd("select * from $table");
- $dbh->dbsqlexec;
- $dbh->dbresults;
-
- while (@row = $dbh->dbnextrow()) {
- print "insert $table values(";
-
- for ($i=0; $i <= $#row; $i++) { # build the INSERT statement
- # Analyse datatype to decide if this column needs to be quoted.
- $coltype = $dbh->dbcoltype($i+1);
-
- if (!defined($row[$i])) {
- print "NULL"; # Never quote NULL regardless of datatype
- }
- elsif ($coltype==35 or $coltype==39 or $coltype==47 or
- $coltype==58 or $coltype==61 or $coltype==111 ){
- # See systypes.type/name for explanation of $coltype.
- $row[$i] =~ s/\r|\n/ /g; # Handles both DOS and UNIX line breaks
- $row[$i] =~ s/\'/\'\'/g; # Stuff double quotes
- print '\'' . $row[$i] . '\'';
- } else {
- print $row[$i];
- }
- print ", " unless $i == $#row;
- }
-
- print ")\n"; # wrap up the INSERT statement.
- # print `go' at every $batchsize interval.
- print "go\n" unless $dbh->DBCURROW % $batchsize;
- }
- print "\ngo\n\n"; # print a `go' after the entire table is done.
- print "-- ### End for $table: rowcount = ", $dbh->DBCURROW, "\n";
- }
-
- # ................................. sub ........................................
- sub sql_pattern_to_like_clause {
- local($field_name, @pattern) = @_;
- $like_clause = "\t( 1 = 0 ";
- foreach (@pattern) {
- $like_clause .= "\n or $field_name like '" . $_ . "' ";
- }
- $like_clause .= "\n\t) \n";
- }
- #!/bin/sh
- #-*-sh-*-
- # Code for question 9.3: Generating dump/load database command.
- #
- # This script calls the function gen_dumpload_command to generate
- # either a dump or a load command.
- #
- # This function works for both System 10 and Sybase 4.x
- # installations. You simply need to change your method of thinking.
- # In Sybase 4.x, we only had a single stripe. In System 10, most
- # of the time we define a single stripe but in our bigger databases
- # we define more stripes.
- #
- # Therefore, everything is a stripe. Whether we use one stripe or
- # many... cool? Right on!
- #
- #
- # The function gen_dumpload_command assumes that all dump devices
- # adhere to the following naming convention:
- #
- # stripe_NN_database
- #
- # NOTE: If your shop is different search for "stripe" and replace
- # with your shop's value.
- #
- #
-
-
- # gen_dumpload_command():
- #
- # purpose: to generate a dump/load to/from command based on
- # what is defined in sysdevices. The environment
- # variable D_DEV is set.
- #
- # return: zero on success, non-zero on failure.
- #
- # sets var: D_DEV is set with the actual dump/load command;
- # stripe devices are also handled.
- #
- # calls: *none*
- #
- # parms: 1 = DSQUERY
- # 2 = PASSWD
- # 3 = DB
- # 4 = CMD -> "dump" or "load"
- #
-
-
- gen_dumpload_command()
- {
- LOCAL_DSQUERY=$1
- LOCAL_PASSWD=$2
- DB_TO_AFFECT=$3
- CMD=$4 # dump/load
-
- if [ "$CMD" = "dump" ] ; then
- VIA="to"
- else
- VIA="from"
- fi
-
- # Check for a dump device
-
- echo "Checking for standard $CMD device"
- # D_DEV=`echo "$LOCAL_PASSWD
- $SYBIN/isql -U sa -S $LOCAL_DSQUERY -w1000 | sed -n -e '/stripe/p' | \
- select name from sysdevices where name like \"stripe%_$DB_TO_AFFECT\"
- go"
- EOSQL
- gawk '{ if (NR == 1) print "'$CMD' database '$DB_TO_AFFECT' '$VIA'", $0
- else print "stripe on", $0
- }'`
-
- if [ -z "$D_DEV" ] ; then # nothing defined... :(
- return 1
- fi
-
- return 0
- }
-
- SYBIN=$SYBASE/bin
-
- gen_dumpload_command $1 $2 $3 $4
-
- if [ $? -eq 1 ] ; then
- echo "Error..."
- exit 1
- fi
-
- # so what does this generate? :-)
- echo $D_DEV
-
- # ... and it can be used as follows:
-
- echo "$PASSWD
- $D_DEV
- go" | isql ...
-
- exit 0
- #!/usr/bin/perl
-
- # $Id: int.pl,v 1.4 1995/11/04 03:16:38 mm Exp mm $
-
- # convert a sun4 interfaces file to a different format (see @modelist)
- # limitations:
- # - does not handle tli/spx entries (yet)
- # - drivers for desktop platform hard coded
- # - no sanity checks (duplicate names, incomplete entries)
- # - ignores extraneous tokens silently (e.g. a 6th field)
- # - don't know whether/how to convert decnet to tli format
- # - ???
-
- require 'getopts.pl';
-
- sub usage
- {
- local(@token) = @_;
-
- if (!($token[0] eq 'short' || $token[0] eq 'long'))
- {
- printf STDERR "Environment variable(s) @token not defined.\n";
- exit (1);
- }
-
- print STDERR <<EOM;
- Usage: $progname -f <sun4 interfaces file>
- -o { $modetext1 }
- [-V] [-v] [-h]
- EOM
-
- if ($token[0] eq 'long')
- {
- print STDERR <<EOM;
- where
- -f <file> input file to process
- -o <mode> specify output mode
- (e.g. $modetext2)
- -V turn on verbose mode
- -v print version string
- -h print this message
- EOM
- }
- else
- {
- print STDERR "For more details run $progname -h\n";
- }
- exit(1);
- } # end of usage
-
-
- # FUNCTION NAME: parse_command_line
- # DESCRIPTION: call getopts and assign command line arguments or
- # default values to global variables
- # FORMAL PARAMETERS: none
- # IMPLICIT INPUTS: command line arguments
- # IMPLICIT OUTPUTS: $inputfile, $mode, $verbose
- # RETURN VALUE: none, exits (in usage) if -h was specified
- # (help option).
- # SIDE EFFECTS: none
- #
- sub parse_command_line {
- &Getopts('f:o:hvV') || &usage('short');
- $inputfile = $opt_f;
- $mode = $opt_o;
- $verbose = $opt_V ? 1 : 0;
-
- print("$progname version is: $version\n"), exit 0 if $opt_v;
- &usage('long') if $opt_h;
- &usage('short') if ! $inputfile || ! $mode;
- &usage('short') if ! grep($mode eq $_, @modelist);
- } # end of parse_command_line
-
- # FUNCTION NAME: process_file
- # DESCRIPTION: parse file, try to convert it line by line.
- # FORMAL PARAMETERS: $file - file to process
- # IMPLICIT INPUTS: none
- # IMPLICIT OUTPUTS: none
- # RETURN VALUE: none
- # SIDE EFFECTS: none
-
- sub process_file {
- local($file) = @_;
- open(INPUT, "<$file") ||
- die "can't open file $file: $!\nExit.";
- local($line) = 0;
- local($type, $prot, $stuff, $host, $port, $tmp);
- print $os2_header if $mode eq 'os2';
- while (<INPUT>)
- {
- $line++;
- # handle empty lines (actually lines with spaces and tabs only)
- #print('\n'), next if /^\s*$/;
- next if /^\s*$/;
- chop;
- # comments, strip leading spaces and tabs
- s/^\s*//, print("$_$lf{$mode}\n"), next if /^\s*#/;
- #s/^\s*//, next if /^\s*#/;
-
- # server names
- if (/^\w+/)
- {
- if ($mode eq 'sol' || $mode eq 'ncr'
- || $mode eq 'vms' || $mode eq 'nw386')
- {
- print "$_$lf{$mode}\n";
- next;
- }
- elsif ($mode eq 'os2')
- {
- $server = $_;
- next;
- }
- else {
- print "[$_]$lf{$mode}\n" if !(/SPX$/);
- next;
- }
- }
-
- if (/^\tmaster|^\tquery|\tconsole/)
- {
- # descriptions
- # parse first whitespace delimited word and
- # following space(s)
- # quietly ignore any extraerraneous characters
- # I actually tried to catch them, but - believe
- # it or not - perl would chop off the last digit of
- # $port. vvvv
- # /^\t(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\d+)(.+)$/;
- if (!(($type, $prot, $stuff, $host, $port) =
- /^\t(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)/))
- {
- print STDERR "line $line: unknown format: $_";
- next;
- }
- #print ("line $line: more than 5 tokens >$etc<, \n"),
- # next if $etc;
- if (!($type eq 'master' || $type eq 'query'
- || $type eq 'console'))
- {
- # unknown type
- print STDERR "line $line: unknown type $type\n";
- next;
- }
- if ($prot eq 'tli')
- {
- #print STDERR "line $line: can't handle tli",
- # " entries (yet)\n";
- # adjust to tli format
- ($layer, $prot, $device, $entry) =
- ($prot, $stuff, $host, $port);
- print "\t$type tli $prot $device ",
- "$entry$lf{$mode}\n" if $mode ne 'win3';
- next;
- }
- if (!($prot eq 'tcp' || $prot eq 'decnet'))
- {
- # unknown protocol
- print STDERR
- "line $line: unknown protocol $prot\n";
- next;
- }
- if ($mode eq 'sol' || $mode eq 'ncr' || $mode eq 'nw386')
- {
- $ip = &get_ip_address($host, 'hex');
- $hexport = sprintf("%4.4x", $port);
- print "\t$type tli $prot $device{$prot} \\x",
- "$prefix{$mode}$hexport$ip$nulls{$mode}\n";
- next;
- }
- if ($mode eq 'vms')
- {
- $ip = &get_ip_address($host, 'dot');
- print "\t$type $prot $stuff $ip $port\n";
- next;
- }
- if ($mode eq 'nt386')
- {
- $type =~ tr/a-z/A-Z/;
- print "\t$type=$sock{$mode},$host,",
- "$port$lf{$mode}\n";
- next;
- }
- if ($mode eq 'dos' || $mode eq 'win3')
- {
- next if $type ne 'query';
- print "\t${mode}_$type=$sock{$mode},",
- "$host,$port$lf{$mode}\n";
- next;
- }
- if ($mode eq 'ntdoswin3')
- {
- ($tmp = $type) =~ tr/a-z/A-Z/;
- # watch out for this local($mode) !!
- # its scope is this BLOCK only and
- # (within this block) overrides the
- # other $mode!!! But we can still access
- # the array %sock.
- local($mode) = 'nt386';
- print "\t$tmp=$sock{$mode},$host,$port",
- "$lf{$mode}\n";
- next if $type ne 'query';
- $mode = 'dos';
- print "\t${mode}_$type=$sock{$mode},",
- "$host,$port$lf{$mode}\n";
- $mode = 'win3';
- print "\t${mode}_$type=$sock{$mode},",
- "$host,$port$lf{$mode}\n";
- next;
- }
- if ($mode eq 'os2')
- {
- print " \'$server\' \'$type\' \'$sock{'os2'}",",$host,$port\'\n";
- next;
- }
- }
- printf STDERR "line $line is ->%s<-\n", chop($_);
- }
- close(INPUT);
- print $os2_tail if $mode eq 'os2';
-
- } # end of process_file
-
- # FUNCTION NAME: print_array
- # DESCRIPTION: print the array
- # FORMAL PARAMETERS: *array - array to be printed, passed by reference
- # IMPLICIT INPUTS: none
- # IMPLICIT OUTPUTS: none
- # RETURN VALUE: none
- # SIDE EFFECTS: none
- #
- sub print_array {
- local(*array) = @_;
- foreach (sort keys %array)
- {
- printf STDERR "%-16s %s\n", $_, $array{$_};
- }
-
- } # end of print_array
-
- # FUNCTION NAME: get_ip_address
- # DESCRIPTION: get the ip address of a host specified by name, return
- # it as a string in the requested format, e.g.
- # requested format == 'dot' --> return 130.214.140.2
- # requested format == 'hex' --> return 82d68c02
- # In order to avoid repeated calls of gethostbyname with
- # the same host, store (formatted) results of gethostbyname
- # in array %map.
- # FORMAL PARAMETERS: name of host, requested return type: hex or dot format
- # IMPLICIT INPUTS: %map
- # IMPLICIT OUTPUTS: none
- # RETURN VALUE: ip address
- # SIDE EFFECTS: maintains %map, key is host name, value is ip address.
- #
- sub get_ip_address {
- local($host, $mode) = @_;
- if (!$map{$host})
- {
- #print "calling gethostbyname for $host";
- ($name, $aliases, $addrtype, $length, @addrs) =
- gethostbyname($host);
- $map{$host} = join('.', unpack('C4', $addrs[0]));
- if ($mode eq 'hex')
- {
- $map{$host} = sprintf("%2.2x%2.2x%2.2x%2.2x",
- split(/\./, $map{$host}));
- }
- #print " - $map{$host}\n";
- }
- return $map{$host};
- } # end of get_ip_address
-
-
- $version = "\$Id: int.pl,v 1.4 1995/11/04 03:16:38 mm Exp mm \$";
- $| = 1;
- ($progname = $0) =~ s#.*/##g;
- @modelist = ('sol', 'ncr', 'vms', 'nw386', 'os2',
- 'nt386', 'win3', 'dos', 'ntdoswin3');
- $modetext1 = join('|', @modelist);
- $modetext2 = join(', ', @modelist);
-
- # tli on solaris needs more zeroes
- $nulls{'sol'} = "0000000000000000";
- $nulls{'nw386'} = "0000000000000000";
- $nulls{'ncr'} = "";
- $nulls{'nt386'} = "";
-
- # prefix for tli entries
- $prefix{'sol'} = "0002";
- $prefix{'nw386'} = "0200";
- $prefix{'ncr'} = "0002";
- $prefix{'nt386'} = "0200";
-
- # protocol devices
- $device{'tcp'} = "/dev/tcp";
- $device{'spx'} = "/dev/nspx";
- $device{'decnet'} = "/dev/tcp";
-
- # socket driver names
- $sock{'nt386'}="NLWNSCK";
- $sock{'dos'}="NLFTPTCP";
- $sock{'win3'}="WNLWNSCK";
- $sock{'os2'}="nlibmtcp";
-
- # linefeed's (^M) for the MS world
- $lf{'nt386'}="
- ";
- $lf{'dos'}="
- ";
- $lf{'win3'}="
- ";
- $lf{'ntdoswin3'}="
- ";
- $lf{'os2'}="";
- $lf{'vms'}="";
- $lf{'sol'}="";
- $lf{'ncr'}="";
- $lf{'nw386'}="";
-
- $os2_header = sprintf("STRINGTABLE\nBEGIN\n%s", " \'\'\n" x 10);
- $os2_tail = "END\n";
-
- &parse_command_line;
- &process_file($inputfile);
- &print_array(*map) if $verbose;
- #!/usr/bin/perl -w
-
- use Getopt::Std;
- use strict;
- use English;
-
- my($fullRow, @processStats, $owner, $pid, $parentPid);
- my($started, $engineNum, %engine);
- my($cpuTime, $servType, $param, $servParam, @dirComps);
- my(@engineParts, %stypes, @procParts);
- my($serverName, %server, $srvType, $engine);
- my($cmd);
-
- # (Empirically) I have found with large numbers of engines, that not
- # all of the child parent relationships are as you imagine, ie engine
- # 0 does not start off all other engines. "-l" indents to show this
- # heirarchy.
-
- getopts('l');
-
- # Script to show, in a nice fashion, all of the Sybase servers on a
- # system.
-
- $cmd = "ps -ef -o user,pid,ppid,start,comm";
-
- SWITCH:
- for ($OSNAME) {
-
- /AIX|OSF1/ && do {
- $cmd = "ps auwwx";
- last SWITCH;
- };
-
- /freebsd/ && do {
- $cmd = "ps ps awwxo user,pid,ppid,start,command";
- last SWITCH;
- };
-
- /linux/ && do {
- $cmd = "ps -awxo user,pid,ppid,stime,command";
- last SWITCH;
- };
-
- /solaris/ && do {
- $cmd = "ps -ef -o user,pid,ppid,stime,args";
- last SWITCH;
- };
- }
-
-
- open(PSCMD, "$cmd |") or die("Cannot fork: $!");
-
- while (<PSCMD>) {
- next if !/dataserver|backupserver|repserver|rsmsrvr|monserver/;
-
- # Remove any white space after the -[sS] command.
-
- s/(-[sS])[\s\s*]/$1/;
-
- # Remove leading space.
-
- s/^ *//;
-
- $fullRow = $_;
- @processStats = split(/\s+/);
-
- $owner = shift(@processStats);
- $pid = shift(@processStats);
- $parentPid = shift(@processStats);
- $started = shift(@processStats);
-
- # $cpuTime = shift(@processStats);
-
- $cpuTime = 999;
-
- # Is it a parent or a child?
-
- if ($fullRow =~ /-ONLINE:/) {
- # Child!
- @procParts = split(/[:]/, $processStats[1]);
- @engineParts = split(/[,]/, $procParts[1]);
- $engineNum = $engineParts[0];
- push(@{ $engine{$parentPid} }, [ $pid, $engineNum, $cpuTime ]);
- } else {
-
- $servParam = shift(@processStats);
- @dirComps = split(/\//, $servParam);
- $servType = pop(@dirComps);
-
- PROCSTAT:
- foreach $param (@processStats) {
- if ($param =~ /^-[sS]/) {
- $serverName = substr($param, 2);
- last PROCSTAT;
- }
- }
- $server{$pid} = [ $serverName, $owner, $started ];
- push(@{ $stypes{$servType} }, $pid);
- push(@{ $engine{$pid} }, [ $pid, 0, $cpuTime ]);
- }
- }
-
- close(PSCMD);
-
- foreach $srvType (keys(%stypes)) {
- print "\n$srvType\'s\n";
- print "-" x (length($srvType) + 2);
-
-
- foreach $pid (@{ $stypes{$srvType} }) {
- print "\n $server{$pid}[0] Owner: $server{$pid}[1], Started: $server{$pid}[2]";
-
- printEngines($pid, 0);
- }
- print "\n";
- }
-
- print "\n";
- $Getopt::Std::opt_l = 0;
-
- sub printEngines {
-
- my($pid) = shift;
- my($level) = shift;
-
- if (defined($engine{$pid})) {
- foreach $engine (@{ $engine{$pid} }) {
- print "\n ";
-
- print " " x $level if defined($Getopt::Std::opt_l);
-
- printf "Engine: %2.2s (PID: %s)", @$engine[1], @$engine[0];
-
- if (@$engine[0] ne $pid) {
- printEngines(@$engine[0], $level + 1);
- }
- }
- }
- }
-
-
- use sybsystemprocs
- go
-
- CREATE PROCEDURE sp__create_crosstab
- ,@code_table varchar(30) -- table containing code lookup rows
- ,@code_key_col varchar(30) -- name of code/lookup ID column
- ,@code_desc_col varchar(30) -- name of code/lookup descriptive text column
- ,@value_table varchar(30) -- name of table containing detail rows
- ,@value_col varchar(30) -- name of value column in detail table
- ,@value_group_by varchar(30) -- value table column to group by.
- ,@value_aggregate varchar(5) -- operator to apply to value being aggregated
-
- AS
- /*
- Copyright (c) 1997, Clayton Groom. All rights reserved.
- Procedure to generate a cross tab query script
- Reqires:
- 1. A lookup table with a code/id column and/or descriptive text column
- 2. A data table with a foreign key from the lookup table & a data value to aggregate
- 3. column(s) name from data table to group by
- 4. Name of an aggregate function to perform on the data value column.
- */
-
- set nocount on
-
- if sign(charindex(upper(@value_aggregate), 'MAX MIN AVG SUM COUNT')) = 0
- BEGIN
- print "@value_aggregate value is not a valid aggregate function"
- -- return -1
- END
-
- declare @value_col_type varchar(12) -- find out data type for aggregated column.
- ,@value_col_len int -- get length of the value column
- ,@str_eval_char varchar(255)
- ,@str_eval_int varchar(255)
- -- constants
- ,@IS_CHAR varchar(100) -- character data types
- ,@IS_NOT_ALLOWED varchar(100) -- data types not allowed
- ,@IS_NUMERIC varchar(255) -- numeric data type names
- ,@NL char(2) -- new line
- ,@QUOTE char(1) -- ascii character 34 '"'
- --test variables
- ,@value_col_is_char tinyint -- 1 = string data type, 0 = numeric or not allowed
- ,@value_col_is_ok tinyint -- 1 = string or numeric type, 0 = type cannot be used.
- ,@value_col_is_num tinyint -- 1 = numeric data type, 0 = string or not allowed
-
- select @IS_CHAR = 'varchar char nchar nvarchar text sysname'
- ,@IS_NOT_ALLOWED= 'binary bit varbinary smalldatetime datetime datetimn image timestamp'
- ,@IS_NUMERIC = 'decimal decimaln float floatn int intn money moneyn numeric numericn real smallint smallmoney tinyint'
- ,@NL = char(13) + char(10)
- ,@QUOTE = '"' -- ascii 34
-
- -- get the base data type & length of the value column. Is it a numeric type or a string type?
- -- need to know this to use string or numeric functions in the generated select statement.
- select @value_col_type = st.name
- ,@value_col_len = sc.length
- from syscolumns sc
- ,systypes st
- where sc.id = object_id(@value_table)
- and sc.name = @value_col
- and sc.type = st.type
- and st.usertype = (select min(usertype)
- from systypes st2
- where st2.type = sc.type)
- --select @value_col_type, @value_col_len
-
- select @value_col_is_char = sign(charindex( @value_col_type, @IS_CHAR))
- ,@value_col_is_ok = 1 - sign(charindex( @value_col_type, @IS_NOT_ALLOWED))
- ,@value_col_is_num = sign(charindex( @value_col_type, @IS_NUMERIC))
-
- IF @value_col_is_ok = 1
- BEGIN
- if @value_col_is_char = 1
- begin
- select @str_eval_char = ''
- end
- else
- if @value_col_is_num = 1
- begin
- select @str_eval_char = ''
- end
- else
- begin
- print " @value_col data type unnown. must be string or numeric"
- -- return -1
- end
- END
- ELSE --ERROR
- BEGIN
- print " @value_col data type not allowed. must be string or numeric"
- -- return -1
- END
-
- -- template. first level expansion query.
- -- result must be executed to generate final output query.
-
- SELECT "select 'select " + @value_group_by + "'"
- IF @value_col_is_char = 1
- BEGIN
- SELECT "select '," + @QUOTE + "' + convert(varchar(40), " + @code_desc_col+ " ) + '" + @QUOTE + @NL
- + " = "
- + @value_aggregate
- + "(isnull( substring("
- + @value_col
- + ", 1, ( "
- + convert(varchar(3), @value_col_len )
- + " * charindex( "
- + @QUOTE
- + "'+"
- + @code_key_col
- + "+'"
- + @QUOTE
- + ", "
- + @code_key_col
- + " ))), "
- + @QUOTE + @QUOTE
- + "))'"
- END
- ELSE IF @value_col_is_num = 1
- BEGIN
- SELECT "select '," + @QUOTE + "' + convert(varchar(40), " + @code_desc_col+ " ) + '" + @QUOTE + @NL
- + " = "
- + @value_aggregate
- + "("
- + @value_col
- + " * charindex( "
- + @QUOTE
- + "'+"
- + @code_key_col
- + "+'"
- + @QUOTE
- + ", "
- + @code_key_col
- + "))'"
- END
- SELECT "from " + @code_table + @NL
- + "select 'from " + @value_table + "'" + @NL
- + "select 'group by " + @value_group_by + "'"
-
- -- end
- go
- use sybsystemprocs
- go
-
- if object_id('sp__indexreport') is not null
- drop procedure sp__indexreport
- go
-
- /*
- ** A system sproc to report on user indexes.
- **
- ** Written by Anthony Mandic - July 2000.
- */
- create procedure sp__indexreport
- as
-
- if @@trancount = 0
- set chained off
-
- set transaction isolation level 1
-
- set nocount on
-
- /*
- ** Check for user tables first.
- */
- if (select count(*) from sysobjects where type = "U") = 0
- begin
- print "No user tables found in current database"
- return 1
- end
-
- /*
- ** Check for tables without any indexes.
- */
- select name
- into #tablelist
- from sysindexes
- group by id
- having count(id) = 1
- and indid = 0
- and id > 99
- and name not like "#tablelist%" /* Avoid finding it if run in tempdb */
-
- if @@rowcount > 0
- select "Tables without indexes" = name
- from #tablelist
- order by name
-
- drop table #tablelist
-
- /*
- ** Select all user indexes where there are multiple indexes on a table.
- */
- select tid = id,
- tname = object_name(id),
- iname = name,
- iid = indid,
- indexcolumns = convert(varchar(254), "")
- into #indexlist
- from sysindexes
- where id > 99
- and indid between 1 and 254
- group by id
- having count(id) > 1
- and indid between 1 and 254
-
- if @@rowcount = 0
- begin
- print "No duplicate indexes found in current database"
- return 1
- end
-
- declare @count int,
- @tid int,
- @size int,
- @icolumns varchar(254)
-
- select @count = 1
-
- while @count < 17 /* 16 appears to be the max number of indexes */
- begin
- update #indexlist
- set indexcolumns =
- case
- when @count > 1 then indexcolumns + ', '
- end
- + index_col(tname, iid, @count)
- where index_col(tname, iid, @count) is not null
-
- if @@rowcount = 0
- break
-
- select @count = @count + 1
- end
-
- create table #finallist
- (
- table_name varchar(30),
- index_name varchar(30),
- tid int,
- index_columns varchar(254)
- )
-
- insert #finallist
- select b.tname,
- b.iname,
- b.tid,
- b.indexcolumns
- from #indexlist a,
- #indexlist b
- where a.tid = b.tid
- and a.indexcolumns like b.indexcolumns + '%'
- group by a.tid,
- a.iname
- having count(*) > 1
- and a.tid = b.tid
- and a.indexcolumns like b.indexcolumns + '%'
-
- if (select count(*) from #finallist) = 0
- begin
- print "No duplicate indexes found in current database"
- return 1
- end
-
- select @size = low / 1024
- from master..spt_values
- where number = 1
- and type = "E"
-
- print "Duplicate leading index columns"
- print "-------------------------------"
- print ""
-
- /*
- ** The distinct is needed to eliminate duplicated identical indexes on tables.
- ** The order by is to get the resultant distinct list sorted.
- */
- select distinct
- "table name" = table_name,
- "index name" = index_name,
- "size" = str(
- (data_pgs(id, doampg) + data_pgs(id, ioampg)) * @size)
- + " KB",
- "index columns" = index_columns
- from #finallist,
- sysindexes
- where id = tid
- and name = index_name
- order by table_name, index_columns
-
- return 0
- go
-
- exec sp_procxmode 'sp__indexreport', 'anymode'
- go
-
- grant execute on sp__indexreport to public
- go
- set flushmessage on
- go
-
- use sybsystemprocs
- go
-
- if exists (select 1
- from sysobjects
- where sysstat & 7 = 4
- and name = 'sp__optdiag')
- begin
- print "Dropping sp__optdiag"
- drop procedure sp__optdiag
- end
- go
-
- print "Installing sp__optdiag"
- go
-
- create procedure sp__optdiag
- @tabname varchar(62) = null, /* user table name */
- @colname varchar(30) = null, /* column name */
- @option varchar(60) = null /* output format */
- , @proc_version varchar(78) = "sp__optdiag/0.4/0/P/KJS/AnyPlat/AnyOS/G/Fri Jan 5 14:56:32 2001"
- as
- /*************************************************************************************************
- **
- ** Description: Format opdiag info from stored procedure
- **
- ** Options: NULL - default
- **
- ** "V/?/HELP/H" - will print the current version string of this proc
- ** "CR" - will approximate cluster ratio calculations. Note that these are simply
- ** simply approximations since cluster ratio calculations are not published.
- ** (future change, not supported yet)
- **
- ** Future Info: Other options can be added in the future
- ** using the @option parameter.
- **
- ** Dependencies: This proc relies on the object_id built-in
- ** and sp_namecrack
- **
- ** Errors:
- **
- ** Version: This proc is for ASE 11.9.x and beyond
- **
- ** Usage: exec <dbname>..sp__optdiag <tabname>, <colname>, <opt>
- **
- ** History: 10/31/2000 (ksherlock) 0.1
- ** Original
- ** 11/14/2000 (ksherlock) 0.2
- ** Fixed bug to handle binary histograms and handle user defined types
- ** 12/20/2000 (ksherlock) 0.3
- ** Fixed bug with column groups not being retrieved in col_cursor
- ** 01/05/2001 (ksherlock) 0.4
- ** Final version which handles numeric decimals correctly
- **
- *************************************************************************************************/
-
- declare
- @colid int /* Variable to hold colid from syscolumns */
- , @tabid int /* Variable to hold object_id from sysobjects */
- , @tabtype char(2) /* Variable to hold type from sysobjects */
- , @s_dbname varchar(30)
- , @s_tabowner varchar(30)
- , @s_tabname varchar(30)
- , @u_tabname varchar(30)
- , @u_tabowner varchar(30)
- , @colgroup_name varchar(255)
- , @u_dbname varchar(30)
- , @u_dbid int
- , @colidarray varbinary(100)
- , @colidarray_len smallint
- , @indid int
- , @index_cols varchar(254)
- , @index_name varchar(30)
- , @keycnt int
- , @dol_clustered int
- , @clustered int
- , @last_updt varchar(28)
- , @c1stat int
- , @statid smallint
- , @used_count int
- , @rownum int
- , @coltype int
- , @typename varchar(30)
- , @collength varchar(5)
- , @precision varchar(3)
- , @scale varchar(3)
- , @rc_density varchar(24)
- , @tot_density varchar(24)
- , @r_sel varchar(24)
- , @between_sel varchar(24)
- , @freq_cell smallint
- , @steps_act int
- , @steps_req int
- , @step char(9)
- , @weight char(10)
- , @prev_step char(9)
- , @prev_weight char(10)
- , @value_raw varbinary(255)
- , @value_c varchar(255)
- , @leafcnt varchar(32) -- int
- , @pagecnt varchar(32) -- int
- , @emptypgcnt varchar(32) -- int
- , @rowcnt varchar(32)
- , @forwrowcnt varchar(32)
- , @delrowcnt varchar(32)
- , @dpagecrcnt varchar(32)
- , @dpagecr varchar(32)
- , @ipagecrcnt varchar(32)
- , @ipagecr varchar(32)
- , @drowcrcnt varchar(32)
- , @drowcr varchar(32)
- , @oamapgcnt varchar(32) -- int
- , @extent0pgcnt varchar(32)
- , @datarowsize varchar(32)
- , @leafrowsize varchar(32)
- , @indexheight varchar(32) -- int
- , @spare1 varchar(32) -- int
- , @spare2 varchar(32)
- , @ptn_data_pgs int
- , @seq int
-
-
- if @@trancount = 0
- begin
- set chained off
- end
-
- set transaction isolation level 1
- set nocount on
- set flushmessage on
-
- if ( (select lower(@option)) in ("v","version","?","h","help") )
- begin
- print "%1!",@proc_version
- return 0
- end
-
- exec sp_namecrack @tabname, " ", @s_dbname out, @s_tabowner out, @s_tabname out
- select @s_dbname = isnull(@s_dbname,db_name())
-
- declare object_cursor cursor for
- select id,
- db_name(),
- db_id(),
- user_name(uid),
- name
- from sysobjects
- where user_name(uid) like isnull(@s_tabowner,"%")
- and name like isnull(@s_tabname,"%")
- and type = "U" and id > 100
- order by user_name(uid), name
- for read only
-
- declare index_cursor cursor for
- select st.indid
- , si.name
- , abs(sign(si.status2 & 512)) /* DOL clustered index */
- , abs(sign(si.status & 16)) /* clustered bit */
- , si.keycnt
- from systabstats st, sysindexes si
- where st.id = @tabid
- and si.id = @tabid
- and st.id = si.id
- and st.indid = si.indid
- order by st.indid
- for read only
-
- declare col_cursor cursor for
- select sc.colid,
- ss.colidarray,
- datalength(ss.colidarray),
- sc.name,
- ss.statid,
- convert(int,ss.c1),
- convert(varchar,ss.moddate,109),
- ltrim(str(round(convert(double precision,ss.c2),16),24,16)),
- ltrim(str(round(convert(double precision,ss.c3),16),24,16)),
- convert(int,ss.c4),
- convert(int,ss.c5),
- st.name,
- ltrim(str(convert(int,ss.c7),5)),
- ltrim(str(convert(int,ss.c8),3)),
- ltrim(str(convert(int,ss.c9),3)),
- ltrim(str(round(convert(double precision,ss.c10),16),24,16)),
- ltrim(str(round(convert(double precision,ss.c11),16),24,16))
- from syscolumns sc, sysstatistics ss, systypes st
- where sc.id = @tabid
- and sc.name like isnull(@colname,"%")
- and ss.id = sc.id
- and convert(int,ss.c6) *= st.type
- and st.name not in ("timestamp","sysname", "nchar", "nvarchar")
- and st.usertype < 100
- and convert(tinyint,substring(ss.colidarray,1,1)) = sc.colid
- and ss.formatid = 100
- order by sc.id, sc.name, ss.colidarray
- for read only
-
- declare nostats_cursor cursor for
- select sc.name
- from syscolumns sc,
- sysstatistics ss
- where ss.id =* sc.id
- and sc.id = @tabid
- and ss.formatid = 100
- and ss.statid = 0
- and ss.sequence = 1
- and sc.colid *= convert(tinyint,substring(ss.colidarray,1,1))
- and datalength(ss.colidarray) = 1
- group by sc.name
- having count(ss.id) = 0
- order by sc.name
- for read only
-
- create table #cells(seq int,colnum int)
-
- /** DO NOT FOLD, SPINDAL, OR MUTILATE (unless its sysstatistics) **/
- /** OK, bear with me, here we go... **/
-
- declare histogram_cursor cursor for
- select
- /** Here is the step number **/
- str(
- ((c.seq-1)*80 + 1 )*(1-abs(sign(c.colnum-1 ))) + ((c.seq-1)*80 + 2 )*(1-abs(sign(c.colnum-2 ))) +
- ((c.seq-1)*80 + 3 )*(1-abs(sign(c.colnum-3 ))) + ((c.seq-1)*80 + 4 )*(1-abs(sign(c.colnum-4 ))) +
- ((c.seq-1)*80 + 5 )*(1-abs(sign(c.colnum-5 ))) + ((c.seq-1)*80 + 6 )*(1-abs(sign(c.colnum-6 ))) +
- ((c.seq-1)*80 + 7 )*(1-abs(sign(c.colnum-7 ))) + ((c.seq-1)*80 + 8 )*(1-abs(sign(c.colnum-8 ))) +
- ((c.seq-1)*80 + 9 )*(1-abs(sign(c.colnum-9 ))) + ((c.seq-1)*80 + 10)*(1-abs(sign(c.colnum-10))) +
- ((c.seq-1)*80 + 11)*(1-abs(sign(c.colnum-11))) + ((c.seq-1)*80 + 12)*(1-abs(sign(c.colnum-12))) +
- ((c.seq-1)*80 + 13)*(1-abs(sign(c.colnum-13))) + ((c.seq-1)*80 + 14)*(1-abs(sign(c.colnum-14))) +
- ((c.seq-1)*80 + 15)*(1-abs(sign(c.colnum-15))) + ((c.seq-1)*80 + 16)*(1-abs(sign(c.colnum-16))) +
- ((c.seq-1)*80 + 17)*(1-abs(sign(c.colnum-17))) + ((c.seq-1)*80 + 18)*(1-abs(sign(c.colnum-18))) +
- ((c.seq-1)*80 + 19)*(1-abs(sign(c.colnum-19))) + ((c.seq-1)*80 + 20)*(1-abs(sign(c.colnum-20))) +
- ((c.seq-1)*80 + 21)*(1-abs(sign(c.colnum-21))) + ((c.seq-1)*80 + 22)*(1-abs(sign(c.colnum-22))) +
- ((c.seq-1)*80 + 23)*(1-abs(sign(c.colnum-23))) + ((c.seq-1)*80 + 24)*(1-abs(sign(c.colnum-24))) +
- ((c.seq-1)*80 + 25)*(1-abs(sign(c.colnum-25))) + ((c.seq-1)*80 + 26)*(1-abs(sign(c.colnum-26))) +
- ((c.seq-1)*80 + 27)*(1-abs(sign(c.colnum-27))) + ((c.seq-1)*80 + 28)*(1-abs(sign(c.colnum-28))) +
- ((c.seq-1)*80 + 29)*(1-abs(sign(c.colnum-29))) + ((c.seq-1)*80 + 30)*(1-abs(sign(c.colnum-30))) +
- ((c.seq-1)*80 + 31)*(1-abs(sign(c.colnum-31))) + ((c.seq-1)*80 + 32)*(1-abs(sign(c.colnum-32))) +
- ((c.seq-1)*80 + 33)*(1-abs(sign(c.colnum-33))) + ((c.seq-1)*80 + 34)*(1-abs(sign(c.colnum-34))) +
- ((c.seq-1)*80 + 35)*(1-abs(sign(c.colnum-35))) + ((c.seq-1)*80 + 36)*(1-abs(sign(c.colnum-36))) +
- ((c.seq-1)*80 + 37)*(1-abs(sign(c.colnum-37))) + ((c.seq-1)*80 + 38)*(1-abs(sign(c.colnum-38))) +
- ((c.seq-1)*80 + 39)*(1-abs(sign(c.colnum-39))) + ((c.seq-1)*80 + 40)*(1-abs(sign(c.colnum-40))) +
- ((c.seq-1)*80 + 41)*(1-abs(sign(c.colnum-41))) + ((c.seq-1)*80 + 42)*(1-abs(sign(c.colnum-42))) +
- ((c.seq-1)*80 + 43)*(1-abs(sign(c.colnum-43))) + ((c.seq-1)*80 + 44)*(1-abs(sign(c.colnum-44))) +
- ((c.seq-1)*80 + 45)*(1-abs(sign(c.colnum-45))) + ((c.seq-1)*80 + 46)*(1-abs(sign(c.colnum-46))) +
- ((c.seq-1)*80 + 47)*(1-abs(sign(c.colnum-47))) + ((c.seq-1)*80 + 48)*(1-abs(sign(c.colnum-48))) +
- ((c.seq-1)*80 + 49)*(1-abs(sign(c.colnum-49))) + ((c.seq-1)*80 + 50)*(1-abs(sign(c.colnum-50))) +
- ((c.seq-1)*80 + 51)*(1-abs(sign(c.colnum-51))) + ((c.seq-1)*80 + 52)*(1-abs(sign(c.colnum-52))) +
- ((c.seq-1)*80 + 53)*(1-abs(sign(c.colnum-53))) + ((c.seq-1)*80 + 54)*(1-abs(sign(c.colnum-54))) +
- ((c.seq-1)*80 + 55)*(1-abs(sign(c.colnum-55))) + ((c.seq-1)*80 + 56)*(1-abs(sign(c.colnum-56))) +
- ((c.seq-1)*80 + 57)*(1-abs(sign(c.colnum-57))) + ((c.seq-1)*80 + 58)*(1-abs(sign(c.colnum-58))) +
- ((c.seq-1)*80 + 59)*(1-abs(sign(c.colnum-59))) + ((c.seq-1)*80 + 60)*(1-abs(sign(c.colnum-60))) +
- ((c.seq-1)*80 + 61)*(1-abs(sign(c.colnum-61))) + ((c.seq-1)*80 + 62)*(1-abs(sign(c.colnum-62))) +
- ((c.seq-1)*80 + 63)*(1-abs(sign(c.colnum-63))) + ((c.seq-1)*80 + 64)*(1-abs(sign(c.colnum-64))) +
- ((c.seq-1)*80 + 65)*(1-abs(sign(c.colnum-65))) + ((c.seq-1)*80 + 66)*(1-abs(sign(c.colnum-66))) +
- ((c.seq-1)*80 + 67)*(1-abs(sign(c.colnum-67))) + ((c.seq-1)*80 + 68)*(1-abs(sign(c.colnum-68))) +
- ((c.seq-1)*80 + 69)*(1-abs(sign(c.colnum-69))) + ((c.seq-1)*80 + 70)*(1-abs(sign(c.colnum-70))) +
- ((c.seq-1)*80 + 71)*(1-abs(sign(c.colnum-71))) + ((c.seq-1)*80 + 72)*(1-abs(sign(c.colnum-72))) +
- ((c.seq-1)*80 + 73)*(1-abs(sign(c.colnum-73))) + ((c.seq-1)*80 + 74)*(1-abs(sign(c.colnum-74))) +
- ((c.seq-1)*80 + 75)*(1-abs(sign(c.colnum-75))) + ((c.seq-1)*80 + 76)*(1-abs(sign(c.colnum-76))) +
- ((c.seq-1)*80 + 77)*(1-abs(sign(c.colnum-77))) + ((c.seq-1)*80 + 78)*(1-abs(sign(c.colnum-78))) +
- ((c.seq-1)*80 + 79)*(1-abs(sign(c.colnum-79))) + ((c.seq-1)*80 + 80)*(1-abs(sign(c.colnum-80)))
- ,9),
-
- /** And here is the Weight of the cell **/
-
- str(
- isnull(convert(real,s.c0)*(1-abs(sign(c.colnum-1 ))) ,0) + isnull(convert(real,s.c1)*(1-abs(sign(c.colnum-2 ))) ,0) +
- isnull(convert(real,s.c2)*(1-abs(sign(c.colnum-3 ))) ,0) + isnull(convert(real,s.c3)*(1-abs(sign(c.colnum-4 ))) ,0) +
- isnull(convert(real,s.c4)*(1-abs(sign(c.colnum-5 ))) ,0) + isnull(convert(real,s.c5)*(1-abs(sign(c.colnum-6 ))) ,0) +
- isnull(convert(real,s.c6)*(1-abs(sign(c.colnum-7 ))) ,0) + isnull(convert(real,s.c7)*(1-abs(sign(c.colnum-8 ))) ,0) +
- isnull(convert(real,s.c8)*(1-abs(sign(c.colnum-9 ))) ,0) + isnull(convert(real,s.c9)*(1-abs(sign(c.colnum-10))) ,0) +
- isnull(convert(real,s.c10)*(1-abs(sign(c.colnum-11))) ,0) + isnull(convert(real,s.c11)*(1-abs(sign(c.colnum-12))) ,0) +
- isnull(convert(real,s.c12)*(1-abs(sign(c.colnum-13))) ,0) + isnull(convert(real,s.c13)*(1-abs(sign(c.colnum-14))) ,0) +
- isnull(convert(real,s.c14)*(1-abs(sign(c.colnum-15))) ,0) + isnull(convert(real,s.c15)*(1-abs(sign(c.colnum-16))) ,0) +
- isnull(convert(real,s.c16)*(1-abs(sign(c.colnum-17))) ,0) + isnull(convert(real,s.c17)*(1-abs(sign(c.colnum-18))) ,0) +
- isnull(convert(real,s.c18)*(1-abs(sign(c.colnum-19))) ,0) + isnull(convert(real,s.c19)*(1-abs(sign(c.colnum-20))) ,0) +
- isnull(convert(real,s.c20)*(1-abs(sign(c.colnum-21))) ,0) + isnull(convert(real,s.c21)*(1-abs(sign(c.colnum-22))) ,0) +
- isnull(convert(real,s.c22)*(1-abs(sign(c.colnum-23))) ,0) + isnull(convert(real,s.c23)*(1-abs(sign(c.colnum-24))) ,0) +
- isnull(convert(real,s.c24)*(1-abs(sign(c.colnum-25))) ,0) + isnull(convert(real,s.c25)*(1-abs(sign(c.colnum-26))) ,0) +
- isnull(convert(real,s.c26)*(1-abs(sign(c.colnum-27))) ,0) + isnull(convert(real,s.c27)*(1-abs(sign(c.colnum-28))) ,0) +
- isnull(convert(real,s.c28)*(1-abs(sign(c.colnum-29))) ,0) + isnull(convert(real,s.c29)*(1-abs(sign(c.colnum-30))) ,0) +
- isnull(convert(real,s.c30)*(1-abs(sign(c.colnum-31))) ,0) + isnull(convert(real,s.c31)*(1-abs(sign(c.colnum-32))) ,0) +
- isnull(convert(real,s.c32)*(1-abs(sign(c.colnum-33))) ,0) + isnull(convert(real,s.c33)*(1-abs(sign(c.colnum-34))) ,0) +
- isnull(convert(real,s.c34)*(1-abs(sign(c.colnum-35))) ,0) + isnull(convert(real,s.c35)*(1-abs(sign(c.colnum-36))) ,0) +
- isnull(convert(real,s.c36)*(1-abs(sign(c.colnum-37))) ,0) + isnull(convert(real,s.c37)*(1-abs(sign(c.colnum-38))) ,0) +
- isnull(convert(real,s.c38)*(1-abs(sign(c.colnum-39))) ,0) + isnull(convert(real,s.c39)*(1-abs(sign(c.colnum-40))) ,0) +
- isnull(convert(real,s.c40)*(1-abs(sign(c.colnum-41))) ,0) + isnull(convert(real,s.c41)*(1-abs(sign(c.colnum-42))) ,0) +
- isnull(convert(real,s.c42)*(1-abs(sign(c.colnum-43))) ,0) + isnull(convert(real,s.c43)*(1-abs(sign(c.colnum-44))) ,0) +
- isnull(convert(real,s.c44)*(1-abs(sign(c.colnum-45))) ,0) + isnull(convert(real,s.c45)*(1-abs(sign(c.colnum-46))) ,0) +
- isnull(convert(real,s.c46)*(1-abs(sign(c.colnum-47))) ,0) + isnull(convert(real,s.c47)*(1-abs(sign(c.colnum-48))) ,0) +
- isnull(convert(real,s.c48)*(1-abs(sign(c.colnum-49))) ,0) + isnull(convert(real,s.c49)*(1-abs(sign(c.colnum-50))) ,0) +
- isnull(convert(real,s.c50)*(1-abs(sign(c.colnum-51))) ,0) + isnull(convert(real,s.c51)*(1-abs(sign(c.colnum-52))) ,0) +
- isnull(convert(real,s.c52)*(1-abs(sign(c.colnum-53))) ,0) + isnull(convert(real,s.c53)*(1-abs(sign(c.colnum-54))) ,0) +
- isnull(convert(real,s.c54)*(1-abs(sign(c.colnum-55))) ,0) + isnull(convert(real,s.c55)*(1-abs(sign(c.colnum-56))) ,0) +
- isnull(convert(real,s.c56)*(1-abs(sign(c.colnum-57))) ,0) + isnull(convert(real,s.c57)*(1-abs(sign(c.colnum-58))) ,0) +
- isnull(convert(real,s.c58)*(1-abs(sign(c.colnum-59))) ,0) + isnull(convert(real,s.c59)*(1-abs(sign(c.colnum-60))) ,0) +
- isnull(convert(real,s.c60)*(1-abs(sign(c.colnum-61))) ,0) + isnull(convert(real,s.c61)*(1-abs(sign(c.colnum-62))) ,0) +
- isnull(convert(real,s.c62)*(1-abs(sign(c.colnum-63))) ,0) + isnull(convert(real,s.c63)*(1-abs(sign(c.colnum-64))) ,0) +
- isnull(convert(real,s.c64)*(1-abs(sign(c.colnum-65))) ,0) + isnull(convert(real,s.c65)*(1-abs(sign(c.colnum-66))) ,0) +
- isnull(convert(real,s.c66)*(1-abs(sign(c.colnum-67))) ,0) + isnull(convert(real,s.c67)*(1-abs(sign(c.colnum-68))) ,0) +
- isnull(convert(real,s.c68)*(1-abs(sign(c.colnum-69))) ,0) + isnull(convert(real,s.c69)*(1-abs(sign(c.colnum-70))) ,0) +
- isnull(convert(real,s.c70)*(1-abs(sign(c.colnum-71))) ,0) + isnull(convert(real,s.c71)*(1-abs(sign(c.colnum-72))) ,0) +
- isnull(convert(real,s.c72)*(1-abs(sign(c.colnum-73))) ,0) + isnull(convert(real,s.c73)*(1-abs(sign(c.colnum-74))) ,0) +
- isnull(convert(real,s.c74)*(1-abs(sign(c.colnum-75))) ,0) + isnull(convert(real,s.c75)*(1-abs(sign(c.colnum-76))) ,0) +
- isnull(convert(real,s.c76)*(1-abs(sign(c.colnum-77))) ,0) + isnull(convert(real,s.c77)*(1-abs(sign(c.colnum-78))) ,0) +
- isnull(convert(real,s.c78)*(1-abs(sign(c.colnum-79))) ,0) + isnull(convert(real,s.c79)*(1-abs(sign(c.colnum-80))) ,0)
- ,10,8),
-
- /** And finally, here is the Value of the cell **/
-
- substring(convert(varbinary(255),v.c0),(1-abs(sign(c.colnum-1 ))) ,255) + substring(convert(varbinary(255),v.c1),(1-abs(sign(c.colnum-2 ))) ,255) +
- substring(convert(varbinary(255),v.c2),(1-abs(sign(c.colnum-3 ))) ,255) + substring(convert(varbinary(255),v.c3),(1-abs(sign(c.colnum-4 ))) ,255) +
- substring(convert(varbinary(255),v.c4),(1-abs(sign(c.colnum-5 ))) ,255) + substring(convert(varbinary(255),v.c5),(1-abs(sign(c.colnum-6 ))) ,255) +
- substring(convert(varbinary(255),v.c6),(1-abs(sign(c.colnum-7 ))) ,255) + substring(convert(varbinary(255),v.c7),(1-abs(sign(c.colnum-8 ))) ,255) +
- substring(convert(varbinary(255),v.c8),(1-abs(sign(c.colnum-9 ))) ,255) + substring(convert(varbinary(255),v.c9),(1-abs(sign(c.colnum-10))) ,255) +
- substring(convert(varbinary(255),v.c10),(1-abs(sign(c.colnum-11))) ,255) + substring(convert(varbinary(255),v.c11),(1-abs(sign(c.colnum-12))) ,255) +
- substring(convert(varbinary(255),v.c12),(1-abs(sign(c.colnum-13))) ,255) + substring(convert(varbinary(255),v.c13),(1-abs(sign(c.colnum-14))) ,255) +
- substring(convert(varbinary(255),v.c14),(1-abs(sign(c.colnum-15))) ,255) + substring(convert(varbinary(255),v.c15),(1-abs(sign(c.colnum-16))) ,255) +
- substring(convert(varbinary(255),v.c16),(1-abs(sign(c.colnum-17))) ,255) + substring(convert(varbinary(255),v.c17),(1-abs(sign(c.colnum-18))) ,255) +
- substring(convert(varbinary(255),v.c18),(1-abs(sign(c.colnum-19))) ,255) + substring(convert(varbinary(255),v.c19),(1-abs(sign(c.colnum-20))) ,255) +
- substring(convert(varbinary(255),v.c20),(1-abs(sign(c.colnum-21))) ,255) + substring(convert(varbinary(255),v.c21),(1-abs(sign(c.colnum-22))) ,255) +
- substring(convert(varbinary(255),v.c22),(1-abs(sign(c.colnum-23))) ,255) + substring(convert(varbinary(255),v.c23),(1-abs(sign(c.colnum-24))) ,255) +
- substring(convert(varbinary(255),v.c24),(1-abs(sign(c.colnum-25))) ,255) + substring(convert(varbinary(255),v.c25),(1-abs(sign(c.colnum-26))) ,255) +
- substring(convert(varbinary(255),v.c26),(1-abs(sign(c.colnum-27))) ,255) + substring(convert(varbinary(255),v.c27),(1-abs(sign(c.colnum-28))) ,255) +
- substring(convert(varbinary(255),v.c28),(1-abs(sign(c.colnum-29))) ,255) + substring(convert(varbinary(255),v.c29),(1-abs(sign(c.colnum-30))) ,255) +
- substring(convert(varbinary(255),v.c30),(1-abs(sign(c.colnum-31))) ,255) + substring(convert(varbinary(255),v.c31),(1-abs(sign(c.colnum-32))) ,255) +
- substring(convert(varbinary(255),v.c32),(1-abs(sign(c.colnum-33))) ,255) + substring(convert(varbinary(255),v.c33),(1-abs(sign(c.colnum-34))) ,255) +
- substring(convert(varbinary(255),v.c34),(1-abs(sign(c.colnum-35))) ,255) + substring(convert(varbinary(255),v.c35),(1-abs(sign(c.colnum-36))) ,255) +
- substring(convert(varbinary(255),v.c36),(1-abs(sign(c.colnum-37))) ,255) + substring(convert(varbinary(255),v.c37),(1-abs(sign(c.colnum-38))) ,255) +
- substring(convert(varbinary(255),v.c38),(1-abs(sign(c.colnum-39))) ,255) + substring(convert(varbinary(255),v.c39),(1-abs(sign(c.colnum-40))) ,255) +
- substring(convert(varbinary(255),v.c40),(1-abs(sign(c.colnum-41))) ,255) + substring(convert(varbinary(255),v.c41),(1-abs(sign(c.colnum-42))) ,255) +
- substring(convert(varbinary(255),v.c42),(1-abs(sign(c.colnum-43))) ,255) + substring(convert(varbinary(255),v.c43),(1-abs(sign(c.colnum-44))) ,255) +
- substring(convert(varbinary(255),v.c44),(1-abs(sign(c.colnum-45))) ,255) + substring(convert(varbinary(255),v.c45),(1-abs(sign(c.colnum-46))) ,255) +
- substring(convert(varbinary(255),v.c46),(1-abs(sign(c.colnum-47))) ,255) + substring(convert(varbinary(255),v.c47),(1-abs(sign(c.colnum-48))) ,255) +
- substring(convert(varbinary(255),v.c48),(1-abs(sign(c.colnum-49))) ,255) + substring(convert(varbinary(255),v.c49),(1-abs(sign(c.colnum-50))) ,255) +
- substring(convert(varbinary(255),v.c50),(1-abs(sign(c.colnum-51))) ,255) + substring(convert(varbinary(255),v.c51),(1-abs(sign(c.colnum-52))) ,255) +
- substring(convert(varbinary(255),v.c52),(1-abs(sign(c.colnum-53))) ,255) + substring(convert(varbinary(255),v.c53),(1-abs(sign(c.colnum-54))) ,255) +
- substring(convert(varbinary(255),v.c54),(1-abs(sign(c.colnum-55))) ,255) + substring(convert(varbinary(255),v.c55),(1-abs(sign(c.colnum-56))) ,255) +
- substring(convert(varbinary(255),v.c56),(1-abs(sign(c.colnum-57))) ,255) + substring(convert(varbinary(255),v.c57),(1-abs(sign(c.colnum-58))) ,255) +
- substring(convert(varbinary(255),v.c58),(1-abs(sign(c.colnum-59))) ,255) + substring(convert(varbinary(255),v.c59),(1-abs(sign(c.colnum-60))) ,255) +
- substring(convert(varbinary(255),v.c60),(1-abs(sign(c.colnum-61))) ,255) + substring(convert(varbinary(255),v.c61),(1-abs(sign(c.colnum-62))) ,255) +
- substring(convert(varbinary(255),v.c62),(1-abs(sign(c.colnum-63))) ,255) + substring(convert(varbinary(255),v.c63),(1-abs(sign(c.colnum-64))) ,255) +
- substring(convert(varbinary(255),v.c64),(1-abs(sign(c.colnum-65))) ,255) + substring(convert(varbinary(255),v.c65),(1-abs(sign(c.colnum-66))) ,255) +
- substring(convert(varbinary(255),v.c66),(1-abs(sign(c.colnum-67))) ,255) + substring(convert(varbinary(255),v.c67),(1-abs(sign(c.colnum-68))) ,255) +
- substring(convert(varbinary(255),v.c68),(1-abs(sign(c.colnum-69))) ,255) + substring(convert(varbinary(255),v.c69),(1-abs(sign(c.colnum-70))) ,255) +
- substring(convert(varbinary(255),v.c70),(1-abs(sign(c.colnum-71))) ,255) + substring(convert(varbinary(255),v.c71),(1-abs(sign(c.colnum-72))) ,255) +
- substring(convert(varbinary(255),v.c72),(1-abs(sign(c.colnum-73))) ,255) + substring(convert(varbinary(255),v.c73),(1-abs(sign(c.colnum-74))) ,255) +
- substring(convert(varbinary(255),v.c74),(1-abs(sign(c.colnum-75))) ,255) + substring(convert(varbinary(255),v.c75),(1-abs(sign(c.colnum-76))) ,255) +
- substring(convert(varbinary(255),v.c76),(1-abs(sign(c.colnum-77))) ,255) + substring(convert(varbinary(255),v.c77),(1-abs(sign(c.colnum-78))) ,255) +
- substring(convert(varbinary(255),v.c78),(1-abs(sign(c.colnum-79))) ,255) + substring(convert(varbinary(255),v.c79),(1-abs(sign(c.colnum-80))) ,255)
- from #cells c, sysstatistics s, sysstatistics v
- where s.id = @tabid
- and s.colidarray = convert(varbinary(1),convert(tinyint,@colid))
- and s.formatid = 104
- and v.id =* s.id
- and v.colidarray =* s.colidarray
- and v.statid =* s.statid
- and v.sequence =* s.sequence
- and v.formatid = 102
- and c.seq = s.sequence
- for read only
-
- /** Wow, I'm glad that's over **/
- /** Let's get on with the business at hand **/
-
- print "%1!",@proc_version
- print "%1!",@@version
- print ''
-
- /** Standard optdiag output **/
- begin
- print 'Server name: "%1!"',@@servername
- print ''
- print 'Specified database: "%1!"',@s_dbname
- if (@s_tabowner is null)
- print 'Specified table owner: not specified'
- else
- print 'Specified table owner: "%1!"',@s_tabowner
- if (@s_tabname is null)
- print 'Specified table: not specified'
- else
- print 'Specified table: "%1!"',@s_tabname
- if (@colname is null)
- print 'Specified column: not specified'
- else
- print 'Specified column: "%1!"',@colname
- print ''
-
- /*
- ** Check to see if the @tabname is in sysobjects.
- */
-
- open object_cursor
-
- fetch object_cursor into
- @tabid, @u_dbname, @u_dbid,
- @u_tabowner, @u_tabname
-
- while (@@sqlstatus = 0)
- begin
- print 'Table owner: "%1!"',@u_tabowner
- print 'Table name: "%1!"',@u_tabname
- print ''
-
- dbcc flushstats(@u_dbid, @tabid)
-
- select @ptn_data_pgs = convert(int, max(ptn_data_pgs(@tabid, partitionid)))
- from syspartitions
- where id = @tabid
-
- ---------------------
- -- Work on Indexes --
- ---------------------
- open index_cursor
- fetch index_cursor into
- @indid ,@index_name ,@dol_clustered, @clustered, @keycnt
-
- while (@@sqlstatus = 0)
- begin
- select @keycnt = @keycnt - isnull(abs(sign(@clustered - 1)),0)
- ,@index_cols = null
- while (@keycnt > 0)
- begin
- select @index_cols = substring(', ' ,abs(sign(@keycnt - 1)),2)
- + '"' + index_col(@u_tabname, @indid, @keycnt, user_id(@u_tabowner)) + '"'
- + @index_cols
- select @keycnt = @keycnt - 1
- end
- select @leafcnt = ltrim(convert(varchar(32),convert(int,leafcnt))),
- @pagecnt = ltrim(convert(varchar(32),convert(int,pagecnt))),
- @emptypgcnt = ltrim(convert(varchar(32),convert(int,emptypgcnt))),
- @rowcnt = ltrim(convert(varchar(32),str(round(convert(double precision,rowcnt),16),32,16))),
- @forwrowcnt = ltrim(convert(varchar(32),str(round(convert(double precision,forwrowcnt),16),32,16))),
- @delrowcnt = ltrim(convert(varchar(32),str(round(convert(double precision,delrowcnt),16),32,16))),
- @dpagecrcnt = ltrim(convert(varchar(32),str(round(convert(double precision,dpagecrcnt),16),32,16))),
- @dpagecr = ltrim(convert(varchar(32),str(round(convert(double precision,dpagecrcnt),16),32,16))),
- @ipagecrcnt = ltrim(convert(varchar(32),str(round(convert(double precision,ipagecrcnt),16),32,16))),
- @ipagecr = ltrim(convert(varchar(32),str(round(convert(double precision,ipagecrcnt),16),32,16))),
- @drowcrcnt = ltrim(convert(varchar(32),str(round(convert(double precision,drowcrcnt),16),32,16))),
- @drowcr = ltrim(convert(varchar(32),str(round(convert(double precision,drowcrcnt),16),32,16))),
- @oamapgcnt = ltrim(convert(varchar(32),convert(int,oamapgcnt))),
- @extent0pgcnt = ltrim(convert(varchar(32),convert(int,extent0pgcnt))),
- @datarowsize = ltrim(convert(varchar(32),str(round(convert(double precision,datarowsize),16),32,16))),
- @leafrowsize = ltrim(convert(varchar(32),str(round(convert(double precision,leafrowsize),16),32,16))),
- @indexheight = ltrim(convert(varchar(32),convert(smallint,indexheight))),
- @spare1 = ltrim(convert(varchar(32),convert(int,spare1))),
- @spare2 = ltrim(convert(varchar(32),str(round(convert(double precision,spare2),16),32,16)))
- from systabstats
- where id = @tabid and indid = @indid
-
- ----------------------
- -- print index info --
- ----------------------
-
- if (@indid = 0)
- print 'Statistics for table: "%1!"',@index_name
- else if (1 in (@clustered,@dol_clustered))
- print 'Statistics for index: "%1!" (clustered)',@index_name
- else
- print 'Statistics for index: "%1!" (nonclustered)',@index_name
- if (@indid > 0)
- print 'Index column list: %1!',@index_cols
- else
- print ''
- if (@clustered = 1 or @indid = 0)
- print ' Data page count: %1!',@pagecnt
- else
- print ' Leaf count: %1!',@leafcnt
-
- if (1 in (@clustered,@dol_clustered) or @indid = 0)
- print ' Empty data page count: %1!',@emptypgcnt
- else
- print ' Empty leaf page count: %1!',@emptypgcnt
-
- if (@clustered = 1 or @indid = 0)
- begin
- print ' Data row count: %1!',@rowcnt
- print ' Forwarded row count: %1!',@forwrowcnt
- print ' Deleted row count: %1!',@delrowcnt
- end
-
- print ' Data page CR count: %1!',@dpagecrcnt
- if ((@clustered = 0 or @dol_clustered = 1) and @indid > 0)
- begin
- print ' Index page CR count: %1!',@ipagecrcnt
- print ' Data row CR count: %1!',@drowcrcnt
- end
-
- if (@clustered = 1 or @indid = 0)
- print ' OAM + allocation page count: %1!',@oamapgcnt
-
- if (@indid = 0)
- print ' First extent data pages: %1!',@extent0pgcnt
- else
- print ' First extent leaf pages: %1!',@extent0pgcnt
- if (@clustered = 1 or @indid = 0)
- print ' Data row size: %1!',@datarowsize
- else
- print ' Leaf row size: %1!',@leafrowsize
- if (@indid > 0)
- print ' Index height: %1!',@indexheight
- if ((@clustered = 1 or @indid = 0) and @ptn_data_pgs is not null)
- print ' Pages in largest partition: %1!',@ptn_data_pgs
-
- print ''
- print ' Derived statistics:'
-
- if ( (select lower(@option)) in ("cr","cluster ratio") )
- begin
- print ' Data page cluster ratio: proprietary'
- end
- else
- print ' Data page cluster ratio: proprietary'
- if ((@clustered = 0 or @dol_clustered = 1) and @indid > 0)
- begin
- print ' Index page cluster ratio: proprietary'
- print ' Data row cluster ratio: proprietary'
- end
- print ''
-
- fetch index_cursor into
- @indid ,@index_name ,@dol_clustered ,@clustered, @keycnt
- end
- close index_cursor
-
- ---------------------
- -- Work on Columns --
- ---------------------
- open col_cursor
- fetch col_cursor into
- @colid, @colidarray, @colidarray_len, @colname, @statid, @c1stat, @last_updt, @rc_density, @tot_density
- ,@steps_act, @steps_req, @typename, @collength, @precision, @scale, @r_sel, @between_sel
-
- while (@@sqlstatus = 0)
- begin
- if (@steps_act is not null)
- print 'Statistics for column: "%1!"',@colname
- else
- begin -- BUILD A COLUMN GROUP NAME
- select @colgroup_name = null
- while (@colidarray_len > 0)
- begin
- select @colgroup_name =
- substring(', ' ,abs(sign(@colidarray_len - 1)),2)
- + '"' + name + '"'
- + @colgroup_name
- from syscolumns
- where id = @tabid
- and colid = convert(tinyint,substring(@colidarray,@colidarray_len,1))
- select @colidarray_len = @colidarray_len - 1
- end
- print 'Statistics for column group: %1!',@colgroup_name
- end
- print 'Last update of column statistics: %1!',@last_updt
- if (@c1stat & 2 = 2)
- print 'Statistics loaded from Optdiag.'
- print ''
- print ' Range cell density: %1!',@rc_density
- print ' Total density: %1!',@tot_density
- if (@r_sel is not null)
- print ' Range selectivity: %1!',@r_sel
- else
- print ' Range selectivity: default used (0.33)'
- if (@between_sel is not null)
- print ' In between selectivity: %1!',@between_sel
- else
- print ' In between selectivity: default used (0.25)'
- print ''
- if (@steps_act is not null) /** Print a Histogram **/
- begin
- truncate table #cells
- select @freq_cell = 0, @seq = 1
- select @used_count = isnull(sum(usedcount),0)
- from sysstatistics
- where id = @tabid
- and statid = @statid
- and colidarray = convert(varbinary(1),convert(tinyint,@colid))
- and formatid = 104
- and sequence = @seq
- while (@used_count > 0)
- begin
- select @rownum = 1
- while (@rownum <= @used_count)
- begin
- insert into #cells(seq,colnum) values (@seq,@rownum)
- select @rownum = @rownum + 1
- end
- select @seq = @seq + 1
- select @used_count = isnull(sum(usedcount),0)
- from sysstatistics
- where id = @tabid
- and statid = @statid
- and colidarray = convert(varbinary(1),convert(tinyint,@colid))
- and formatid = 104
- and sequence = @seq
- end
-
- print 'Histogram for column: "%1!"',@colname
- if (@typename in ("int","intn"))
- select @typename = "integer"
- if (@typename = "float" and @collength = "4")
- select @typename = "real"
- if (@typename = "float" and @collength = "8")
- select @typename = "double precision"
- if (@typename in ("varchar","nvarchar","char","nchar","binary","varbinary","float","floatn"))
- print 'Column datatype: %1!(%2!)',@typename,@collength
- else if (@typename in ("numeric","decimal","numericn","decimaln"))
- print 'Column datatype: %1!(%2!,%3!)',@typename,@precision,@scale
- else
- print 'Column datatype: %1!',@typename
- print 'Requested step count: %1!',@steps_req
- print 'Actual step count: %1!',@steps_act
- print ''
- print ' Step Weight Value'
- print ''
-
- open histogram_cursor
- fetch histogram_cursor into
- @step, @weight, @value_raw
- while (@@sqlstatus = 0)
- begin
- select
- @value_c =
- CASE
- WHEN @typename in ("varchar","nvarchar","char","nchar")
- THEN '"' + convert(varchar(255),@value_raw) + '"'
-
- WHEN @typename in ("int","intn","integer")
- THEN str(convert(int,@value_raw),10)
-
- WHEN @typename in ("smallint")
- THEN str(convert(smallint,@value_raw),10)
-
- WHEN @typename in ("tinyint")
- THEN str(convert(tinyint,@value_raw),10)
-
- /** Oh, oh, a scaled numeric, where does the decimal place go??? **/
- WHEN (@typename in ("numeric","decimal","numericn","decimaln") and convert(smallint,@scale) > 0)
- THEN str(convert(numeric(38),right(replicate(0x00,255-convert(smallint,@collength)) + @value_raw,17))
- /* move over @scale decimal places please */
- /power(convert(numeric,10),convert(smallint,@scale))
- /* make room for @precision, minus, and decimal signs */
- , convert(smallint,@precision)+2,convert(smallint,@scale))
-
- WHEN (@typename in ("numeric","decimal","numericn","decimaln") and @scale = "0")
- THEN str(convert(numeric(38),right(replicate(0x00,255-convert(smallint,@collength)) + @value_raw,17))
- , convert(smallint,@precision))
-
- WHEN (@typename in ("float","floatn","real") and @collength = "4")
- THEN str(convert(real,@value_raw),40,8)
-
- WHEN (@typename in ("float","floatn","double precision") and @collength = "8")
- THEN str(convert(double precision,@value_raw),40,16)
-
- WHEN @typename in ("money","moneyn","smallmoney")
- THEN str(convert(money,@value_raw),22,2)
-
- WHEN @typename in ("datetime","datetimn")
- THEN '"' + convert(varchar(255),convert(datetime,@value_raw),109) + '"'
-
- WHEN @typename in ("smalldatetime")
- THEN '"' + convert(varchar(255),convert(smalldatetime,@value_raw),100) + '"'
-
- ELSE @value_raw
- END
-
- if (@value_raw is null)
- select @freq_cell =1 , @prev_step = @step, @prev_weight = @weight, @value_c = "null"
- else
- begin
- select @value_c = ltrim(@value_c)
- if (@freq_cell = 1)
- begin /* Printing a frequency cell */
- if (@typename in ("binary","varbinary","timestamp"))
- begin
- print '%1! %2! < %3!',@prev_step,@prev_weight,@value_raw
- print '%1! %2! = %3!',@step,@weight,@value_raw
- end
- else
- begin
- print '%1! %2! < %3!',@prev_step,@prev_weight,@value_c
- print '%1! %2! = %3!',@step,@weight,@value_c
- end
- end
- else /* NOT printing a frequency cell */
- begin
- if (@typename in ("binary","varbinary","timestamp"))
- print '%1! %2! <= %3!',@step,@weight,@value_raw
- else
- print '%1! %2! <= %3!',@step,@weight,@value_c
- end
- select @freq_cell = 0
- end
-
- fetch histogram_cursor into
- @step, @weight, @value_raw
- end
- close histogram_cursor
- /* Is there only one cell (a freqency cell) */
- if (@freq_cell = 1)
- print '%1! %2! = %3!',@prev_step,@prev_weight,@value_c
- print ''
- end /* histogram print */
-
- fetch col_cursor into
- @colid, @colidarray, @colidarray_len, @colname, @statid, @c1stat, @last_updt, @rc_density, @tot_density
- ,@steps_act, @steps_req, @typename, @collength, @precision, @scale, @r_sel, @between_sel
- end
- close col_cursor
- -----------------------
- -- Done with columns --
- -----------------------
-
- ------------------------------
- -- print cols with no stats --
- ------------------------------
- select @keycnt = 0
- open nostats_cursor
- fetch nostats_cursor into @colname
- while (@@sqlstatus = 0)
- begin
- select @keycnt = @keycnt + 1
- if (@keycnt = 1)
- print 'No statistics for remaining columns: "%1!"',@colname
- else if (@keycnt = 2)
- print '(default values used) "%1!"',@colname
- else
- print ' "%1!"',@colname
- fetch nostats_cursor into @colname
- end
- close nostats_cursor
- if (@keycnt = 1)
- print '(default values used)'
-
- print ''
-
- fetch object_cursor into
- @tabid, @u_dbname, @u_dbid,
- @u_tabowner, @u_tabname
- end
- close object_cursor
- -----------------------
- -- Done with Objects --
- -----------------------
- end
-
- go
-
- grant execute on sp__optdiag to public
- go
- use sybsystemprocs
- go
- drop procedure sp__rev_configure
- go
- create procedure sp__rev_configure
- as
- declare @sptlang int /* current sessions language */
- declare @whichone int /* using english or default lang ? */
-
- if @@trancount = 0
- begin
- set transaction isolation level 1
- set chained off
- end
-
- select @whichone = 0
-
- select @sptlang = @@langid
-
- if @@langid != 0
- begin
- if not exists (
- select * from master.dbo.sysmessages where error
- between 17015 and 17049
- and langid = @@langid)
- select @sptlang = 0
- else
- if not exists (
- select * from master.dbo.sysmessages where error
- between 17100 and 17109
- and langid = @@langid)
- select @sptlang = 0
- end
-
- if @sptlang = 0
- begin
- select "-- sp_configure settings"
- = "sp_configure '" + name + "', "
- + convert( char(12), c.value)
- + char(13) + char(10) + "go"
- from master.dbo.spt_values a,
- master.dbo.syscurconfigs c
- where a.type = "C"
- and a.number *= c.config
- and a.number >= 0
- end
- else
- begin
- select "-- sp_configure settings"
- = "sp_configure '" + name + "', "
- + convert(char(12), c.value)
- + char(13) + char(10) + "go"
- from master.dbo.spt_values a,
- master.dbo.syscurconfigs c,
- master.dbo.sysmessages d
- where type = "C"
- and a.number *= c.config
- and a.number >= 0
- and msgnum = error and isnull(langid, 0) = @sptlang
- end
- return (0)
- go
- --
- -- You may or may not wish to do the following.
- --
- --grant execute on sp__rev_configure to public
- --gouse sybsystemprocs
- go
-
- /*
- * DROP PROC sp__revroles
- */
- IF OBJECT_ID('sp__revroles') IS NOT NULL
- BEGIN
- DROP PROC sp__revroles
- PRINT '<<< Dropped proc sp__revroles >>>'
- END
- go
- create procedure sp__revroles
- as
- /* Created 03/05/97 by Clayton Groom
- creates a reverse engineered set of commands to restore user roles
- */
- select "exec sp_role grant, " + u.name + ", " + s.name + char(13) + char(10) + "go"
- from master..syssrvroles s,
- sysroles r,
- sysusers u
- where r.id = s.srid
- and r.lrid = u.uid
- and s.name <> u.name
- go
-
- IF OBJECT_ID('sp__revroles') IS NOT NULL
- PRINT '<<< Created proc sp__revroles >>>'
- ELSE
- PRINT '<<< Failed to create proc sp__revroles >>>'
- go
- use sybsystemprocs
- go
-
- if object_id('sp_days') is not NULL
- drop proc sp_days
- go
-
- create proc sp_days @days tinyint OUTPUT, @month tinyint, @year smallint
- as
- declare @date datetime
- select @date=convert(char,@month)+'/01/'+convert(char, @year)
- select @days=datediff(dd,@date, dateadd(mm,1,@date))
- select @days
- go
-
- grant exec on sp_days to public
- gouse sybsystemprocs
- go
-
- if object_id('dbo.sp_ddl_create_table') is not null
- drop procedure sp_ddl_create_table
- print "Dropping sp_ddl_create_table"
- go
-
- create proc sp_ddl_create_table
- as
-
- -- Creates the DDL for all the user tables in the
- -- current database
-
- select right('create table ' + so1.name + '(' + '
- ', 255 * ( abs( sign(sc1.colid - 1) - 1 ) ) )+
- sc1.name + ' ' +
- st1.name + ' ' +
- substring( '(' + rtrim( convert( char, sc1.length ) ) + ') ', 1,
- patindex('%char', st1.name ) * 10 ) +
- substring( '(' + rtrim( convert( char, sc1.prec ) ) + ', ' + rtrim(
- convert( char, sc1.scale ) ) + ') ' , 1, patindex('numeric', st1.name ) * 10 ) +
- substring( 'NOT NULL', ( convert( int, convert( bit,( sc1.status & 8 ) ) ) * 4 ) + 1,
- 8 * abs(convert(bit, (sc1.status & 0x80)) - 1 ) ) +
- right('identity ', 9 * convert(bit, (sc1.status & 0x80)) ) +
- right(',', 5 * ( convert(int,sc2.colid) - convert(int,sc1.colid) ) ) +
- right(' )
- ' + 'go' + '
- ' + '
- ', 255 * abs( sign( ( convert(int,sc2.colid) - convert(int,sc1.colid) ) ) -
- 1 ) )
- from sysobjects so1,
- syscolumns sc1,
- syscolumns sc2,
- systypes st1
- where so1.type = 'U'
- and sc1.id = so1.id
- and st1.usertype = sc1.usertype
- and sc2.id = sc1.id
- and sc2.colid = (select max(colid)
- from syscolumns
- where id = sc1.id)
- order by so1.name, sc1.colid
- go
-
- if object_id('dbo.sp_ddl_create_table') is not null
- begin
- grant execute on sp_ddl_create_table to public
- print "Created sp_ddl_create_table"
- end
- else
- print "Failed to create sp_ddl_create_table"
- go
-
- goIF OBJECT_ID('sp_desc') IS NOT NULL
- BEGIN
- DROP PROCEDURE sp_desc
- IF OBJECT_ID('sp_desc') IS NOT NULL
- PRINT '<<< FAILED DROPPING PROCEDURE sp_desc >>>'
- ELSE
- PRINT '<<< DROPPED PROCEDURE sp_desc >>>'
- END
- go
-
- create procedure sp_desc @table_name char(30) = NULL
- --
- -- Snarfed from CDS, cannot remember who posted the original.
- -- Update for dec and numeric data types, plus ensured that
- -- varchars came out as that.
- --
- -- David Owen 2001 (dowen@midsomer.org)
-
- as
- -- This stored procedure returns a description of a SQL Server table in
- -- a format more like the Oracle DESC command.
-
- if (@table_name IS NULL)
- begin
- raiserror 20001 "Must specify table name for sp_desc!"
- return
- end
-
- declare @min_id int
-
- select
- C.colid 'column_id',
- C.name 'column_name',
- T.name 'column_type',
- T.usertype 'user_type',
- T.type 'base_type',
- C.length 'column_length',
- C.scale 'column_scale',
- C.status 'column_is_null'
- into
- #tab_descr
- from
- syscolumns C,
- sysobjects O,
- systypes T
- where
- C.id = O.id
- and C.usertype = T.usertype
- and O.name = @table_name
-
- if (@@rowcount = 0)
- begin
- raiserror 20001 "Table specified does not exist"
- return
- end
-
- update
- #tab_descr
- set
- user_type = systypes.usertype
- from
- systypes
- where
- systypes.type = #tab_descr.base_type
- and systypes.usertype < 100
-
- -- update
- -- #tab_descr
- -- set
- -- column_type = name
- -- from
- -- systypes
- -- where
- -- #tab_descr.user_type = systypes.usertype
-
- update
- #tab_descr
- set
- column_type = name
- from
- systypes st,
- #tab_descr td
- where td.base_type = st.type
- and td.user_type > 100
-
- update
- #tab_descr
- set
- column_type = column_type + "(" + LTRIM(RTRIM(str(column_length)))+")"
- where
- column_type in ("char", "varchar", "nchar", "nvarchar", "binary", "varbinary")
-
- update
- #tab_descr
- set
- column_type = column_type + "(" +
- LTRIM(RTRIM(str(column_length))) +
- "," +
- LTRIM(RTRIM(str(column_scale))) +
- ")"
- where
- column_type in ("dec", "numeric", "decimal")
-
- -- update
- -- #tab_descr
- -- set
- -- column_type = "varchar("+LTRIM(RTRIM(str(column_length)))+")"
- -- where
- -- column_type = "sysname"
-
- select
- @min_id = min(column_id)
- from
- #tab_descr
-
- update
- #tab_descr
- set
- column_id = column_id - @min_id + 1
-
- print @table_name
-
- select
- convert(char(5), "("+LTRIM(str(column_id))+")") 'No.',
- column_name 'Column Name',
- convert(char(20), column_type) 'Datatype',
- case column_is_null
- when 0 then "NOT NULL"
- else ""
- end
- from
- #tab_descr
- order by column_id
- go
-
- IF OBJECT_ID('dbo.sp_desc') IS NOT NULL
- BEGIN
- PRINT '<<< CREATED PROCEDURE dbo.sp_desc >>>'
- GRANT EXECUTE ON dbo.sp_desc TO public
- END
- ELSE
- PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_desc >>>'
- go
- use sybsystemprocs
- go
- /*
- * DROP PROC dbo.sp_devusage
- */
- IF OBJECT_ID('dbo.sp_devusage') IS NOT NULL
- BEGIN
- DROP PROC dbo.sp_devusage
- PRINT '<<< DROPPED PROC dbo.sp_devusage >>>'
- END
- go
- CREATE PROCEDURE sp_devusage (@device_name char(30) = NULL)
- AS
- IF @device_name != NULL
- BEGIN
- SELECT dev_name = substring(dv.name,1,20),db_name = substring(db.name,1,20),
- size_mb = u.size/512.0,
- u.segmap,
- vdevno = u.vstart/power(2,24)
- FROM master..sysusages u , master..sysdevices dv,
- master..sysdatabases db
- WHERE u.vstart between dv.low and dv.high
- AND db.dbid = u.dbid
- AND cntrltype = 0
- AND dv.name = @device_name
- ORDER BY dv.name
- COMPUTE sum(u.size/512.0) by dv.name
- END
- ELSE
- BEGIN
- SELECT dev_name = substring(dv.name,1,20),db_name = substring(db.name,1,20),
- size_mb = u.size/512.0, u.segmap,
- vdevno = u.vstart/power(2,24)
- FROM master..sysusages u , master..sysdevices dv,
- master..sysdatabases db
- WHERE u.vstart between dv.low and dv.high
- AND db.dbid = u.dbid
- AND cntrltype = 0
- ORDER BY dv.name
- COMPUTE sum(u.size/512.0) by dv.name
- END
- go
-
- IF OBJECT_ID('dbo.sp_devusage') IS NOT NULL
- PRINT '<<< CREATED PROC dbo.sp_devusage >>>'
- ELSE
- PRINT '<<< FAILED CREATING PROC dbo.sp_devusage >>>'
- go
- /*
- * Granting/Revoking Permissions on dbo.sp_devusage
- */
- GRANT EXECUTE ON dbo.sp_devusage TO public
- go
-
- /*>>>>>>>>>>>>>>>>>>>>>>>>>>> sp_dos <<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
- IF OBJECT_ID('dbo.sp_dos') IS NOT NULL
- DROP PROCEDURE sp_dos
- go
-
- CREATE PROCEDURE sp_dos
- @vcObjectName varchar(30) = NULL
- AS
- /***********************************************************************
- * sp_dos - Display Object Scope
- * This procedure graphically displays the scope of a object in
- * the database.
- *
- * Copyright 1996, all rights reserved.
- *
- * Author: David W. Pledger, Strategic Data Systems, Inc.
- *
- * Parameters
- * ----------------------------------------------------------------
- * Name In/Out Description
- * ----------------------------------------------------------------
- * @vcObjectName In Mandatory - The exact name of a single
- * database object for which the call
- * hierarchy is to be extracted.
- *
- * Selected Data
- * A sample report follows:
- * ----------------------------------------------------------------
- *
- * SCOPE OF EFFECT FOR OBJECT: ti_users
- * +------------------------------------------------------------------+
- * (T) ti_users (Trigger on table 'users')
- * |
- * +--(P) pUT_GetError
- * | |
- * | +--(U) ui_error
- * |
- * +--(U) BGRP
- * |
- * +--(U) user_information (See Triggers: tu_user_information)
- * |
- * +--(U) users (See Triggers: ti_users, tu_users, td_users)
- * |
- * +--(P) pUT_LUDVersion
- * |
- * +--(P) pUT_GetError
- * | |
- * | +--(U) ui_error
- * |
- * +--(U) BGRP_LUDVersion
- *
- * <End of Sample>
- *
- * Return Values
- * ----------------------------------------------------------------
- * Value Description
- * ----------------------------------------------------------------
- * < -99 Unexpected error - should never occur.
- *
- * -99 to -1 Sybase **reserved** return status values.
- *
- * 0 Execution succeeded
- *
- * 1 Execution of this procedure failed.
- *
- * > 1 Unexpected error - should never occur.
- *
- ***********************************************************************/
- BEGIN
-
- /*------------------- Local Declarations -------------------------*/
- DECLARE @iObjectID int /* System ID of object */
- DECLARE @cObjectType char(1) /* System Object Type code */
- DECLARE @vcName varchar(30) /* System Object name */
- DECLARE @vcMsg varchar(255) /* Error Message if needed */
- DECLARE @iInsTrigID int /* Insert Trigger ID */
- DECLARE @iUpdTrigID int /* Update Trigger ID */
- DECLARE @iDelTrigID int /* Delete Trigger ID */
- DECLARE @vcErrMsg varchar(255) /* Error Message */
-
- /* Local variables to facilitate descending the parent-child
- ** object hierarchy.
- */
- DECLARE @iCurrent int /* Current node in the tree */
- DECLARE @iRoot int /* The root node in the tree */
- DECLARE @iLevel int /* The current level */
-
- /* Local variables that contain the fragments of the text to
- ** be displayed while descending the hierarchy.
- */
- DECLARE @iDotIndex int /* Index for locating periods */
- DECLARE @cConnector char(3) /* '+--' */
- DECLARE @cSibSpacer char(3) /* '| ' */
- DECLARE @cBar char(1) /* '|' */
- DECLARE @cSpacer char(3) /* ' ' */
- DECLARE @cPrntStrng1 char(255) /* The first string to print */
- DECLARE @cPrntStrng2 char(255) /* The second string to print */
- DECLARE @iLoop int /* Temp var used for loop */
- DECLARE @vcDepends varchar(255) /* Dependency String */
- DECLARE @iDependsItem int /* Index to a string item */
-
- /* Create a temporary table to handle the hierarchical
- ** decomposition of the task parent-child relationship. The Stack
- ** table keeps track of where we are while the leaf table keeps
- ** track of the leaf tasks which need to be performed.
- */
- CREATE TABLE #Stack
- (iItem int,
- iLevel int)
-
- /*------------------- Validate Input Parameters --------------------*/
- /* Make sure the table is local to the current database. */
- IF (@vcObjectName LIKE "%.%.%") AND (SUBSTRING(@vcObjectName, 1,
- CHARINDEX(".", @vcObjectName) - 1) != DB_NAME())
- GOTO ErrorNotLocal
-
- /* Now check to see that the object is in sysobjects. */
- IF OBJECT_ID(@vcObjectName) IS NULL
- GOTO ErrorNotFound
-
- /* ---------------------- Initialization -------------------------*/
-
- /* Do print any rowcounts while this is in progress. */
- SET NOCOUNT ON
-
- /* Retrieve the object ID out of sysobjects */
- SELECT @iObjectID = O.id,
- @cObjectType = O.type
- FROM sysobjects O
- WHERE O.name = @vcObjectName
-
- /* Make sure a job exists. */
- IF NOT (@@rowcount = 1 and @@error = 0 and @iObjectID > 0)
- GOTO ErrorNotFound
-
- /* Initialize the print string pieces. */
- SELECT @cConnector = "+--",
- @cSibSpacer = "|..",
- @cBar = "|",
- @cSpacer = "...",
- @cPrntStrng1 = "",
- @cPrntStrng2 = ""
-
- /* Print a separator line. */
- PRINT " "
- PRINT "** Utility by David Pledger, Strategic Data Systems, Inc. **"
- PRINT "** PO Box 498, Springboro, OH 45066 **"
- PRINT " "
- PRINT " SCOPE OF EFFECT FOR OBJECT: %1!",@vcObjectName
- PRINT "+------------------------------------------------------------------+"
-
- /* -------------------- Show the Hierarchy -----------------------*/
- /* Find the root task for this job. The root task is the only task
- ** that has a parent task ID of null.
- */
- SELECT @iRoot = @iObjectID
-
- /* Since there is a root task, we can assign the first
- ** stack value and assign it a level of one.
- */
- SELECT @iCurrent = @iRoot,
- @iLevel = 1
-
- /* Prime the stack with the root level. */
- INSERT INTO #Stack values (@iCurrent, 1)
-
- /* As long as there are nodes which have not been visited
- ** within the tree, the level will be > 0. Continue until all
- ** nodes are visited. This outer loop descends the tree through
- ** the parent-child relationship of the nodes.
- */
- WHILE (@iLevel > 0)
- BEGIN
-
- /* Do any nodes exist at the current level? If yes, process them.
- ** If no, then back out to the previous level.
- */
- IF EXISTS
- (SELECT *
- FROM #Stack S
- WHERE S.iLevel = @iLevel)
- BEGIN
-
- /* Get the smallest numbered node at the current level. */
- SELECT @iCurrent = min(S.iItem)
- FROM #Stack S
- WHERE S.iLevel = @iLevel
-
- /* Get the name and type of this node. */
- SELECT @cObjectType = O.type,
- @vcName = O.name,
- @iInsTrigID = ISNULL(O.instrig, 0),
- @iUpdTrigID = ISNULL(O.updtrig, 0),
- @iDelTrigID = ISNULL(O.deltrig, 0)
- FROM sysobjects O
- WHERE O.id = @iCurrent
-
- /*
- * *=================================================* *
- * * Print out data for this node. (Consider * *
- * * making this a separate procedure.) * *
- * *=================================================* *
- */
-
- /* Initialize the print strings to empty (different from NULL).
- ** @cPrntStrng1 is used to 'double space' the output and
- ** contains the necessary column connectors, but no data.
- ** @cPrntStrng2 contains the actual data at the end of the
- ** string.
- */
- SELECT @cPrntStrng1 = ""
- SELECT @cPrntStrng2 = ""
-
- /* Level 1 is the root node level. All Jobs have a single
- ** root task. All other tasks are subordinate to this task.
- ** No job may have more than one root task.
- */
- IF @iLevel = 1
- BEGIN
- /* Print data for the root node. */
- SELECT @cPrntStrng1 = "",
- @cPrntStrng2 = "(" + @cObjectType + ") " + @vcName
- END
- ELSE /* Else part of (IF @iLevel = 1) */
- BEGIN
-
- /* Initialize loop variable to 2 since level one has
- ** already been processed for printing.
- */
- SELECT @iLoop = 2
-
- /* Look at the values on the stack at each level to
- ** determine which symbol should be inserted into the
- ** print string.
- */
- WHILE @iLoop <= @iLevel
- BEGIN
-
- /* While the loop variable is less than the current
- ** level, add the appropriate spacer to line up
- ** the printed output.
- */
- IF @iLoop < @iLevel
- BEGIN
-
- /* Is there a sibling (another node which exists
- ** at the same level) on the stack? If so, use
- ** one type of separator; otherwise, use another
- ** type of separator.
- */
- IF EXISTS(SELECT * FROM #Stack WHERE iLevel = @iLoop)
- BEGIN
- SELECT @cPrntStrng1 = rtrim(@cPrntStrng1) +
- @cSibSpacer
- SELECT @cPrntStrng2 = rtrim(@cPrntStrng2) +
- @cSibSpacer
- END
- ELSE
- BEGIN
- SELECT @cPrntStrng1 = rtrim(@cPrntStrng1) + @cSpacer
- SELECT @cPrntStrng2 = rtrim(@cPrntStrng2) + @cSpacer
- END
- END
- ELSE /* Else part of (IF @iLoop < @iLevel) */
- BEGIN
- SELECT @cPrntStrng1 = rtrim(@cPrntStrng1) + @cBar
- SELECT @cPrntStrng2 = rtrim(@cPrntStrng2) +
- @cConnector + "(" + @cObjectType + ") " +
- @vcName
- END
-
- /* Increment the loop variable */
- SELECT @iLoop = @iLoop + 1
-
- END /* While @iLoop <= @iLevel */
- END /* IF @iLevel = 1 */
-
- /* Spaces are inserted into the string to separate the levels
- ** into columns in the printed output. Spaces, however, caused
- ** a number of problems when attempting to concatenate the
- ** two strings together. To perform the concatenation, the
- ** function rtrim was used to remove the end of the string.
- ** This also removed the spaces we just added. To aleviate
- ** this problem, we used a period (.) wherever there was
- ** supposed to be a space. Now that we are ready to print
- ** the line of text, we need to substitute real spaces
- ** wherever there is a period in the string. To do this,
- ** we simply look for periods and substitute spaces. This
- ** has to be done in a loop since there is no mechanism to
- ** make this substitution in the whole string at once.
- */
-
- /* Find the first period. */
- SELECT @iDotIndex = charindex (".", @cPrntStrng1)
-
- /* If a period exists, substitute a space for it and then
- ** find the next period.
- */
- WHILE @iDotIndex > 0
- BEGIN
- /* Substitute the space */
- SELECT @cPrntStrng1 = stuff(@cPrntStrng1, @iDotIndex, 1, " ")
-
- /* Find the next. */
- SELECT @iDotIndex = charindex (".", @cPrntStrng1)
- END
-
- /* Do the same thing for the second print string. */
- SELECT @iDotIndex = charindex (".", @cPrntStrng2)
- WHILE @iDotIndex > 0
- BEGIN
- SELECT @cPrntStrng2 = stuff(@cPrntStrng2, @iDotIndex, 1, " ")
- SELECT @iDotIndex = charindex (".", @cPrntStrng2)
- END
-
- SELECT @vcDepends = NULL
-
- IF @iInsTrigID > 0
- SELECT @vcDepends = OBJECT_NAME(@iInsTrigID) + " (Insert)"
-
- IF @iUpdTrigID > 0
- IF @vcDepends IS NULL
- SELECT @vcDepends = OBJECT_NAME(@iUpdTrigID) + " (Update)"
- ELSE
- SELECT @vcDepends = @vcDepends + ", " +
- OBJECT_NAME(@iUpdTrigID) + " (Update)"
-
- IF @iDelTrigID > 0
- IF @vcDepends IS NULL
- SELECT @vcDepends = OBJECT_NAME(@iDelTrigID) + " (Delete)"
- ELSE
- SELECT @vcDepends = @vcDepends + ", " +
- OBJECT_NAME(@iDelTrigID) + " (Delete)"
-
- IF @vcDepends IS NOT NULL
- IF @cObjectType = "T"
- SELECT @cPrntStrng2 = @cPrntStrng2 +
- " (Trigger on table '" + @vcDepends + "')"
- ELSE
- SELECT @cPrntStrng2 = @cPrntStrng2 +
- " (See Triggers: " + @vcDepends + ")"
-
- /* Remove trailing blanks from the first print string. */
- SELECT @cPrntStrng1 = rtrim(@cPrntStrng1)
- SELECT @cPrntStrng2 = rtrim(@cPrntStrng2)
-
- /* Print the two strings. */
- PRINT @cPrntStrng1
- PRINT @cPrntStrng2
-
- /* Remove the current entry from the stack (Pop) */
- DELETE #Stack
- WHERE #Stack.iLevel = @iLevel
- AND #Stack.iItem = @iCurrent
-
- /* Add (push) to the stack all the children of the current
- ** node.
- */
- INSERT INTO #Stack
- SELECT D.depid,
- @iLevel + 1
- FROM sysdepends D
- WHERE D.id = @iCurrent
-
- /* If any were added, then we must descend another level. */
- IF @@rowcount > 0
- BEGIN
- SELECT @iLevel = @iLevel + 1
- END
-
- END
- ELSE
- BEGIN
- /* We have reached a leaf node. Move back to the previous
- ** level and see what else is left to process.
- */
- SELECT @iLevel = @iLevel - 1
- END
-
- END /* While (@iLevel > 0) */
-
- PRINT " "
-
- RETURN (0)
-
- /*------------------------ Error Handling --------------------------*/
- ErrorNotLocal:
- /* 17460, Table must be in the current database. */
- EXEC sp_getmessage 17460, @vcErrMsg OUT
- PRINT @vcErrMsg
- RETURN (1)
-
- ErrorNotFound:
- /* 17461, Table is not in this database. */
- EXEC sp_getmessage 17461, @vcErrMsg OUT
- PRINT @vcErrMsg
- PRINT " "
-
- PRINT "Local object types and objecs are:"
-
- SELECT "Object Type" = type,
- "Object Name" = name
- FROM sysobjects
- WHERE type IN ("U","TR","P","V")
- ORDER BY type, name
-
- RETURN (1)
-
- END
- go
-
- grant execute on sp_dos to public
- go
-
- /*
- * If sybsystemprocs exists, we wish to use it. If it fails, then we
- * should be left in either master or the users defaultdb, both of which
- * are probably what we want.
- */
-
- use sybsystemprocs
- go
-
- /* Procedure sp_freedevice, owner dbo */
- IF OBJECT_ID('sp_freedevice') IS NOT NULL
- BEGIN
-
- setuser 'dbo'
-
- DROP PROCEDURE sp_freedevice
- IF OBJECT_ID('sp_freedevice') IS NOT NULL
- PRINT '<<< FAILED TO DROP PROCEDURE sp_freedevice >>>'
- ELSE
- PRINT '<<< DROPPED PROCEDURE sp_freedevice >>>'
- END
- go
-
- setuser 'dbo'
- go
-
- /*
- * Name: sp_freedevice
- * Version: 1.1
- * Author: Unknown (if you know who it is/was let me know and I will modify this).
- * Description: Prints the current disk usage in a nice table for all of the devices on the system.
- * Part of the FAQ ASE code package. Latest version available from URL below.
- * Source: http://www.isug.com/Sybase_FAQ/ASE/section9.html
- * Maintainer: David Owen (dowen@midsomer.org)
- */
-
- create proc sp_freedevice
- @devname char(30) = null
- as
-
- declare @showdev bit
- declare @alloc int
-
- if @devname = null
- select @devname = '%'
- ,@showdev = 0
- else
- select @showdev = 1
-
- select @alloc = low
- from master.dbo.spt_values
- where type = 'E'
- and number = 1
-
- create table #freedev
- (
- name char(30)
- ,size numeric(14,2)
- ,used numeric(14,2)
- )
-
- insert #freedev
- select dev.name
- ,((dev.high - dev.low) * @alloc + 500000) / 1048576
- ,convert(numeric(14,2), sum((usg.size * @alloc + 500000) / 1048576))
- from master.dbo.sysdevices dev
- ,master.dbo.sysusages usg
- where dev.low <= usg.size + usg.vstart - 1
- and dev.high >= usg.size + usg.vstart - 1
- and dev.cntrltype = 0
- group by dev.name
-
- insert #freedev
- select name
- ,convert(numeric(14,2), ((sd.high - sd.low) * @alloc + 500000) / 1048576)
- ,0
- from master.dbo.sysdevices sd
- where sd.cntrltype = 0
- and not exists (select 1
- from #freedev fd
- where fd.name = sd.name)
-
- if @showdev = 1
- begin
- select devname = dev.name
- ,size = right(replicate(' ', 21) + convert(varchar(18),f.size) + ' MB', 21)
- ,used = right(replicate(' ', 21) + convert(varchar(18),f.used) + ' MB', 21)
- ,free = right(replicate(' ', 21) + convert(varchar(18),f.size - f.used) + ' MB', 21)
- from master.dbo.sysdevices dev
- ,#freedev f
- where dev.name = f.name
- and dev.name like @devname
-
- select dbase = db.name
- ,size = right(replicate(' ', 21) + convert(varchar(18),
- (usg.size * @alloc + 500000) / 1048576
- ) + ' MB', 21)
- ,usage = vl.name
- from master.dbo.sysdatabases db
- ,master.dbo.sysusages usg
- ,master.dbo.sysdevices dev
- ,master.dbo.spt_values vl
- where db.dbid = usg.dbid
- and usg.segmap = vl.number
- and dev.low <= usg.size + usg.vstart - 1
- and dev.high >= usg.size + usg.vstart - 1
- and dev.status & 2 = 2
- and vl.type = 'S'
- and dev.name = @devname
- end
- else
- begin
-
- select total = right(replicate(' ', 21) + convert(varchar(18), sum(size)) + ' MB', 21)
- ,used = right(replicate(' ', 21) + convert(varchar(18), sum(used)) + ' MB', 21)
- ,free = right(replicate(' ', 21) + convert(varchar(18), sum(size) - sum(used)) + ' MB', 21)
- from #freedev
-
- select devname = dev.name
- ,size = right(replicate(' ', 21) + convert(varchar(18), f.size) + ' MB', 21)
- ,used = right(replicate(' ', 21) + convert(varchar(18), f.used) + ' MB', 21)
- ,free = right(replicate(' ', 21) + convert(varchar(18), f.size - f.used) + ' MB', 21)
- from master.dbo.sysdevices dev
- ,#freedev f
- where dev.name = f.name
- end
- go
-
- IF OBJECT_ID('sp_freedevice') IS NOT NULL
- PRINT '<<< CREATED PROCEDURE sp_freedevice >>>'
- ELSE
- PRINT '<<< FAILED TO CREATE PROCEDURE sp_freedevice >>>'
- go
-
- IF OBJECT_ID('sp_freedevice') IS NOT NULL
- BEGIN
- GRANT EXECUTE ON sp_freedevice TO public
- END
- go
- use sybsystemprocs
- go
-
- if object_id('sp_helpoptions') is not null
- begin
- drop procedure sp_helpoptions
- if object_id('sp_helpoptions') is not null
- print '<<< Failed to drop procedure sp_helpoptions >>>'
- else
- print '<<< Dropped procedure sp_helpoptions >>>'
- end
- go
-
-
-
- create procedure sp_helpoptions as
-
- -- initial design by Bret Halford (bret@sybase.com) 10 Jan 2000
- -- with assistance from Kimberly Russell
- -- relies only on @@options, developed on ASE 11.5.x Solaris
-
- -- This stored procedure displays a list of SET options and indicates
- -- for each option if the option is ON or OFF
-
- -- The @@options global variable contains bits that indicate
- -- whether certain of the SET command options are on or not.
-
- -- By observing the difference (if any) in @@options value when an
- -- option is on and off, a test can be derived for that condition
-
- -- Note that @@options is not documented in the manuals and its details
- -- are possibly subject to change without notice and may vary by platform.
-
- -- This procedure can probably be expanded to test for other SET command
- -- options as well. If you come up with a test for any other SET option,
- -- please send it to me and I will add it to the procedure.
-
- declare @high_bits int
- declare @low_bits int
- select @high_bits = convert(int,substring(@@options,1,4))
- select @low_bits = convert(int,substring(@@options,5,4))
-
- if (@high_bits & 268435456 = 268435456 ) print "showplan is on"
- else print "showplan is off"
-
- if (@low_bits & 33554432 = 33554432) print "ansinull is on"
- else print "ansinull is off"
-
- if (@low_bits & 536870912 = 536870912) print "ansi_permissions is on"
- else print "ansi_permissions is off"
-
- if (@high_bits & -2147418112 = -2147418112) print "arithabort is on"
- else print "arithabort is off"
-
- if (@high_bits & 1073741824 = 1073741824) print "arithignore is on"
- else print "arithignore is off"
-
- if (@high_bits & 1073741824 = 1073741824) print "arithignore arith_overflow"
- else print "arithignore arith_overflow off"
-
- if (@high_bits & 32 = 32) print "close on endtran is on"
- else print "close on endtran is off"
-
- if (@high_bits & 32768 = 32768) print "nocount is on"
- else print "nocount is off"
-
- -- Note: if 'noexec' or 'parseonly' were on, this procedure could not run,
- -- so no test is necessary.
- print 'noexec is off'
- print 'parseonly is off.'
-
- go
-
- if object_id('sp_helpoptions') is not null
- begin
- print '<<< Created procedure sp_helpoptions >>>'
- grant execute on sp_helpoptions to public
- end
- else
- print '<<< Failed to create procedure sp_helpoptions >>>'
- go
-
- use sybsystemprocs
- go
-
- drop procedure sp_lockconfig
- go
-
- -- sp_lockconfig, 'Lists data for lock promotions and index locking schemes'
- -- sp_lockconfig, ' if SYS_FLAG is non-null include system tables'
-
-
- create procedure sp_lockconfig (@SYS_FLAG char (1) = NULL) as
- set ansinull on
- set flushmessage on
- set nocount on
- set string_rtruncation on
-
- print ' '
-
- if (@@trancount = 0)
- begin
- set chained off
-
- if (@@isolation > 1)
- begin
- set transaction isolation level 1
- end
- end
- else
- begin
- print ' sp_lockconfig CANNOT BE RUN FROM WITHIN A TRANSACTION.'
-
- print ' '
-
- return 1
- end
-
- declare @allcount varchar (7),
- @dpcount varchar (7),
- @drcount varchar (7),
- @sysval smallint,
- @tabtext varchar (12)
-
- create table #lockcfg
- (sort tinyint not null,
- type char (8) not null,
- name varchar (30) not null,
- levelx varchar ( 5) not null,
- txt varchar (33) not null)
-
- insert into #lockcfg
- select 1,
- 'Table',
- object_name (object),
- 'page',
- substring (char_value, 1, 33)
- from sysattributes
- where class = 5
- and attribute = 0
- and object_type = 'T'
-
- insert into #lockcfg
- select 1,
- 'Table',
- object_name (object),
- 'row',
- substring (char_value, 1, 33)
- from sysattributes
- where class = 5
- and attribute = 1
- and object_type = 'T'
-
- insert into #lockcfg
- select 2,
- 'Database',
- db_name (),
- 'page',
- substring (char_value, 1, 33)
- from master.dbo.sysattributes
- where class = 5
- and attribute = 0
- and object_type = 'D'
- and object = db_id ()
-
- insert into #lockcfg
- select 2,
- 'Database',
- db_name (),
- 'row',
- substring (char_value, 1, 33)
- from master.dbo.sysattributes
- where class = 5
- and attribute = 1
- and object_type = 'D'
- and object = db_id ()
-
- insert into #lockcfg
- select 3,
- 'Server',
- 'default lock scheme',
- '-',
- substring (c.value2, 1, 10)
- from master.dbo.sysconfigures f,
- master.dbo.syscurconfigs c
- where f.name = 'lock scheme'
- and f.parent <> 19
- and f.config <> 19
- and c.config = f.config
-
- insert into #lockcfg
- select 3,
- 'Server',
- '-',
- 'page',
- 'PCT = '
- + convert (varchar (11), pc.value)
- + ', LWM = '
- + convert (varchar (11), lc.value)
- + ', HWM = '
- + convert (varchar (11), hc.value)
- from master.dbo.sysconfigures pf,
- master.dbo.sysconfigures lf,
- master.dbo.sysconfigures hf,
- master.dbo.syscurconfigs pc,
- master.dbo.syscurconfigs lc,
- master.dbo.syscurconfigs hc
- where pf.config = pc.config
- and pf.name = 'page lock promotion PCT'
- and pf.parent <> 19
- and pf.config <> 19
- and lf.config = lc.config
- and lf.name = 'page lock promotion LWM'
- and lf.parent <> 19
- and lf.config <> 19
- and hf.config = hc.config
- and hf.name = 'page lock promotion HWM'
- and hf.parent <> 19
- and hf.config <> 19
-
- insert into #lockcfg
- select 3,
- 'Server',
- '-',
- 'row',
- 'PCT = '
- + convert (varchar (11), pc.value)
- + ', LWM = '
- + convert (varchar (11), lc.value)
- + ', HWM = '
- + convert (varchar (11), hc.value)
- from master.dbo.sysconfigures pf,
- master.dbo.sysconfigures lf,
- master.dbo.sysconfigures hf,
- master.dbo.syscurconfigs pc,
- master.dbo.syscurconfigs lc,
- master.dbo.syscurconfigs hc
- where pf.config = pc.config
- and pf.name = 'row lock promotion PCT'
- and pf.parent <> 19
- and pf.config <> 19
- and lf.config = lc.config
- and lf.name = 'row lock promotion LWM'
- and lf.parent <> 19
- and lf.config <> 19
- and hf.config = hc.config
- and hf.name = 'row lock promotion HWM'
- and hf.parent <> 19
- and hf.config <> 19
-
- select TYPE = type,
- OBJECT = substring (name, 1, 28),
- 'LEVEL' = levelx,
- 'LOCK DATA' = txt
- from #lockcfg
- order by sort, name, levelx
-
- print ' '
-
- if (@SYS_FLAG IS NULL)
- begin
- select @sysval = 3,
- @tabtext = 'USER'
- end
- else
- begin
- select @sysval = 1,
- @tabtext = 'USER/SYSTEM'
- end
-
- select @allcount = ltrim (substring (convert (char (10),
- convert (money,
- count (*)),
- 1),
- 1,
- 7))
- from sysobjects
- where (sysstat & 15) in (@sysval, 3)
- and (sysstat2 & 8192) = 8192
-
- select @dpcount = ltrim (substring (convert (char (10),
- convert (money,
- count (*)),
- 1),
- 1,
- 7))
- from sysobjects
- where (sysstat & 15) in (@sysval, 3)
- and (sysstat2 & 16384) = 16384
-
- select @drcount = ltrim (substring (convert (char (10),
- convert (money,
- count (*)),
- 1),
- 1,
- 7))
- from sysobjects
- where (sysstat & 15) in (@sysval, 3)
- and (sysstat2 & 32768) = 32768
-
- if ((@allcount <> '0') and (@dpcount = '0') and (@drcount = '0'))
- begin
- print ' ALL %1! TABLES USE ALLPAGES LOCKING.', @tabtext
- end
- else if ((@allcount = '0') and (@dpcount <> '0') and (@drcount = '0'))
- begin
- print ' ALL %1! TABLES USE DATAPAGES LOCKING.', @tabtext
- end
- else if ((@allcount = '0') and (@dpcount = '0') and (@drcount <> '0'))
- begin
- print ' ALL %1! TABLES USE DATAROWS LOCKING.', @tabtext
- end
- else
- begin
- if (@allcount = '0')
- begin
- print ' THERE ARE NO %1! TABLES WITH ALLPAGES LOCKING.', @tabtext
- end
- else
- begin
- print ' THERE ARE %1! %2! TABLES WITH ALLPAGES LOCKING.',
- @allcount, @tabtext
-
- print ' '
-
- select 'TABLE' = name,
- OWNER = user_name (uid)
- from sysobjects
- where (sysstat & 15) in (@sysval, 3)
- and (sysstat2 & 8192) = 8192
- order by 'TABLE', OWNER
- end
-
- print ' '
-
- if (@dpcount = '0')
- begin
- print ' THERE ARE NO %1! TABLES WITH DATAPAGES LOCKING.',
- @tabtext
- end
- else
- begin
- print ' THERE ARE %1! %2! TABLES WITH DATAPAGES LOCKING.',
- @dpcount, @tabtext
-
- print ' '
-
- select 'TABLE' = space (30),
- OWNER = space (30)
- where 1 = 2
- union
- select substring (name + ' *',
- 1,
- 30),
- user_name (uid)
- from sysobjects
- where (sysstat & 15) in (@sysval, 3)
- and (sysstat2 & 16384) = 16384
- and (sysstat2 & 131072) = 131072
- union
- select name,
- user_name (uid)
- from sysobjects
- where (sysstat & 15) in (@sysval, 3)
- and (sysstat2 & 16384) = 16384
- and (sysstat2 & 131072) <> 131072
- order by 'TABLE', OWNER
- end
-
- print ' '
-
- if (@drcount = '0')
- begin
- print ' THERE ARE NO %1! TABLES WITH DATAROWS LOCKING.',
- @tabtext
- end
- else
- begin
- print ' THERE ARE %1! %2! TABLES WITH DATAROWS LOCKING.',
- @drcount, @tabtext
-
- print ' '
-
- select 'TABLE' = space (30),
- OWNER = space (30)
- where 1 = 2
- union
- select substring (name + ' *',
- 1,
- 30),
- user_name (uid)
- from sysobjects
- where (sysstat & 15) in (@sysval, 3)
- and (sysstat2 & 32768) = 32768
- and (sysstat2 & 131072) = 131072
- union
- select name,
- user_name (uid)
- from sysobjects
- where (sysstat & 15) in (@sysval, 3)
- and (sysstat2 & 32768) = 32768
- and (sysstat2 & 131072) <> 131072
- order by 'TABLE', OWNER
- end
- end
-
- print ' '
- go
- sp_procxmode sp_lockconfig, anymode
- go
- use sybsystemprocs
- go
- /*
- * DROP PROC dbo.sp_servermap
- */
- IF OBJECT_ID('dbo.sp_servermap') IS NOT NULL
- BEGIN
- DROP PROC dbo.sp_servermap
- PRINT '<<< DROPPED PROC dbo.sp_servermap >>>'
- END
- go
-
- create proc sp_servermap (@selection varchar(10) = "ABCDEF")
- as
-
- /* produces 6 "reports" against all possible data in
- master..sysdatabases
- master..sysdevices
- master..sysusages
-
- sp_servermap help
- produces a list of the six reports.
- A subset of the complete set of reports can be requested by passing
- an argument that consists of a string containing the letters of the
- desired report.
-
- This procedure was developed on 4.9.1 server. It will run on 4.8
- and 10.0 servers, but it has not been verified that the results
- produced are correct.
- */
-
- declare @atitle varchar(40),
- @btitle varchar(40),
- @ctitle varchar(40),
- @dtitle varchar(40),
- @etitle varchar(40),
- @ftitle varchar(40),
- @stars varchar(40),
- @xstars varchar(40)
-
- set nocount on
-
- select @atitle = "A - DATABASE SEGMENT MAP",
- @btitle = "B - DATABASE INFORMATION",
- @ctitle = "C - DEVICE ALLOCATION MAP",
- @dtitle = "D - DEVICE NUMBER, DEFAULT & SPACE USAGE",
- @etitle = "E - DEVICE LOCATION",
- @ftitle = "F - MIRRORED DEVICES",
- @selection = upper(@selection),
- @stars = replicate("*",40)
-
- if @selection = "HELP" begin
- print @atitle
- print @btitle
- print @ctitle
- print @dtitle
- print @etitle
- print @ftitle
- print ""
- print "select any combination of reports by entering a string of"
- print "report letters as the argument to sp_servermap:"
- print " sp_servermap acd"
- print "will select reports A,C and D."
- print "calling sp_servermap with no argument will produce all reports"
- return
- end
-
- select @@servername, "Current Date/Time" = getdate()
- select "Version" = @@version
-
- if charindex("A",@selection) > 0
- begin
- print ""
- print @atitle
- select @xstars = substring(@stars,1,datalength(@atitle))
- print @xstars
-
- select db=substring(db.name,1,15),db.dbid,
- usg.segmap,
- segs = substring(" U",sign(usg.segmap/8)+1,1) +
- substring(" L",(usg.segmap & 4)/4+1,1) +
- substring(" D",(usg.segmap & 2)/2+1,1) +
- substring(" S",(usg.segmap & 1)+1,1),
- "device fragment"=substring(dev.name,1,15),
- "start (pg)" = usg.vstart,"size (MB)" = str(usg.size/512.,7,2)
- from master.dbo.sysusages usg,
- master.dbo.sysdevices dev,
- master.dbo.sysdatabases db
- where vstart between low and high
- and cntrltype = 0
- and db.dbid = usg.dbid
- order by db.dbid, usg.lstart
-
- print ""
- print"Segment Codes:"
- print "U=User-defined segment on this device fragment"
- print "L=Database Log may be placed on this device fragment"
- print "D=Database objects may be placed on this device fragment by DEFAULT"
- print "S=SYSTEM objects may be placed on this device fragment"
- print ""
- end
-
- if charindex("B",@selection) > 0
- begin
- print ""
- print @btitle
- select @xstars = substring(@stars,1,datalength(@btitle))
- print @xstars
-
- select db=substring(db.name,1,15),
- db.dbid,
- "size (MB)" = str(sum(usg.size)/512.,7,2),
- "db status codes " = substring(" A",(status & 4)/4+1,1) +
- substring(" B",(status & 8)/8+1,1) +
- substring(" C",(status & 16)/16+1,1) +
- substring(" D",(status & 32)/32+1,1) +
- substring(" E",(status & 256)/256+1,1) +
- substring(" F",(status & 512)/512+1,1) +
- substring(" G",(status & 1024)/1024+1,1) +
- substring(" H",(status & 2048)/2048+1,1) +
- substring(" I",(status & 4096)/4096+1,1) +
- substring(" J",(status & 16384)/16384+1,1) +
- substring(" K",(status & 64)/64+1,1) +
- substring(" L",(status & 128)/128+1,1) +
- substring(" M",(status2 & 1)/1+1,1) +
- substring(" N",(status2 & 2)/2+1,1) +
- substring(" O",(status2 & 4)/4+1,1) +
- substring(" P",(status2 & 8)/8+1,1) +
- substring(" Q",(status2 & 16)/16+1,1) +
- substring(" R",(status2 & 32)/32+1,1),
- "created" = convert(char(9),crdate,6) + " " +
- convert(char(5),crdate,8),
- "dump tran" = convert(char(9),dumptrdate,6) + " " +
- convert(char(5),dumptrdate,8)
- from master.dbo.sysdatabases db,
- master.dbo.sysusages usg
- where db.dbid =usg.dbid
- group by db.dbid
- order by db.dbid
-
- print ""
- print "Status Code Key"
- print ""
- print "Code Status"
- print "---- ----------------------------------"
- print " A select into/bulk copy allowed"
- print " B truncate log on checkpoint"
- print " C no checkpoint on recovery"
- print " D db in load-from-dump mode"
- print " E db is suspect"
- print " F ddl in tran"
- print " G db is read-only"
- print " H db is for dbo use only"
- print " I db in single-user mode"
- print " J db name has been changed"
- print " K db is in recovery"
- print " L db has bypass recovery set"
- print " M abort tran on log full"
- print " N no free space accounting"
- print " O auto identity"
- print " P identity in nonunique index"
- print " Q db is offline"
- print " R db is offline until recovery completes"
- print ""
- end
-
- if charindex("C",@selection) > 0
- begin
- print ""
- print @ctitle
- select @xstars = substring(@stars,1,datalength(@ctitle))
- print @xstars
-
- select "device fragment"=substring(dev.name,1,15),
- "start (pg)" = usg.vstart,"size (MB)" = str(usg.size/512.,7,2),
- db=substring(db.name,1,15),
- lstart,
- segs = substring(" U",sign(usg.segmap/8)+1,1) +
- substring(" L",(usg.segmap & 4)/4+1,1) +
- substring(" D",(usg.segmap & 2)/2+1,1) +
- substring(" S",(usg.segmap & 1)+1,1)
- from master.dbo.sysusages usg,
- master.dbo.sysdevices dev,
- master.dbo.sysdatabases db
- where usg.vstart between dev.low and dev.high
- and dev.cntrltype = 0
- and db.dbid = usg.dbid
- group by dev.name, usg.vstart, db.name
- having db.dbid = usg.dbid
- order by dev.name, usg.vstart
-
-
- print ""
- print "Segment Codes:"
- print "U=USER-definedsegment on this device fragment"
- print "L=Database LOG may be placed on this device fragment"
- print "D=Database objects may be placed on this device fragment by DEFAULT"
- print "S=SYSTEM objects may be placed on this device fragment"
- print ""
- end
-
- if charindex("D",@selection) > 0
- begin
- print ""
- print @dtitle
- select @xstars = substring(@stars,1,datalength(@dtitle))
- print @xstars
-
- declare @vsize int
- select @vsize = low
- from master.dbo.spt_values
- where type="E"
- and number = 3
-
- select device = substring(name,1,15),
- vdevno = convert(tinyint,substring(convert(binary(4),low),@vsize,1)),
- "default disk?" = " " + substring("NY",(status & 1)+1,1),
- "total (MB)" = str(round((high-low)/512.,2),7,2),
- used = str(round(isnull(sum(size),0)/512.,2),7,2),
- free = str(round(abs((high-low-isnull(sum(size),0))/512.),2),7,2)
- from master.dbo.sysusages,
- master.dbo.sysdevices
- where vstart between low and high
- and cntrltype=0
- group by all name
- having cntrltype=0
- order by vdevno
- end
-
- if charindex("E",@selection) > 0
- begin
- print ""
- print @etitle
- select @xstars = substring(@stars,1,datalength(@etitle))
- print @xstars
-
- select device = substring(name,1,15),
- location = substring(phyname,1,60)
- from master.dbo.sysdevices
- where cntrltype=0
- end
-
- if charindex("F",@selection) > 0
- begin
- if exists (select 1
- from master.dbo.sysdevices
- where status & 64 = 64)
- begin
-
- print ""
- print @ftitle
- select @xstars = substring(@stars,1,datalength(@ftitle))
- print @xstars
-
- select device = substring(name,1,15),
- pri =" " + substring("* **",(status/256)+1,1),
- sec = " " + substring(" ***",(status/256)+1,1),
- serial = " " + substring(" *",(status & 32)/32+1,1),
- "mirror" = substring(mirrorname,1,35),
- reads = " " + substring(" *",(status & 128)/128+1,1)
- from master.dbo.sysdevices
- where cntrltype=0
- and status & 64 = 64
- end
- else
- begin
- print ""
- print "NO DEVICES ARE MIRRORED"
- end
- end
-
- set nocount off
-
-
- go
- IF OBJECT_ID('dbo.sp_servermap') IS NOT NULL
- BEGIN
- PRINT '<<< CREATED PROC dbo.sp_servermap >>>'
- grant execute on dbo.sp_servermap to sa_role
- END
- ELSE
- PRINT '<<< FAILED CREATING PROC dbo.sp_servermap >>>'
- gouse sybsystemprocs
- go
-
- IF OBJECT_ID('dbo.sp_spaceused_table') IS NOT NULL
- BEGIN
- DROP PROCEDURE dbo.sp_spaceused_table
- IF OBJECT_ID('dbo.sp_spaceused_table') IS NOT NULL
- PRINT '<<< FAILED TO DROP dbo.sp_spaceused_table >>>'
- ELSE
- PRINT '<<< DROPPED PROC dbo.sp_spaceused_table >>>'
- END
- go
-
- create procedure sp_spaceused_table
- @list_indices int = 0
- as
- declare @type smallint, -- the object type
- @msg varchar(250), -- message output
- @dbname varchar(30), -- database name
- @tabname varchar(30), -- table name
- @length int,
- @object_id int
-
- set nocount on
-
- if @@trancount = 0
- begin
- set chained off
- end
-
- set transaction isolation level 1
-
- create table #pagecounts
- (
- name varchar(45) null,
- iname varchar(45) null,
- low int null,
- rowtotal int null,
- reserved numeric(20,9) null,
- data numeric(20,9) null,
- index_size numeric(20,9) null,
- unused numeric(20,9) null
- )
-
- select @object_id = min(id)
- from sysobjects
- where type = 'U'
- and name not like "%pagecount%"
-
- while (@object_id is not null)
- begin
- /*
- ** We want a particular object.
- */
- insert #pagecounts
- select name = o.name,
- iname = i.name,
- low = d.low,
- rowtotal = rowcnt(i.doampg),
- reserved = convert(numeric(20,9),
- (reserved_pgs(i.id, i.doampg) +
- reserved_pgs(i.id, i.ioampg))),
- data = convert(numeric(20,9), data_pgs(i.id, i.doampg)),
- index_size = convert(numeric(20,9), data_pgs(i.id, i.ioampg)),
- unused = convert(numeric(20,9),
- ((reserved_pgs(i.id, i.doampg) +
- reserved_pgs(i.id, i.ioampg)) -
- (data_pgs(i.id, i.doampg) +
- data_pgs(i.id, i.ioampg))))
- from sysobjects o
- ,sysindexes i
- ,master.dbo.spt_values d
- where i.id = @object_id
- and o.id = @object_id
- and i.id = o.id
- and d.number = 1
- and d.type = 'E'
-
- select @object_id = min(id)
- from sysobjects
- where type = 'U'
- and id > @object_id
- and name not like "%pagecount%"
-
- end
-
- select @length = max(datalength(iname))
- from #pagecounts
-
- if (@list_indices = 1)
- begin
-
- if (@length > 20)
- begin
- select index_name = iname,
- size = convert(char(10), convert(varchar(11),
- convert(numeric(11,0),
- index_size / 1024 *
- low)) + ' KB'),
- reserved = convert(char(10),
- convert(varchar(11),
- convert(numeric(11,0),
- reserved / 1024 *
- low)) + ' KB'),
- unused = convert(char(10), convert(varchar(11),
- convert(numeric(11,0), unused / 1024 *
- low)) + ' KB')
- from #pagecounts
-
- end
- else
- begin
- select index_name = convert(char(20), iname),
- size = convert(char(10), convert(varchar(11),
- convert(numeric(11,0),
- index_size / 1024 *
- low)) + ' KB'),
- reserved = convert(char(10),
- convert(varchar(11),
- convert(numeric(11,0),
- reserved / 1024 *
- low)) + ' KB'),
- unused = convert(char(10), convert(varchar(11),
- convert(numeric(11,0), unused / 1024 *
- low)) + ' KB')
- from #pagecounts
- end
- end
-
- if (@length > 20)
- begin
- select distinct name,
- rowtotal = convert(char(11), sum(rowtotal)),
- reserved = convert(char(15), convert(varchar(11),
- convert(numeric(11,0), sum(reserved) *
- (low / 1024))) + ' KB'),
- data = convert(char(15), convert(varchar(11),
- convert(numeric(11,0), sum(data) * (low / 1024)))
- + ' KB'),
- index_size = convert(char(15), convert(varchar(11),
- convert(numeric(11,0), sum(index_size) *
- (low / 1024))) + ' KB'),
- unused = convert(char(15), convert(varchar(11),
- convert(numeric(11,0), sum(unused) *
- (low / 1024))) + ' KB')
- from #pagecounts
- group by name
- end
- else
- begin
- select distinct name = convert(char(20), name),
- rowtotal = convert(char(11), sum(rowtotal)),
- reserved = convert(char(15), convert(varchar(11),
- convert(numeric(11,0), sum(reserved) *
- (low / 1024))) + ' KB'),
- data = convert(char(15), convert(varchar(11),
- convert(numeric(11,0), sum(data) * (low / 1024)))
- + ' KB'),
- index_size = convert(char(15), convert(varchar(11),
- convert(numeric(11,0), sum(index_size) *
- (low / 1024))) + ' KB'),
- unused = convert(char(15), convert(varchar(11),
- convert(numeric(11,0), sum(unused) *
- (low / 1024))) + ' KB')
- from #pagecounts
- group by name
- end
-
- return (0)
- go
-
- IF OBJECT_ID('dbo.sp_spaceused_table') IS NOT NULL
- PRINT '<<< CREATED PROC dbo.sp_spaceused_table >>>'
- ELSE
- PRINT '<<< FAILED TO CREATE PROC dbo.sp_spaceused_table >>>'
- go
- use sybsystemprocs
- go
-
- if object_id('sp_whodo') is not null
- begin
- drop procedure sp_whodo
- if object_id('sp_whodo') is not null
- print '<<< Failed to drop procedure sp_whodo >>>'
- else
- print '<<< Dropped procedure sp_whodo >>>'
- end
- go
-
- create procedure sp_whodo @loginame varchar(30) = NULL
- as
-
- declare @low int
- ,@high int
- ,@spidlow int
- ,@spidhigh int
-
- select @low = 0
- ,@high = 32767
- ,@spidlow = 0
- ,@spidhigh = 32767
-
- if @loginame is not NULL
- begin
- select @low = suser_id(@loginame)
- ,@high = suser_id(@loginame)
-
- if @low is NULL
- begin
- if @loginame like "[0-9]%"
- begin
- select @spidlow = convert(int, @loginame)
- ,@spidhigh = convert(int, @loginame)
- ,@low = 0
- ,@high = 32767
- end
- else
- begin
- print "Login %1! does not exist.", @loginame
- return (1)
- end
- end
- end
-
- select spid
- ,status
- ,substring(suser_name(suid),1,12) loginame
- ,hostname
- ,convert(char(3), blocked) blk
- ,convert(char(7), isnull(time_blocked, 0)) blk_sec
- ,convert(char(16), program_name) program
- ,convert(char(7), db_name(dbid)) dbname
- ,convert(char(16), cmd) cmd
- ,convert(char(6), cpu) cpu
- ,convert(char(7), physical_io) io
- ,convert(char(16), isnull(tran_name, "")) tran_name
- from master..sysprocesses
- where suid >= @low
- and suid <= @high
- and spid>= @spidlow
- and spid <= @spidhigh
-
- return (0)
-
- go
-
- if object_id('sp_whodo') is not null
- begin
- print '<<< Created procedure sp_whodo >>>'
- grant execute on sp_whodo to public
- end
- else
- print '<<< Failed to create procedure sp_whodo >>>'
- go
-
- use master
- go
-
- if object_id('sp_whodo') is not null
- begin
- drop procedure sp_whodo
- if object_id('sp_whodo') is not null
- print '<<< Failed to drop procedure sp_whodo >>>'
- else
- print '<<< Dropped procedure sp_whodo >>>'
- end
- go
-
- create procedure sp_whodo @loginame varchar(30) = NULL
- as
-
- declare @low int
- ,@high int
- ,@spidlow int
- ,@spidhigh int
-
- select @low = 0
- ,@high = 32767
- ,@spidlow = 0
- ,@spidhigh = 32767
-
- if @loginame is not NULL
- begin
-
- select @low = suser_id(@loginame)
- ,@high = suser_id(@loginame)
-
- if @low is NULL
- begin
- if @loginame like "[0-9]%"
- begin
- select @spidlow = convert(int, @loginame)
- ,@spidhigh = convert(int, @loginame)
- ,@low = 0
- ,@high = 32767
- end
- else
- begin
- print "No login exists with the supplied name."
- return (1)
- end
- end
- end
-
- select
- spid
- ,status
- ,substring(suser_name(suid),1,12) loginame
- ,hostname
- ,convert(char(3), blocked) blk
- ,convert(char(16), program_name) program
- ,convert(char(7), db_name(dbid)) dbname
- ,convert(char(16), cmd) cmd
- ,convert(char(6), cpu) cpu
- ,convert(char(7), physical_io) io
- from master..sysprocesses
- where suid >= @low
- and suid <= @high
- and spid >= @spidlow
- and spid <= @spidhigh
-
- return (0)
- go
-
- if object_id('sp_whodo') is not null
- begin
- print '<<< Created procedure sp_whodo >>>'
- grant execute on sp_whodo to public
- else
- print '<<< Failed to create procedure sp_whodo >>>'
- end
- goCreate procedure sp_whodoneit
- as
- Create table #usr_locks(
- spid int, dbid smallint, id int)
- Insert Into #usr_locks(spid,dbid,id)
- Select distinct spid,dbid,id
- From master..syslocks
- Select
- str(procs.spid,4) as "Spid",
- substring(isnull(suser_name(procs.suid),"Sybase"),1,12) as "User",
- hostname as "Host",
- substring(cmd,1,6) as "Cmd",
- convert(varchar(5),procs.cpu) as "Cpu",
- convert(varchar(7),physical_io) as "I/O",
- convert(varchar(3),blocked) as "Blk",
- convert(varchar(10),db_name(ul.dbid)) as "DB Name",
- ul.id as "Object Id",
- getdate() as "Date"
- From master..sysprocesses procs, #usr_locks ul
- Where procs.spid *= ul.spid
- #!/bin/csh -f
-
- isql -U<dbusr> -P<dbpw> -S<dbsvr> -w265 $*
- #!/bin/csh
- # ########################################################################
- # #
- # # SCCS Keyword Header
- # # -------------------
- # #
- # # Module Name : update_stats.csh
- # # Version : 1.8
- # # Last Modified: 2/16/98 at 17:19:38
- # # Extracted : 2/16/98 at 17:19:39
- # # Archived as : <host>:/u/sybase/SCCS/s.update_stats.csh
- # #
- # ########################################################################
-
-
-
-
-
- # upd_stats.csh
- # ------------------
- #
- # Shell to update the distribution pages for each table in a database.
- #
- # Requires sqlsa (script w/ the proper isql login for dbo of a database)
- # ex:
- # #!/bin/csh -f
- # isql -U<dbusr> -P<dbpw> -S<dbsvr> -w265 $*
- # exit($$status)
- #
- # Author: FJ Lundy, 2/96
-
-
-
- ARGS:
- set progname = `basename $0`
- if ($#argv != 2) then
- goto USAGE
- endif
- set dbdb = $1
- set parallel_jobs = $2
-
-
-
- INIT:
- # Declare intermediate files
- set filebase = /tmp/$progname:r.-D$dbdb
- set cmdfile = $filebase.sql
- set awkfile = $filebase.awk
- set tblfile = $filebase.tbl
- set workflag = $filebase.working
- set logfile = $filebase.log
- set runningflag = $filebase.running
-
- # Check for another running copy of this process
- if ( -f $runningflag ) goto ERROR
-
- # Set the running flag to prevent multiple copies of
- onintr DONE
-
- # Clean up from previous runs
- rm -f $filebase.* >& /dev/null
-
- # Set the 'running flag' (this step must FOLLOW the 'clean-up from previous
- # runs' step!
- touch $runningflag
-
- # Which OS are we running on?
- set os = `uname`
- switch ($os)
- case 'IRIX':
- case 'IRIX64':
- case 'HP-UX':
- set splitFlag = '-l'
- breaksw
- case 'Linux':
- case 'SunOS':
- set splitFlag = '-'
- breaksw
- default:
- echo 'ERROR: $progname- Unsupported Os($os). Aborting'
- exit(-1)
- endsw
-
-
-
- MAIN:
- # Start the Log
- rm -f $logfile
- echo '$0 $*' > $logfile
- echo 'NOTE: $progname- (`date`) BEGIN $progname' >> $logfile
-
-
- # Create the awk command file.
- cat << EOJ > $awkfile
- \$0 !~ /^\$/ {
- tblname = \$1
- printf('declare @msg varchar(255), @dt_start datetime, @dt_end datetime\n')
- printf('select @msg = \'Updating Statistics for: Db(%s)\'\n', '$dbdb')
- printf('print @msg\n')
- printf('select @dt_start = getdate()\n')
- printf('update statistics %s\n', tblname)
- printf('exec sp_recompile '%s'\n', tblname)
- printf('select @dt_end = getdate()\n')
- printf('select @msg = \'Table(%s)\'\n', tblname)
- printf('print @msg\n')
- printf('select @msg = \'\tstart(\' + convert(varchar, @dt_start) + \')\'\n')
- printf('print @msg\n')
- printf('select @msg = \'\t end(\' + convert(varchar, @dt_end) + \')\'\n')
- printf('print @msg\n')
- printf('print \'\'\n')
- printf('go\n\n')
- }
- EOJ
-
-
- # Create a list of tables to update the stats for
- sqlsa << EOJ | tail +3 | sed 's/^[ ]*//g' | cut -f1 -d\ > $tblfile
- set nocount on
- use $dbdb
- go
- select u.name + '.' + o.name 'Table',
- sum((reserved_pgs(i.id, i.doampg) + reserved_pgs(i.id, i.ioampg)) * 2) 'Kb'
- from sysindexes i, sysobjects o, sysusers u
- where (o.id = i.id) and (o.uid = u.uid) and (o.type = 'U' or o.type = 'S')
- group by u.name, o.name
- order by Kb desc
- go
- EOJ
-
- exit(0)
- # Split the files into equal-sized chunks based on the passed
- # parameter for the number of parallelized jobs
- @ ct = 0
- foreach tbl (`cat $tblfile`)
- @ i = $ct % $parallel_jobs
- echo '$tbl' >> $tblfile.$i
- @ ct = $ct + 1
- end
-
-
- # For each of the created table lists:
- # 1) create TSQL, 2) set a work flag 3) background the job
- @ i = 0
- set all_work_flags = ''
- foreach file ( $tblfile.* )
- # Create the T-SQL command file
- @ i = $i + 1
- echo 'set nocount on' > $cmdfile.$i
- echo 'use $dbdb' >> $cmdfile.$i
- echo 'go' >> $cmdfile.$i
- awk -f $awkfile $file >> $cmdfile.$i
-
- # Spawn a subshell and remove the working flag when done
- # Log output to a log file commonto all threads. This can possibly cause
- # lost information in the log file if all the threads come crashing in
- # at once. Oh well...
- set all_work_flags = ( $all_work_flags $workflag.$i )
- touch $workflag.$i
- (sqlsa < $cmdfile.$i >>& $logfile ; rm -f $workflag.$i) &
- end
-
-
- # Loop until all of the spawned processes are finished (as indicated by the
- # absence of working flags
- while ( 1 )
- set num_working = `ls $workflag.* | wc -l`
- if ( $num_working == 0 ) break
- sleep 10
- end # end-while: wait for work to finish
-
-
-
- DONE:
- rm $awkfile $cmdfile.* $tblfile $tblfile.*
- rm $runningflag
- echo 'NOTE: $progname- (`date`) END $progname' >> $logfile
- cat $logfile
- exit(0)
-
-
-
- USAGE:
- echo ''
- echo 'USAGE : $progname <db> <# of parallel jobs>'
- echo ' Updates the distribution pages for each user and system table in'
- echo ' the specified database.'
- echo 'REQUIRES: sqlsa'
- echo ''
- exit(-1)
-
-
-
- ERROR:
- echo ''
- echo 'ERROR: $progname- This process is already running for $dbdb. Aborting'
- echo ''
- exit(-2)
-
- # EOJ
-