defaults to the DB with the log full else to the first DB with a blocking lock on else to the DB with the largest data percentage full.
SEE ALSO
SAMPLE OUTPUT
----------------------------------------------
You are 'SYS_INSTALL' as 'dbo' under SPID 10
on with role(s): sa, sso, oper
-DATABASE: Monday Oct 11 1999 11:18AM
========
Database: xrm_db dbid:5
DB owner: xrm_mgr suid:6
Creation: Apr 1 1999 9:42AM MB: 52
-PLACEMENT:
=========
id Database id Database
-- ------------------- -- -------------------
4 sybsystemprocs
5 xrm_db <<<<<<<<<<<<
6 xrm_sod01_db
7 xrm_security_db
8 sim_db
-OPTIONS:
=======
- Select into/bulkcopy/pllsort
- Trunc log on chkpt
- Abort tran on log full
-IN USE BY:
=========
All DBs # # # # # # # # # # # # # # # # # #
This DB xrm_db is NOT in use
-ROLES:
=====
sa sso oper replication
---------- ---------- ---------- ------------
sa
SYS_OPER
SYS_INSTAL
SCRIPT_SVR
-SERVER:
======
Running ####################################
Idle ##################################
SQL #############
I/O ####################################
Received ####################################
Sent ###############################
Errors: 2
Read #######
Write ####################################
Errors: 0
-ENGINES:
=======
Nr Status # Pr Online since
0 online 0 Oct 6 1999 11:48AM
-DATA:
====
Size MB Used MB Full % Free MB
40 30.8 77.1 9.2
-LOG:
===
Size MB Used MB Full % Free MB
12 0.1 0.8 11.9
-ALLOCATED:
=========
Device Usage Size MB
datadev1 - Data - 40
logdev1 - Log - 10
logdev1 - Log - 2
-AVAILABLE: (max used Virtual Device Nr = 3 )
=========
Device VDN Total MB Free MB
datadev1 2 28
logdev1 3 13
master 0 1
sysprocsdev 1 0
+ ------ + -----
387 42
----------------------------------------------
see also 'sp__dbuse usage'
sp__depends
It's a superset of sp_depends.
|
AUTHOR
Q Vincent Yin (umyin@mctrf.mb.ca), Sep 1995
DESCRIPTION
This proc can handle usertypes, defaults and rules that are not covered by the original proc sp_depends. For tables, procs, etc, that are covered by sp_depends, this proc will simply call sp_depends. It prints usage and quits if invoked without arguments. Otherwise:
For each line printed by this proc:
If @format=null, output is in tabular format similar to sp_depends.
If @format='drop', output is in isql format.
For example,
exec sp__depends 'my_rule', 'drop'
will print (not execute) isql scripts that would unbind my_rule from all attached columns and usertypes, and then drop my_rule. By running the generated isql script, you won't encounter this frustrating error:
Msg 3716, Level 16, State 1:
The rule 'my_rule' cannot be dropped because it is
bound to one or more column.
BUGS
@format='drop' doesn't guarentee the successful dropping of usertype because the usertype may have been used by some tables and procs.
I didn't pay much attention to the owners of objects since all objects at our site are owned by dbo.
USAGE
Proc_name Order Parameter
------------------------- ----- ------------------------------
sp__depends 1 @objname varchar(30) NOT NULL
sp__depends 2 @format varchar(30) NOT NULL
sp__depends 3 @dont_format char(1) NOT NULL
SAMPLE OUTPUT
object dependant
------------------------------ ------------------------------
sp_procxmode sysobjects
sp_validlang syslanguages
sp_getmessage sysusermessages
sp_getmessage sysmessages
sp_getmessage syslanguages
sp_getmessage sp_validlang
sp_configure sysattributes
sp_configure sysdevices
sp_configure sysconfigures
sp_configure syscurconfigs
sp__diskdevice
List disk devices and their basic information
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Basic information about disk devices
USAGE
sp__diskdevice [@devname]
@devname: device name you are interested in - defaults to printing all dump devices
SEE ALSO
sp__helpdevice, sp__dumpdevice
SAMPLE OUTPUT
1> exec sp__diskdevice
****** PHYSICAL DISK DEVICES (Mirror info after device name) ******
Device Name Physical Name size alloc free
------------- ------------------------------- ------ -------- --------
datadev /disk1/sybase10/datadev.dat 20.0MB 20.0MB 0.0MB
datadev2 /disk1/sybase10/datadev2.dat 19.5MB 17.0MB 2.5MB
datadev3 /disk1/sybase10/datadev3.dat 10.0MB 4.0MB 6.0MB
master d_master 17.0MB 16.5MB 0.5MB
sybsecurity /disk1/sybase10/sybsecurity.da 5.0MB 5.0MB 0.0MB
sysprocsdev /disk1/sybase10/sysprocsdev.da 10.0MB 10.0MB 0.0MB
sp__dumpdevice
List dump devices and their basic information
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Basic information about dump devices
USAGE
sp__dumpdevice [@devname]
@devname: device name you are inetested in - defaults to printing all dump devices
SEE ALSO
sp__helpdevice, sp__diskdevice
SAMPLE OUTPUT
1> sp__dumpdevice
1> exec sp__dumpdevice
Device Name Physical Name
-------------------- --------------------------------------------------
tapedump1 /dev/rmt4
tapedump2 /dev/rst0
sp__find_missing_index
Lists potentially missing indexes
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Lists potentially missing indexes. Assumes that your foreign key relationships are potential joins and compares keys with actual indexes, listing any keys that do not have associated indexes. This proc will list a key relationship based on fields a,b,c & d if there were no index on a, a&b, a&b&c and so on, assuming that the dba has done his work and would not have an index that was not sufficiently selective (ie. if you decide a is good enough for an index your relationship a,b,c,d should use it in its joins). If this proc does not find the missing index, your model is not sufficiently defined.
USAGE
sp__find_missing_index [ @objname ] [ @p1]
@objectname gives you missing indexes for that object
@p1 if passed will give full output suitable for programs, but too long for humans to read.
SAMPLE OUTPUT
1> exec sp__find_missing_index
No Indexes Found in Current Database
sp__flowchart
List execution flow of procedures
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
List flow of procedures in current db. Goes 6 levels. Does this from sysdepends. See also sp__read_write for another good optimization tool. I think the output is readable but...
USAGE
sp__flowchart [ @objname ] [ @p1]
@objectname gives you flow only from 1 object
@p1 if passed will give full 30 character by 6 level output (otherwise the output is
parsed to 15 characters wide strings x 4 levels to fit on the screen).
SAMPLE OUTPUT
1> exec sp__flowchart
level 1 level 2 level 3 level 4
------------------ ------------------ ------------------ -----------
ap_insert_server ap_insert_system
sp__grep
grep for Sybase SQL Server System 10
|
AUTHOR
Andrew Zanevsky, AZ Databases, Inc.
DESCRIPTION: Searches syscomments table in the current database for occurences of a combination of strings. Correctly handles cases when a substring begins in one row of syscomments and continues in the next.
USAGE
sp__grep @parameter [,@case]
@parameter describes the search:
string1 {operation1 string2} {operation2 string 3} ... where - stringN is a string of characters enclosed in curly brackets not longer than 80 characters. Brackets may be omitted if stringN does not
contain spaces or characters: +,-,&; operationN is one of the characters: +,-,&.
Parameter is interpreted as follows:
1.Combine the list of all objects where string1 occurs.
2.If there is no more operations in the parameter, then display the list and stop. Otherwise continue.
3.If the next operation is + then add to the list all objects where the next string occurs; else if the next operation is - then delete from the list all objects where the next string occurs; else if the next operation is & then delete from the list all objects where the next string does not occur (leave in the list only those objects where the next string occurs);
4.Goto step 2.
Parameter may be up to 255 characters long & may not contain <Line Feed> characters. Please note that operations are applied in the order they are used in the parameter string (left to right). There is no other priority of executing them. Every operation is applied to the list combined as a result of all previous operations.
Number of spaces between words of a string matters in a search (e.g. "select *" is not equal to "select *"). Short or frequently used strings (such as "select") may produce a long result set.
- @case: i = insensitive / s = sensitive (default).
SAMPLE OUTPUT
list all objects where string 'employee' occurs;
sp__grep employee
list all objects where string 'employee' occurs in any case (upper, lower, or mixed), such as 'EMPLOYEE', 'Employee', 'employee', etc.;
sp__grep employee, i
list all objects where either both strings 'employee' and 'salary' occur or string 'department' occurs, and string 'trigger' does not occur;
sp__grep 'employee&salary+department-trigger'
list all objects where string "select FirstName + LastName" occurs;
sp__grep '{select FirstName + LastName}'
sp__groupprotect
Synopsis of protection stuff.
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Gives number of select / update /delete /insert / revoke / and execute grants for each group and type of object. Useful to summarize what groups have priviliges to do what.
SEE ALSO
sp__helprotect, sp__objprotect
SAMPLE OUTPUT
1> sp__groupprotect
2> go
type grp tot sel upd del ins rev exe
---- --------------- ---- ------ ---- ---- ---- ---- ----
P g_mon6 27 0 0 0 0 0 0
P public 27 0 0 0 0 0 9
R g_mon6 6 0 0 0 0 0 0
R g_monitor 6 0 0 0 0 0 0
S g_mon6 57 0 0 0 0 0 0
S g_monitor 57 0 0 0 0 0 0
S public 57 16 0 0 0 0 0
U g_mon6 33 0 0 0 0 0 0
U g_monitor 33 0 0 0 0 0 0
U public 33 11 0 0 0 0 0
V g_mon6 3 0 0 0 0 0 0
V g_monitor 3 0 0 0 0 0 0
V public 3 0 0 0 0 0 0
sp__help
Modified sp_help
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Pretty version of sp_help. sp_help scrolls off screen and is ugly. List objects in current database (if @objname undefined) or list table columns (if @objname defined). The proc is slow....
USAGE
sp_help [@objname]
if @objname is defined, will list information about specific object if that object exists. If the object doesnt exist, it will try to print all objects that contain the string fragment @object. EXAMPLE
sp__help "pmt_" Info about all objects with "pmt_" in the name
sp__help server Info about table server (with column listing)
SEE ALSO
sp__help calls the procedures sp__helpcolumn and sp__helpindex when an object is passed as a parameter.
SAMPLE OUTPUT
1> exec sp__help
Name Owner Object_type
-------------------- -------------------- -----------------
alerts dbo user table
audit_trail dbo user table
comn_database dbo user table
comn_dumpdevices dbo user table
comn_syscolumns dbo user table
comn_sysdevices dbo user table
comn_sysindexes dbo user table
comn_syslocks dbo user table
1> sp__help authors
Name Owner Object_type
-------------------- -------------------- ----------------
authors dbo user table
table name insert trigger update trigger delete trigger
------------- --------------- --------------- ---------------
authors authors_ins ........... ............
Column_name Type Nulls Default_name Rule_name
------------- --------------- ----- --------------- --------
au_id id 0
au_lname varchar(40) 0
au_fname varchar(40) 0
phone char(12) 0 phonedflt
address varchar(40) 1
INDEX KEY c = clustered u = unique
a = allow dup row s = suspect
Table Name Index Name c u a s List of Index Keys
-------------------- ----------- - - - - ------------------
authors auidind Y Y au_id
sp__helpcolumn
List columns for table / database
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
List columns for given table
USAGE
sp__helpcolumn @objname , @p1
@objname can be any valid table or view. If null returns all columns.
@p1 if set will not reformat columns
note the columns are sorted by column id if an object is selected and by column name if not.
SEE ALSO
sp__collist
SAMPLE OUTPUT
1> sp__helpcolumn server
1> exec sp__helpcolumn
Column name Type I Null Dflt Rule Table
----------------- ------------ --- ---- ---- ---- --------------------
action char(18) 0 Yes audit_trail
allow_null tinyint 0 Yes comn_syscolumns
allow_updates smallint 0 Yes server_configures
attribute char(30) 0 Yes schedule_history
attribute varchar(127) 0 Yes schedule_attributes
audit_trail tinyint 0 No personal_preferences
benchmark int 0 Yes server_statistics
benchmark int 0 Yes summary_statistics
...
sp__helpdb
shows database information in a nice format
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Show standard database information in a concise format
SEE ALSO
The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdb, sp__helpdbdev, sp__helpdevice
USAGE
sp_helpdb shows information about all databases
sp__helpdb [ @dbname ] prints specific information about given database.
SAMPLE OUTPUT
1> sp__helpdb statsdb
name size_data size_log owner
---------- ---------- -------- ----------
statsdb 7.000000 2.000000 sa
Database Name Device Name Size Usage
--------------- --------------- --------------------
statsdb data3 2.000000 data only
statsdb datadevice 2.000000 data only
statsdb datadevice 3.000000 data only
statsdb log 2.000000 log only
1> sp__helpdb
1> exec sp__helpdb
key description key description
--- ----------- --- -----------
si select into/bulkcopy ro read only
tl trunc. log on chkpt do dbo use only
cr no chkpt on recovery su single user
cl crashed during load ab abort tran
ds database suspect
****** DATABASE CONFIGURATION *******
database data log owner si tl cr cl ds ro do su ab
------------------ ---- ----- ------ -- -- -- -- -- -- -- -- --
master (1) 5 N/A sa
mis (7) 15 6 sa
model (3) 2 N/A sa
pubs2 (8) 4 2 sa
statsdb (6) 10 N/A sa Y
sybsecurity (5) 5 N/A sa Y
sybsystemprocs (4) 14 3 sa Y Y
tempdb (2) 7 N/A sa Y Y
total space used total data total log
---------------- ---------- ----------
73.00 62.00 11.00
sp__helpdbdev
Show how database uses devices
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Show device to database breakdown. Which devices are used by database.
SEE ALSO
The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdb, sp__helpdevice, sp__helpdbdev.
USAGE
sp__helpdbdev [ @dbname ]
if @dbname parameter is passed, only show information for given database
SAMPLE OUTPUT
1> sp__helpdbdev
Database Name Device Name Size Usage
--------------- --------------- -------------------- ---------------
master master 2.000000 data and log
master master 2.000000 data and log
master master 3.000000 data and log
migrator datadevice 10.000000 data and log
model master 2.000000 data and log
pubs2 master 2.000000 data and log
tempdb master 2.000000 data and log
1> exec sp__helpdbdev
Database Name Device Name Size Usage
--------------- --------------- -------------------- ---------------
master master 1.500000 data and log
master master 3.000000 data and log
mis datadev2 5.000000 data only
sp__helpdefault
list default information in current databases
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
list defaults information in current database
USAGE
sp__helpdefault [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
SEE ALSO
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
BUGS
These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.
SAMPLE OUTPUT
1> sp__helpdefault
Default Name Times Used Definition
-------------------- ---------- -------------------------------------
sp__helpdevice
Break down database devices into a nice report
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Just the info you need, in the format you want. Split between dump and disk devices. Can also be used to show which databases use a given device.
SEE ALSO
The following procedures are especially useful while creating new databases and attempting to perform optimal allocation of space: sp__helpdevice, sp__helpdbdev, sp__helpdb.
USAGE
sp__helpdevice summary information on all devices sp__helpdevice @devicename details for particular device
SAMPLE OUTPUT
1> exec sp__helpdevice
Device Name Physical Name
-------------------- ---------------------------------------
tapedump1 /dev/rmt4
tapedump2 /dev/rst0
Device Name Physical Name size alloc free
-------------- ------------------------------- -------- ------- ------
datadev /disk1/sybase10/datadev.dat 20.0MB 20.0MB 0.0MB
datadev2 /disk1/sybase10/datadev2.dat 19.5MB 17.0MB 2.5MB
datadev3 /disk1/sybase10/datadev3.dat 10.0MB 4.0MB 6.0MB
master d_master 17.0MB 16.5MB 0.5MB
sybsecurity /disk1/sybase10/sybsecurity.da 5.0MB 5.0MB 0.0MB
sysprocsdev /disk1/sybase10/sysprocsdev.da 10.0MB 10.0MB 0.0MB
1> sp__helpdevice datadevice
Device Name Physical Name size alloc free
-------------- --------------------------- ------- ------- --------
datadevice /disk1/sybase10/datadev.dat 20.0MB 20.0MB 0.0MB
db_name size usage
------------------------------ -------------------- --------------
migrator 10.000000 data and log
statsdb 2.000000 data only
sp__helpgroup
Lists groups members in current database (incl. aliases)
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Lists users AND aliases in current database. The alias feature is one that is NOT in sp_helpuser. This is a quick method of seeing who has access to particular data. Groups with no users are also shown.
USAGE
sp__helpgroup [ @groupname ]
if @groupname provided, it will only provide information about that specific group.
SEE ALSO
sp__helplogin, sp__helpuser
SAMPLE OUTPUT
1> exec sp__helpgroup
Group_name Login_name Alias User_name Default_db
-------------- -------------- ----- -------------- --------------
g_mon6 mon6 mon6 master
g_monitor monitor monitor master
navigator_role N.A. N N.A. N.A.
oper_role N.A. N N.A. N.A.
public sa dbo master
public statsdbo Y dbo statsdb
public ebarlow ebarlow statsdb
replication_ro N.A. N N.A. N.A.
sp__helpindex
Shows indexes by table
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
show index information in current database
USAGE
sp__helpindex [ @objectname, ] [ @width ]
if @objectname is defined, will print index information just for current object. Parameter @width allows printing in modes greater than 80 columns. It may sound weird, but if @objectname is a procedure, it will give any indexes that are used by tables dependant on the procedure (useful for tuning).
SAMPLE OUTPUT
1> sp__helpindex
INDEX KEY: c = clustered u = unique
a = allow dup row s = suspect
Table Name Index Name c u a s List of Keys
-------------------- ----------- - - - - ---------------------
alerts XPKalerts Y Y hostname,srvname
applications XPKAccounti Y Y dbname
audit_trail XPKaudit_tr Y Y date,login_name
comn_database XPKdatabase Y Y srvname,dbname
comn_dumpdevices XPKcomn_dum Y Y srvname,devname
comn_syssegments XPKcomn_sys Y Y seg_id
comn_sysusages XPKcomn_sys Y Y srvname,devname,dbname
1> exec sp__helpindex
INDEX KEY: c = clustered u = unique
a = allow dup row s = suspect
Table Name Index Name c u a s List of Index Keys
----------------- ----------- - - - - -----------------------
alerts XPKalerts Y Y hostname,srvname
audit_trail XPKaudit_tr Y Y crdate,login_name
comn_database XPKdatabase Y Y srvname,dbname
comn_dumpdevices XPKcomn_dum Y Y srvname,devname
comn_syscolumns XPKcomn_sys Y Y srvname,dbname,object_id
sp__helplogin
Show logins and remote logins to the server
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Shows both login and remote login information Note: if Running as sa on pre 4.9 releases, the password field will show. If running system 10, roles for logins are shown.
USAGE
sp__helplogin [ @loginname ]
SEE ALSO
sp__helpgroup, sp__helpuser
SAMPLE OUTPUT
(4.9 Version)
1> sp__helplogin
****** SERVER LOGINS *******
Login_name Default_db RemoteName RemoteSrvr
-------------- -------------- -------------- --------------
cosmic migrator
ebarlow pubs2
probe master
qqq pubs2
sa master
1> exec sp__helplogin
****** SERVER LOGINS *******
Id Login_name Default_db Sht Lck Exp SA SSO Oper Remote
---- -------------- -------------- --- --- --- -- --- ---- ------
12 a master
4 ebarlow statsdb
6 george statsdb
7 lperry sybsystemprocs
11 mis mis
13 misread mis
10 mon6 master
9 monitor master
2 probe master
1 sa master Y Y Y
3 statsdbo statsdb
sp__helpmirror
Show mirrors, discover broken mirrors
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
show mirror information in 3 batches: mirrored devices, disabled mirrors, and confused mirrors. The "Pri" & "Sec" fields mean the primary and mirror are active if starred. The Serial field contains a star if Serial writes - none if Parallel.
USAGE
sp__helpmirror
SAMPLE OUTPUT
1> sp__helpmirror
******* SYBASE MIRROR INFORMATION *******
MIRRORED DEVICES
Device Pri Sec Serial Mirror Reads
--------------- --- --- ------ -------------------------------- -----
data2 * * * /home/programs/sybase/datax *
1> sp__helpmirror
******* SYBASE MIRROR INFORMATION *******
sp__helpobject
list objects in current databases
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
list tables, views, procedures, rules, defaults, and triggers in current database
USAGE
sp__helpobject [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
SEE ALSO
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
BUGS
These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.
SAMPLE OUTPUT
1> exec sp__helpobject
Table Name Rows Res KB Usd KB Rows/KB Segment Cr Date
------------------ ------ ------ ------ ------- --------- -------
alerts 420 176 146 2.87 default 24Jan96
audit_trail 4 62 6 0.66 default 24Jan96
comn_database 4 32 6 0.66 default 24Jan96
comn_dumpdevices 3 32 6 0.50 default 24Jan96
comn_syscolumns 593 160 138 4.29 default 24Jan96
comn_sysdevices 2 32 6 0.33 default 24Jan96
comn_sysindexes 70 32 16 4.37 default 24Jan96
comn_syslocks 0 16 2 0.00 default 24Jan96
View Name Cr Date Tables Used
-------------------- ---------- -------------------------------
titleview 02/22/95 authors, titles, titleauthor
Procedure_name Owner Created_date
------------------------------ --------------- ------------
ap_audit_report dbo 24Jan96
ap_audit_start dbo 24Jan96
ap_audit_status dbo 24Jan96
Rule Name Times Usd Definition
------------- --------- ------------------------------------------
pub_idrule 1 @pub_id in ("1389", "0736", "0877", "1622"
title_idrule 2 @title_id like "[0-9][0-9][0-9][0-9]"
Default Name Times Used Definition
-------------------- ---------- ---------------------------------
datedflt 1 getdate()
phonedflt 1 "unknown"
typedflt 1 "undecided"
Trigger Name Cr Date Ins Cnt Del Cnt Upd Cnt
------------------------------ ------- ------- ------- -------
db_space_ins_trigger 24Jan96 1 1 1
lock_del_trigger 24Jan96 1 1 1
person_del_trigger 18Jan96 1 1 1
person_ins_trigger 18Jan96 1 1 1
scheduler_ins_trigger 24Jan96 1 1 1
server_del_trigger 24Jan96 1 1 1
sp__helpproc
list procedure information in current databases
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
list procedure information in current database
USAGE
sp__helpproc [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
SEE ALSO
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
BUGS
These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.
SAMPLE OUTPUT
1> exec sp__helpproc
Procedure_name Owner Created_date
------------------------------ --------------- ------------
ap_audit_report dbo 24Jan96
ap_audit_start dbo 24Jan96
ap_audit_status dbo 24Jan96
ap_config_info dbo 24Jan96
ap_debug_statsdb dbo 24Jan96
ap_diff_db dbo 24Jan96
ap_diff_srvr dbo 24Jan96
ap_get_disk_layout dbo 24Jan96
sp__helprotect
Protection Information for current database
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
list protection information for current database
USAGE
sp__helprotect [ @parameter = objectname | username | group ] [@do_system_tables] [@dont_format]
if @parameter is provided then procedure will attempt to print information about only one object (if it exists), group, or user. If no parameter is passed, all objects are displayed. The @do_system_tables parameter, if not null, will include system tables in the output.
SEE ALSO
sp__groupprotect, sp__objprotect
BUGS
helprotect should really be spelled helpprotect, but im sticking with the sybase naming convention.
SAMPLE OUTPUT
1> sp__helprotect
------------------------------
Grant Execute on ap_get_disk_layout to public
Grant Execute on ap_get_ind_to_rebuild to public
sp__helprule
list rule information in current databases
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
list rules information in current database
USAGE
sp__helprule [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
SEE ALSO
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
BUGS
These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.
SAMPLE OUTPUT
1> sp__helprule
Rule Name Times Used Definition
------------- ---------- ---------------------------------------------
pub_idrule 1 @pub_id in ("1389", "0736", "0877", "1622", "
title_idrule 2 @title_id like "bu[0-9][0-9][0-9][0-9]"
sp__helpsegment
Stored procedure to check segments on server
|
DESCRIPTION
shows segments information by database. Also shows sizes of segments.Used in conjunction with sp__segment procedure to decipher segment heirarchy.
USAGE
sp__helpsegment [ @segname ]
SAMPLE OUTPUT
1> exec sp__helpsegment
Segment Codes:
U=USER-defined segment on this device fragment
L=Database LOG may be placed on this device fragment
D=Database objects may be placed on this device fragment by DEFAULT
S=SYSTEM objects may be placed on this device fragment
******* SERVER SEGMENT MAP *******
db segmap segname segs device name size (MB)
---------- -------- --------------- ---- --------------- ---------
master 7 system LDS master 3.00
master 7 default LDS master 3.00
master 7 logsegment LDS master 3.00
master 7 system LDS master 1.50
master 7 default LDS master 1.50
master 7 logsegment LDS master 1.50
mis 4 logsegment L datadev 3.00
mis 4 logsegment L datadev 3.00
1> sp__helpsegment "datadevice"
Segment Codes: U=USER-defined segment on this device fragment L=Database LOG may be placed on this device fragment D=Database objects may be placed on this device fragment by DEFAULT S=SYSTEM objects may be placed on this device fragment
sp__helptable
list table information in current databases
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
list table information in current database
USAGE
sp__helptable [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
SEE ALSO
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
BUGS
These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.
SAMPLE OUTPUT
1> exec sp__helptable
Table Name Rows Res KB Usd KB Rows/KB Segment Cr Date
------------------ ------ ------ ------ ------- ---------- -------
alerts 420 176 146 2.87 default 24Jan96
audit_trail 4 62 6 0.66 default 24Jan96
comn_database 4 32 6 0.66 default 24Jan96
comn_dumpdevices 3 32 6 0.50 default 24Jan96
comn_syscolumns 593 160 138 4.29 default 24Jan96
comn_sysdevices 2 32 6 0.33 default 24Jan96
comn_sysindexes 70 32 16 4.37 default 24Jan96
comn_syslocks 0 16 2 0.00 default 24Jan96
sp__helptext
show helptext info with spacing correct
|
AUTHOR
Andrew Zanevsky, AZ Databases, Inc.
DESCRIPTION
list text of code. Uses print statement for technical reasons.
USAGE
sp__helptrigger
list trigger information in current databases
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
list trigger information in current database
USAGE
sp__helptrigger [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
SEE ALSO
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview. Another way to look at triggers is sp__trigger.
BUGS
These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.
SAMPLE OUTPUT
1> exec sp__helptrigger
Trigger Name Cr Date Ins Cnt Del Cnt Upd Cnt
------------------------------ ------- ------- ------- -------
db_space_ins_trigger 24Jan96 1 1 1
lock_del_trigger 24Jan96 1 1 1
person_del_trigger 18Jan96 1 1 1
person_ins_trigger 18Jan96 1 1 1
scheduler_ins_trigger 24Jan96 1 1 1
server_del_trigger 24Jan96 1 1 1
server_ins_trigger 24Jan96 1 1 1
server_upd_trigger 24Jan96 1 1 1
sp__helptype
get data types (user and system defined)
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
There is no easy way to look at user defined types using the current system procs, so here one is.
USAGE
Proc_name Order Parameter
------------------------------ ----- ------------------------------
sp__helptype 1 @dont_format char(1) NOT NULL
SAMPLE OUTPUT
TYPE DEFAULT RULE NULL BASE TYPE
--------------------- ------------- ------------- ---- -----------
binary NULL NULL 1 binary
tinyint NULL NULL 1 tinyint
smallint NULL NULL 1 smallint
int NULL NULL 1 int
float NULL NULL 1 float
numeric NULL NULL 1 numeric
money NULL NULL 1 money
datetime NULL NULL 1 datetime
intn NULL NULL 1 intn
sp__helpuser
Lists users in current database by access level (incl. aliases)
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Lists users AND aliases in current database. The alias feature is one that is NOT in sp_helpuser. This is a quick method of seeing who has access to particular data. Users whose logins are not in syslogins are listed with a login name of NULL.
SEE ALSO
sp__helplogin, sp__helpgroup
SAMPLE OUTPUT
1> exec sp__helpuser
Login_name User_name Alias Group_name Default_db
----------------- ----------------- ----- ----------------- -----------
sa dbo public master
statsdbo dbo Y public statsdb
ebarlow ebarlow public statsdb
mon6 mon6 g_mon6 master
monitor monitor g_monitor master
sp__helpview
list views in current databases
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
list views information in current database
USAGE
sp__helpview [ @objectname ]
if @objectname is provided then procedure will attempt to print information about only one object (if it exists). If not, it will print any objects with that string fragment in them. An error occurs if no objects with the string fragment exist. For example, if you wish to print any table with the name tbl in them use sp__helptable "tbl". If no parameter is passed, all object of type displayed.
SEE ALSO
sp__help, sp__helpdefault, sp__helpobject, sp__helpproc, sp__helprule, sp__helptable, sp__helptrigger, sp__helpview
BUGS
These procedures only read row one of syscomments to determine useful information regarding defaults, rules, and views. It is concievable (though unlikely) that a very long select statement could cause necessary data to be in row two, resulting in ugly output.
SAMPLE OUTPUT
1> sp__helpview
View Name Cr Date Tables Used
-------------------- ---------- --------------------------------------
sp__id
Tells you who you are and in which database
|
AUTHOR
Edward Barlow
DESCRIPTION:
Tells you who you are and in which database you are in
USAGE
sp__id
SAMPLE OUTPUT
1> exec sp__id
db login id db name
-------------------- -------------------- ---- --------------------
statsdb sa 1 dbo
sp__indexspace
Space breakdown by index in current database
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
sp__indexspace gives details of the space used by index in the database. It is a quick way of finding out information on in the database (did your load succeed...). Another way to look at this information is with the stored procedure sp__indexspace, which sumarizes on an index by index basis. Rows/KB is calculated only on the data plus index pages used. It ignores system overhead. If the Rows/KB field (actual rows in table) is much less than the Max/KB (maximum according to row size - found in rowpage field of sysindexes), the index may be very fragmented and could be in need of rebuilding (drop and recreate it). This is only relevant if there is a signifcant numbers of rows.
USAGE
sp__indexspace [ @table ]
SEE ALSO
sp__helptable
SAMPLE OUTPUT
1> exec sp__indexspace
Data Level (Index Type 0 or 1)
Name Rows Used/Data/Idx KB Rows/KB Segment
---------------------- -------- ---------------- ------- ------------
alerts.XPKalerts 420 176/142/4 2.88 default
audit_trail.XPKaudit_t 4 62/2/4 0.67 default
comn_database.XPKdatab 4 32/2/4 0.67 default
comn_dumpdevices.XPKco 3 32/2/4 0.50 default
comn_syscolumns.XPKcom 593 160/122/16 4.30 default
Non Clustered Indexes
Name Rows Used/Data/Idx KB Rows/KB Segment
---------------------- -------- ---------------- ------- ------------
pbcatcol.pbcatcol_idx 0 16/0/2 0.00 default
sp__iostat
Equivalent of the unix iostat command
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
SEE ALSO
sp__whoactive, sp__isactive, sp__iostat
DESCRIPTION
Unix iostat command equivalent. Does a loop on existing processes and prints only rows in sysprocesses that are doing anything (io or cpu > 0).
BUGS
Does not monitor logins started after process starts.
USAGE
sp__iostat { [ @count ] [, @delay ]
@count is number of iterations (default=3)
@delay - delay between stats rows in seconds. Valid values
are 1,3,5,10,30,60
SAMPLE OUTPUT
1> exec sp__iostat
-------- ---------
00:22:32 No Change
Time Spid Login Cmd Cpu Io Mem Blk
-------- ---- ---------- ---------------- ------ ---- ---- ---
00:22:42 6 NULL SITE HANDLER DEAD DEAD DEAD 0
-------- ---------
00:22:52 No Change
sp__isactive
Check who is doing something on server
|
AUTHOR
Androw Zanevsky, AZ Databases, Inc. 71232.3446@compuserve.com
SEE ALSO
sp__whoactive, sp__isactive, sp__iostat
DESCRIPTION
Monitors indicators of a given process for specified time and reports activity (CPU, IO, Locks...).
If any indicator changes, then process is active. This proc uses the waitfor command.
USAGE
sp__whoactive @spid [, @delay ]
@spid is the spid to watch
@delay is the monitoring interval in seconds (must be 5,10,20,or 60) - defaults to 5
SAMPLE OUTPUT
> sp__isactive 1
time locks cpu phys_io cmd status
-------- ------ ----------- --------- ------------ -------------
21:24:51 0 5 0 SELECT running
21:24:56 0 5 0 SELECT running
~+4 sec 0 0 0
Process shows no activity
sp__lock
Check locks on server
|
AUTHOR
Simon Walker, The SQL Workshop LTD., Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
pretty print locks
USAGE
sp__lock
SEE ALSO
sp__block
sp__lockt
SAMPLE OUTPUT
1> exec sp__lock
Type User Table Page Cmd
----------- ------------------ ---------------------- -------- ------
Sh_intent sa (pid=1) master..spt_values 0 SELECT
sp__lockt
Check table locks on server
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
pretty print table locks
USAGE
sp__lockt
SEE ALSO
sp__block sp__lock
SAMPLE OUTPUT
1> exec sp__lockt
spid dbname objname type
---- ---------------- ---------------- ----------------
1 master spt_values Sh_intent
BUGS
Only shows correct object name for objects in current database. If the
sp__ls
Equivalent of the unix ls command
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Unix ls equivalent. lists objects in db
USAGE
sp__ls [ @string ]
@ string: search string - pass in wildcards as you wish (ie. xxx% or ab%cd%). Does a normal Sybase like on the string passed.
SAMPLE OUTPUT
1> exec sp__ls
Object_name Type Owner Created_date
------------------------------ ---- --------------- --------------------
alerts U dbo Jan 24 1996 1:43PM
ap_audit_report P dbo Jan 24 1996 5:38PM
ap_audit_start P dbo Jan 24 1996 5:38PM
...
sp__noindex
Analysis of what tables lack indexes.
|
AUTHOR
Simon Walker, The SQL Workshop LTD.
DESCRIPTION
find tables with either no index or no clustered index. If the table has more than a small number of rows (5 data pages), you may be able to easily improve system performance by adding some.
USAGE
sp__noindex
SAMPLE OUTPUT
1> exec sp__noindex
No_Indexes Rows Pages
------------------------------ ----------- -----------
model 1 1
server_syslocks 0 1
system_stats_save 3610 145
summary_statistics_save 3022 160
table_space_save 1028 47
db_space_save 90 6
comn_syslocks 0 1
No_Clustered_Index Rows Pages
------------------------------ ----------- -----------
pbcattbl 0 1
pbcatcol 0 1
x 0 1
sp__objprotect
Synopsis of protection stuff.
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Gives number of select / update /delete /insert / revoke / and execute grants for each object. Can either be by user (parameter) or total.
SEE ALSO
sp__groupprotect, sp__helprotect
SAMPLE OUTPUT
1> sp__objprotect 'public'
name type sel upd del ins rev exe
------------------------ ---- ------ ------ ------ ------ ------ ------
datedflt D 0 0 0 0 0 0
phonedflt D 0 0 0 0 0 0
typedflt D 0 0 0 0 0 0
byroyalty P 0 0 0 0 0 1
discount_proc P 0 0 0 0 0 1
history_proc P 0 0 0 0 0 1
insert_sales_proc P 0 0 0 0 0 1
insert_salesdetail_proc P 0 0 0 0 0 1
storeid_proc P 0 0 0 0 0 1
storename_proc P 0 0 0 0 0 1
sp__proclib_version
Quick dump of statistics on server
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Returns Extended Stored Procedure Library version number
SAMPLE OUTPUT
1> exec sp__proclib_version
-----
3.80
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
sp__dbspace has had locking problems in busy environments when run programaticly. Use this procedure if you are running space monitoring in a loop.
SEE ALSO
sp__dbspace
USAGE
Proc_name Order Parameter
------------------------- ----- ------------------------------
sp__qspace 1 @dont_format char(1) NOT NULL
SAMPLE OUTPUT
Name Percent Log Pct
------------------------ --------------- ----------------
master 63.789063 0.000000
sp__quickstats
Quick dump of statistics on server
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Quick dump of server stats. Can be run in a tight loop to see how things happen on the server. Normally this program will get the current date from the server, and then run in a while loop running the sp__quickstats procedure (parameter of the previously retrieved date). The parameters mean the following: conn is number of non - sa connections. Time is the time taken to run this query. tbllocks are the number of table locks. runnable is the number of non sa processes performing work.
USAGE
sp__quickstats { @starttime }
if @starttime is not passed, will not print out a date field at the end.
SAMPLE OUTPUT
1> exec sp__quickstats
blks conn ctime locks run tlock %cpu %io %idle minutes
---- ---- ------ ----- ---- ----- ----- ----- ----- ----------
0 0 126 0 0 0 17.51 0.00 82.49 10.2
sp__read_write
Identifies which tables are read & which are written by procs
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
This nice proc identifies which tables are read and which are written by stored procedures in the database. Useful for tuning/optimization. See sp__flowchart. read_write is both read & write. The data used by ths proc is not accurate.
USAGE
sp__read_write
SAMPLE OUTPUT
1> exec sp__read_write
Tblname Sel Reads Writes R&W num_rows
------------------------------ ----- ----- ------ ----- -----------
alerts 0 2 2 2 420
audit_trail 0 0 4 0 4
comn_database 0 0 0 0 4
comn_syscolumns 0 0 0 0 593
comn_sysdevices 0 0 0 0 2
comn_sysindexes 0 0 0 0 70
comn_syslocks 0 0 0 0 0
comn_syslogins 0 0 0 0 6
comn_sysobjects 0 0 0 0 325
comn_sysusages 0 0 0 0 4
comn_sysusers 0 0 0 0 16
db_space 0 0 0 0 3
db_space_history 0 1 1 1 88
dflt_configures 0 1 1 1 4
sp__revalias
Reverse engineer aliases
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
reverse engineers aliases
USAGE
sp__revalias
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser
WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
1> sp__revalias
Text
-----------------------------------------------------------------------
exec sp_addalias 'probe','dbo'
exec sp_addalias 'ebarlow','dbo'
sp__revbindings
reverse engineer bindings in db
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
Reverse engineer bindings of user defined types to columns
SAMPLE OUTPUT
bindings
-----------------------------------------------------
exec sp_bindefault option_yn_def,'acc_control.modify'
exec sp_bindefault option_yn_def,'sod_control.modify'
exec sp_bindrule option_yn_rule,option_yn_type
exec sp_bindefault option_yn_def,option_yn_type
sp__revdb
Reverse engineer database layout of the server
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
reverse engineers layout of the databases on a server. Purposly excludes device "master", "model", and "tempdb".
USAGE
sp__revdb
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser
WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
1> sp__revdb
Create Database statsdb on 'data3'=2
,'datadevice'=2
,'datadevice'=3
log on 'log'=2
go
Create Database migrator on 'datadevice'=10
go
Create Database pubs2 on 'master'=2
go
sp__revdevice
Reverse engineer device layout of the server
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
reverse engineers layout of the devices on a server. Purposly excludes device "master", "diskdump", "tapedump1", and "tapedump2".
USAGE
sp__revdevice
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser
WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
1> sp__revdevice
/********* BACKUP DEVICES *********/
Text
---------------------------
exec sp_addumpdevice 'disk','diskdump','/dev/null',2
exec sp_addumpdevice 'disk','master_dbdump','/dumps/master_dbdump',2
exec sp_addumpdevice 'disk','master_logdump','/dumps/master_logdump', 2
exec sp_addumpdevice 'disk','tapedump1','/dev/rmt4',2
exec sp_addumpdevice 'disk','tapedump2','/dev/rst0',2
/****** PHYSICAL DISK DEVICES ******/
Text
----------------------------------------------------------------------
disk init name='data2',physname='/sybase/data2',vdevno=2,size=1024
disk init name='data3',physname='/sybase/data3', vdevno=4,size=2000
disk init name='data',physname='/sybase/data', vdevno=7,size=8096
disk init name='log', physname='/sybase/log', vdevno=3, size=1024
sp__revgroup
Reverse engineer groups layout of the server / database
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
reverse engineers layout of the groups in your current database
USAGE
sp__revgroup
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser
WARNING: Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
1> sp__revgroup
-------------------------------------------------
exec sp_addgroup 'developer'
exec sp_addgroup 'user'
sp__revindex
Reverse engineer indexes in current database
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
reverse engineers indexes in your current database
USAGE
sp__revindex
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser WARNING:
Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
1> sp__revindex
Text
-------------------------------------------------------------------
create clustered index XPKalerts on dbo.alerts (hostname,srvname)
create unique clustered index XPKAccounting on dbo.applications
(dbname)
create unique clustered index XPKaudit_trail on dbo.audit_trail
(date,login_name)
create unique clustered index XPKdatabase on dbo.comn_database
(srvname,dbname)
create unique clustered index XPKcomn_dumpdevices
on dbo.comn_dumpdevices
sp__revkey
Reverse engineer keys in the database
|
AUTHOR
Created By : "Chris Vilsack"
DESCRIPTION
reverse engineers keys in your current database.
USAGE
sp__revkey
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revtable
WARNING: run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
exec sp_primarykey 'sd_region', region_id
go
exec sp_primarykey 'sd_site', site_id
go
exec sp_primarykey 'sd_table_maint', tbl_name
go
exec sp_primarykey 'sd_timeband', timeband_id
go
exec sp_primarykey 'acc_control', db_name
go
sp__revlogin
Reverse engineer logins for current server
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
reverse engineers logins in your current server. Can only be run by sa.
USAGE
sp__revlogin
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser WARNING:
Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
1> sp__revlogin
---------------------------------------------
exec sp_addlogin 'sa','sybase','master'
exec sp_addlogin 'probe','','master'
exec sp_addlogin 'ebarlow','ebarlow','statsdb'
exec sp_addlogin 'xxx','xxx','master'
exec sp_addlogin 'yyy','yyy','master'
exec sp_addlogin 'ttt','ttt','statsdb'
SYSTEM 10 NOTE:
As the system 10 password is encrypted, the password field is filled
sp__revmirror
Reverse engineer mirrors on current server
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
reverse engineers mirror information
USAGE
sp__revuser
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser WARNING:
Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
1> sp__revmirror
----------------------------------------------------------
disk mirror name='data2',mirror='/home/programs/sybase/datax'
sp__revrole
reverse engineer role granting sql for this server
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
SAMPLE OUTPUT
exec sp_role 'grant',sa_role , SYS_OPER
exec sp_role 'grant',sa_role , SYS_INSTALL
exec sp_role 'grant',sa_role , SCRIPT_SVR
exec sp_role 'grant',sso_role , xrm_mgr
exec sp_role 'grant',sso_role , SYS_OPER
exec sp_role 'grant',sso_role , SYS_INSTALL
exec sp_role 'grant',sso_role , SCRIPT_SVR
exec sp_role 'grant',oper_role , xrm_admin
exec sp_role 'grant',oper_role , SYS_OPER
exec sp_role 'grant',oper_role , SYS_INSTALL
exec sp_role 'grant',oper_role , SCRIPT_SVR
sp__revrule
Reverse engineer rules in the database
|
AUTHOR
Created By : "Chris Vilsack"
DESCRIPTION
reverse engineers rules in your current database.
USAGE
sp__revrule
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revtable
WARNING: run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
exec sp_bindrule 'cc1_sd_product_group', 'sd_product_group.fx'
exec sp_bindrule 'cc2_sd_product_group', 'sd_product_group.int'
exec sp_bindrule 'option_yn_rule', 'acc_control.active'
exec sp_bindrule 'option_yn_rule', 'acc_control.backup'
exec sp_bindrule 'option_yn_rule', 'acc_control.modify'
sp__revsegment
Reverse engineer segments of current server / database
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. )
DESCRIPTION
reverse engineers segment layout in your current database
USAGE
sp__revsegment
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revsegment WARNING:
Run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
1> sp__revsegment
-------------------------------------------
exec sp_addsegment 'yyy','datadevice'
sp__revtable
Reverse engineer tables of current server / database
|
AUTHOR
Edward Barlow ( SQL Technologies, inc. ). Modified by Victor Go and Viorel Vlad to add the constraint stuff.
DESCRIPTION
reverse engineers table layout in your current database. This version handles default, check, and primary key constraints.
USAGE
sp__revtable
SEE ALSO
sp__revalias, sp__revdb, sp__revdevice, sp__revgroup, sp__revindex, sp__revlogin, sp__revmirror, sp__revuser, sp__revtable
WARNING: run all the reverse engineering utilities in at least 180 column mode (-w180) to prevent line wrapping.
SAMPLE OUTPUT
1> sp__revtable authors
tbl_txt
-------------------------------------------
CREATE TABLE authors
(
au_id id NOT NULL,
au_lname varchar(4