Sybase FAQ: 18/19 - ASE Section 9 (3 of 3)

Archive-name: databases/sybase-faq/part18
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/03/02
Posting-Frequency: posted every 3rd month
A how-to-find-the-FAQ article is posted on the intervening months.

#!/usr/bin/perl

# Author: Vincent Yin (umyin (AT) 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|monserve r/;

# 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,indexhe ight))),
@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,@s cale))

WHEN (@typename in ("numeric","decimal","numericn","decimaln") and @scale = "0")
THEN str(convert(numeric(38),right(replicate(0x00,255-convert(smallint,@collength)) + @value_raw,17))
, convert(smallint,@precision))

WHEN (@typename in ("float","floatn","real") and @collength = "4")
THEN str(convert(real,@value_raw),40,8)

WHEN (@typename in ("float","floatn","double precision") and @collength = "8")
THEN str(convert(double precision,@value_raw),40,16)

WHEN @typename in ("money","moneyn","smallmoney")
THEN str(convert(money,@value_raw),22,2)

WHEN @typename in ("datetime","datetimn")
THEN '"' + convert(varchar(255),convert(datetime,@value_raw), 109) + '"'

WHEN @typename in ("smalldatetime")
THEN '"' + convert(varchar(255),convert(smalldatetime,@value_ raw),100) + '"'

ELSE @value_raw
END

if (@value_raw is null)
select @freq_cell =1 , @prev_step = @step, @prev_weight = @weight, @value_c = "null"
else
begin
select @value_c = ltrim(@value_c)
if (@freq_cell = 1)
begin /* Printing a frequency cell */
if (@typename in ("binary","varbinary","timestamp"))
begin
print '%1! %2! < %3!',@prev_step,@prev_weight,@value_raw
print '%1! %2! = %3!',@step,@weight,@value_raw
end
else
begin
print '%1! %2! < %3!',@prev_step,@prev_weight,@value_c
print '%1! %2! = %3!',@step,@weight,@value_c
end
end
else /* NOT printing a frequency cell */
begin
if (@typename in ("binary","varbinary","timestamp"))
print '%1! %2! <= %3!',@step,@weight,@value_raw
else
print '%1! %2! <= %3!',@step,@weight,@value_c
end
select @freq_cell = 0
end

fetch histogram_cursor into
@step, @weight, @value_raw
end
close histogram_cursor
/* Is there only one cell (a freqency cell) */
if (@freq_cell = 1)
print '%1! %2! = %3!',@prev_step,@prev_weight,@value_c
print ''
end /* histogram print */

fetch col_cursor into
@colid, @colidarray, @colidarray_len, @colname, @statid, @c1stat, @last_updt, @rc_density, @tot_density
,@steps_act, @steps_req, @typename, @collength, @precision, @scale, @r_sel, @between_sel
end
close col_cursor
-----------------------
-- Done with columns --
-----------------------

------------------------------
-- print cols with no stats --
------------------------------
select @keycnt = 0
open nostats_cursor
fetch nostats_cursor into @colname
while (@@sqlstatus = 0)
begin
select @keycnt = @keycnt + 1
if (@keycnt = 1)
print 'No statistics for remaining columns: "%1!"',@colname
else if (@keycnt = 2)
print '(default values used) "%1!"',@colname
else
print ' "%1!"',@colname
fetch nostats_cursor into @colname
end
close nostats_cursor
if (@keycnt = 1)
print '(default values used)'

print ''

fetch object_cursor into
@tabid, @u_dbname, @u_dbid,
@u_tabowner, @u_tabname
end
close object_cursor
-----------------------
-- Done with Objects --
-----------------------
end

go

grant execute on sp__optdiag to public
go
use sybsystemprocs
go
drop procedure sp__rev_configure
go
create procedure sp__rev_configure
as
declare @sptlang int /* current sessions language */
declare @whichone int /* using english or default lang ? */

if @@trancount = 0
begin
set transaction isolation level 1
set chained off
end

select @whichone = 0

select @sptlang = @@langid

if @@langid != 0
begin
if not exists (
select * from master.dbo.sysmessages where error
between 17015 and 17049
and langid = @@langid)
select @sptlang = 0
else
if not exists (
select * from master.dbo.sysmessages where error
between 17100 and 17109
and langid = @@langid)
select @sptlang = 0
end

if @sptlang = 0
begin
select "-- sp_configure settings"
= "sp_configure '" + name + "', "
+ convert( char(12), c.value)
+ char(13) + char(10) + "go"
from master.dbo.spt_values a,
master.dbo.syscurconfigs c
where a.type = "C"
and a.number *= c.config
and a.number >= 0
end
else
begin
select "-- sp_configure settings"
= "sp_configure '" + name + "', "
+ convert(char(12), c.value)
+ char(13) + char(10) + "go"
from master.dbo.spt_values a,
master.dbo.syscurconfigs c,
master.dbo.sysmessages d
where type = "C"
and a.number *= c.config
and a.number >= 0
and msgnum = error and isnull(langid, 0) = @sptlang
end
return (0)
go
--
-- You may or may not wish to do the following.
--
--grant execute on sp__rev_configure to public
--gouse sybsystemprocs
go

/*
* DROP PROC sp__revroles
*/
IF OBJECT_ID('sp__revroles') IS NOT NULL
BEGIN
DROP PROC sp__revroles
PRINT '<<< Dropped proc sp__revroles >>>'
END
go
create procedure sp__revroles
as
/* Created 03/05/97 by Clayton Groom
creates a reverse engineered set of commands to restore user roles
*/
select "exec sp_role grant, " + u.name + ", " + s.name + char(13) + char(10) + "go"
from master..syssrvroles s,
sysroles r,
sysusers u
where r.id = s.srid
and r.lrid = u.uid
and s.name <> u.name
go

IF OBJECT_ID('sp__revroles') IS NOT NULL
PRINT '<<< Created proc sp__revroles >>>'
ELSE
PRINT '<<< Failed to create proc sp__revroles >>>'
go
use sybsystemprocs
go

if object_id('sp_days') is not NULL
drop proc sp_days
go

create proc sp_days @days tinyint OUTPUT, @month tinyint, @year smallint
as
declare @date datetime
select @date=convert(char,@month)+'/01/'+convert(char, @year)
select @days=datediff(dd,@date, dateadd(mm,1,@date))
select @days
go

grant exec on sp_days to public
gouse sybsystemprocs
go

if object_id('dbo.sp_ddl_create_table') is not null
drop procedure sp_ddl_create_table
print "Dropping sp_ddl_create_table"
go

create proc sp_ddl_create_table
as

-- Creates the DDL for all the user tables in the
-- current database

select right('create table ' + so1.name + '(' + '
', 255 * ( abs( sign(sc1.colid - 1) - 1 ) ) )+
sc1.name + ' ' +
st1.name + ' ' +
substring( '(' + rtrim( convert( char, sc1.length ) ) + ') ', 1,
patindex('%char', st1.name ) * 10 ) +
substring( '(' + rtrim( convert( char, sc1.prec ) ) + ', ' + rtrim(
convert( char, sc1.scale ) ) + ') ' , 1, patindex('numeric', st1.name ) * 10 ) +
substring( 'NOT NULL', ( convert( int, convert( bit,( sc1.status & 8 ) ) ) * 4 ) + 1,
8 * abs(convert(bit, (sc1.status & 0x80)) - 1 ) ) +
right('identity ', 9 * convert(bit, (sc1.status & 0x80)) ) +
right(',', 5 * ( convert(int,sc2.colid) - convert(int,sc1.colid) ) ) +
right(' )
' + 'go' + '
' + '
', 255 * abs( sign( ( convert(int,sc2.colid) - convert(int,sc1.colid) ) ) -
1 ) )
from sysobjects so1,
syscolumns sc1,
syscolumns sc2,
systypes st1
where so1.type = 'U'
and sc1.id = so1.id
and st1.usertype = sc1.usertype
and sc2.id = sc1.id
and sc2.colid = (select max(colid)
from syscolumns
where id = sc1.id)
order by so1.name, sc1.colid
go

if object_id('dbo.sp_ddl_create_table') is not null
begin
grant execute on sp_ddl_create_table to public
print "Created sp_ddl_create_table"
end
else
print "Failed to create sp_ddl_create_table"
go

goIF OBJECT_ID('sp_desc') IS NOT NULL
BEGIN
DROP PROCEDURE sp_desc
IF OBJECT_ID('sp_desc') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE sp_desc >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE sp_desc >>>'
END
go

create procedure sp_desc @table_name char(30) = NULL
--
-- Snarfed from CDS, cannot remember who posted the original.
-- Update for dec and numeric data types, plus ensured that
-- varchars came out as that.
--
-- David Owen 2001 (dowen@midsomer.org)

as
-- This stored procedure returns a description of a SQL Server table in
-- a format more like the Oracle DESC command.

if (@table_name IS NULL)
begin
raiserror 20001 "Must specify table name for sp_desc!"
return
end

declare @min_id int

select
C.colid 'column_id',
C.name 'column_name',
T.name 'column_type',
T.usertype 'user_type',
T.type 'base_type',
C.length 'column_length',
C.scale 'column_scale',
C.status 'column_is_null'
into
#tab_descr
from
syscolumns C,
sysobjects O,
systypes T
where
C.id = O.id
and C.usertype = T.usertype
and O.name = @table_name

if (@@rowcount = 0)
begin
raiserror 20001 "Table specified does not exist"
return
end

update
#tab_descr
set
user_type = systypes.usertype
from
systypes
where
systypes.type = #tab_descr.base_type
and systypes.usertype < 100

-- update
-- #tab_descr
-- set
-- column_type = name
-- from
-- systypes
-- where
-- #tab_descr.user_type = systypes.usertype

update
#tab_descr
set
column_type = name
from
systypes st,
#tab_descr td
where td.base_type = st.type
and td.user_type > 100

update
#tab_descr
set
column_type = column_type + "(" + LTRIM(RTRIM(str(column_length)))+")"
where
column_type in ("char", "varchar", "nchar", "nvarchar", "binary", "varbinary")

update
#tab_descr
set
column_type = column_type + "(" +
LTRIM(RTRIM(str(column_length))) +
"," +
LTRIM(RTRIM(str(column_scale))) +
")"
where
column_type in ("dec", "numeric", "decimal")

-- update
-- #tab_descr
-- set
-- column_type = "varchar("+LTRIM(RTRIM(str(column_length)))+")"
-- where
-- column_type = "sysname"

select
@min_id = min(column_id)
from
#tab_descr

update
#tab_descr
set
column_id = column_id - @min_id + 1

print @table_name

select
convert(char(5), "("+LTRIM(str(column_id))+")") 'No.',
column_name 'Column Name',
convert(char(20), column_type) 'Datatype',
case column_is_null
when 0 then "NOT NULL"
else ""
end
from
#tab_descr
order by column_id
go

IF OBJECT_ID('dbo.sp_desc') IS NOT NULL
BEGIN
PRINT '<<< CREATED PROCEDURE dbo.sp_desc >>>'
GRANT EXECUTE ON dbo.sp_desc TO public
END
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_desc >>>'
go
use sybsystemprocs
go
/*
* DROP PROC dbo.sp_devusage
*/
IF OBJECT_ID('dbo.sp_devusage') IS NOT NULL
BEGIN
DROP PROC dbo.sp_devusage
PRINT '<<< DROPPED PROC dbo.sp_devusage >>>'
END
go
CREATE PROCEDURE sp_devusage (@device_name char(30) = NULL)
AS
IF @device_name != NULL
BEGIN
SELECT dev_name = substring(dv.name,1,20),db_name = substring(db.name,1,20),
size_mb = u.size/512.0,
u.segmap,
vdevno = u.vstart/power(2,24)
FROM master..sysusages u , master..sysdevices dv,
master..sysdatabases db
WHERE u.vstart between dv.low and dv.high
AND db.dbid = u.dbid
AND cntrltype = 0
AND dv.name = @device_name
ORDER BY dv.name
COMPUTE sum(u.size/512.0) by dv.name
END
ELSE
BEGIN
SELECT dev_name = substring(dv.name,1,20),db_name = substring(db.name,1,20),
size_mb = u.size/512.0, u.segmap,
vdevno = u.vstart/power(2,24)
FROM master..sysusages u , master..sysdevices dv,
master..sysdatabases db
WHERE u.vstart between dv.low and dv.high
AND db.dbid = u.dbid
AND cntrltype = 0
ORDER BY dv.name
COMPUTE sum(u.size/512.0) by dv.name
END
go

IF OBJECT_ID('dbo.sp_devusage') IS NOT NULL
PRINT '<<< CREATED PROC dbo.sp_devusage >>>'
ELSE
PRINT '<<< FAILED CREATING PROC dbo.sp_devusage >>>'
go
/*
* Granting/Revoking Permissions on dbo.sp_devusage
*/
GRANT EXECUTE ON dbo.sp_devusage TO public
go

/*>>>>>>>>>>>>>>>>>>>>>>>>>>> sp_dos <<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
IF OBJECT_ID('dbo.sp_dos') IS NOT NULL
DROP PROCEDURE sp_dos
go

CREATE PROCEDURE sp_dos
@vcObjectName varchar(30) = NULL
AS
/************************************************** *********************
* sp_dos - Display Object Scope
* This procedure graphically displays the scope of a object in
* the database.
*
* Copyright 1996, all rights reserved.
*
* Author: David W. Pledger, Strategic Data Systems, Inc.
*
* Parameters
* ----------------------------------------------------------------
* Name In/Out Description
* ----------------------------------------------------------------
* @vcObjectName In Mandatory - The exact name of a single
* database object for which the call
* hierarchy is to be extracted.
*
* Selected Data
* A sample report follows:
* ----------------------------------------------------------------
*
* SCOPE OF EFFECT FOR OBJECT: ti_users
* +------------------------------------------------------------------+
* (T) ti_users (Trigger on table 'users')
* |
* +--(P) pUT_GetError
* | |
* | +--(U) ui_error
* |
* +--(U) BGRP
* |
* +--(U) user_information (See Triggers: tu_user_information)
* |
* +--(U) users (See Triggers: ti_users, tu_users, td_users)
* |
* +--(P) pUT_LUDVersion
* |
* +--(P) pUT_GetError
* | |
* | +--(U) ui_error
* |
* +--(U) BGRP_LUDVersion
*
* <End of Sample>
*
* Return Values
* ----------------------------------------------------------------
* Value Description
* ----------------------------------------------------------------
* < -99 Unexpected error - should never occur.
*
* -99 to -1 Sybase **reserved** return status values.
*
* 0 Execution succeeded
*
* 1 Execution of this procedure failed.
*
* > 1 Unexpected error - should never occur.
*
************************************************** *********************/
BEGIN

/*------------------- Local Declarations -------------------------*/
DECLARE @iObjectID int /* System ID of object */
DECLARE @cObjectType char(1) /* System Object Type code */
DECLARE @vcName varchar(30) /* System Object name */
DECLARE @vcMsg varchar(255) /* Error Message