Archive-name: databases/sybase-faq/part18 URL: http://www.isug.com/Sybase_FAQ Version: 1.7 Maintainer: David Owen Last-modified: 2003/01/17 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 /*>>>>>>>>>>>>>>>>>>>>>>>>>>> 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 use sybsystemprocs go drop proc sp_freedevice go 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 float ,used float ) insert #freedev select dev.name ,((dev.high - dev.low) * @alloc + 500000) / 1048576 ,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 ,((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 = convert(varchar(10),f.size) + " MB" ,used = convert(varchar(10),f.used) + " MB" ,free = convert(varchar(10),f.size - f.used) + " MB" from master.dbo.sysdevices dev ,#freedev f where dev.name = f.name and dev.name like @devname select dbase = db.name ,size = convert(varchar(10), (usg.size * @alloc + 500000) / 1048576 ) + " MB" ,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 = convert(varchar(10), sum(size)) + " MB" ,used = convert(varchar(10), sum(used)) + " MB" ,free = convert(varchar(10), sum(size) - sum(used)) + " MB" from #freedev select devname = dev.name ,size = convert(varchar(10), f.size) + " MB" ,used = convert(varchar(10), f.used) + " MB" ,free = convert(varchar(10), f.size - f.used) + " MB" from master.dbo.sysdevices dev ,#freedev f where dev.name = f.name end go grant execute on sp_freedevice to public 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 go#!/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 -- - David Alex Lamb, one of the *.answers moderators news-answers-request@mit.edu
Закладки на сайте Проследить за страницей |
Created 1996-2024 by Maxim Chirkov Добавить, Поддержать, Вебмастеру |