http://goo.gl/EmKxy0

Home > ASE > Sybase frequently used system stored procedures

Sybase frequently used system stored procedures

===================
sp_foreignkey
————-
defines a foreign key on a table or view in the current database

Syntax – sp_foreignkey tabname, pktabname, col1 [, col2] ..[, col8]

Example
——–
sp_foreignkey titles, publishers, pub_id

sp_foreignkey orders, parts, part, subpart

Usage — sp_foreignkey adds the key to the syskeys table. Keys make explicit a logical relationship that is implicit in your database design. sp_foreignkey does not enforce referential integrity constraints; use the foreign key clause of the create table or alter table command to enforce a foreign key relationship.

— To display a report on the keys that have been defined, execute sp_helpkey.

NOTE: Forign key created using this proc is different from creating via “create table” command is that is only creates logical relationship between to table columns which would be used by system procedure “sp_helpjoins” to suggest user that what columns could be used in join. It does not enforce any data validation.
===================

===================
sp_commonkey
————
defines a common-key between two tables or view.

Syntax – sp_commonkey tabaname, tabbname, col1a, col1b [, col2a, col2b, …, col8a, col8b]

Example – sp_commonkey titles, titleauthor, title_id, title_id

Usage — Common keys are created in order to make explicit a logical relationship that is implicit in your database design. The information can be used by an application. sp_commonkey does not enforce referential integrity constraints; use the primary key and foreign key clauses of the create table or alter table command to enforce key relationships.
— Executing sp_commonkey adds the key to the syskeys system table. To display a report on the common keys that have been defined, use sp_helpkey.

NOTE: Comman key created using this proc is different from creating via “create table” command is that is only creates logical relationship between to table columns which would be used by system procedure “sp_helpjoins” to suggest user that what columns could be used in join. It does not enforce any data validation.

===================

===================
sp_helpjoins
————
lists the columns in two tables or views that are likely join candidates

Syntax — sp_helpjoins lefttab, righttab

Usage — The column pairs that sp_helpjoins displays come from either of two sources. sp_helpjoins checks the syskeys table in the current database to see if any foreign keys have been defined with sp_foreignkey on the two tables, then checks to see if any common keys have been defined with sp_commonkey on the two tables. If sp_helpjoins does not find any foreign keys or common keys there, it checks for keys with the same user-defined datatypes. If that fails, it checks for columns with the same name and datatype.

— sp_helpjoins does not create any joins.

NOTE: This sproc used the entries in syskeys table to suggest user what columns could be used to join.
===================

===================
sp_helpconstraint
—————–
prints the name & definition integrity constraint for a specified table [ sp_helpconstraint ]

Syntax — sp_helpconstraint [objname] [, detail]

objname — is the name of a table that has one or more integrity constraints defined by a create table or alter table statement.

detail — returns information about the constraint’s user or error messages.

Usage — sp_helpconstraint prints the name and definition of the integrity constraint, and the number of references used by the table. The detail option returns information about the constraint’s user or error messages.
— sp_helpconstraint reports only the integrity constraint information about a table (defined by a create table or alter table statement). It does not report information about rules, triggers, or indexes created using the create index statement.
— You can use sp_helpconstraint only for tables in the current database.
— If a query exceeds the configured number of auxiliary scan descriptors, Adaptive Server returns an error message. You can use sp_helpconstraint to determine the necessary number of scan descriptors.
— A System Security Officer can prevent the source text of constraint definitions from being displayed to most users who execute sp_helpconstraint. To restrict select permission on the text column of the syscomments table to the object owner or a System Administrator, use sp_configure to set the select on syscomments.text column parameter to 0.

NOTE: Constrains created by using this system procedure have enteries on sysconstraints table, not in syskeys. Becuase it enforce the data validation and business rule.
===================

===================
sp_activeroles
————–
Displays all active roles

Syntax — sp_activeroles [expand_down]
===================

===================
sp_addalias
———–

Allows an Adaptive Server user to be known in a database as another user.

Syntax — sp_addalias loginame, name_in_db
Parameters — loginame
is the master.dbo.syslogins name of the user who wants an alternate identity in the current database.
name_in_db
is the database user name to alias loginame to. The name must exist in both master.dbo.syslogins and in the sysusers table of the current database.

Examples There is a user named “albert” in the database’s sysusers table and a login for a user named “victoria” in master.dbo.syslogins. This command allows “victoria” to use the current database by assuming the name “albert”:

sp_addalias victoria, albert

Usage — Executing sp_addalias maps one user to another in the current database.The mapping is shown in sysalternates, where the two users’ suids (system user IDs) are connected.
— A user can be aliased to only one database user at a time.
— If the user named in loginame is in the database’s sysusers table, Adaptive Server does not use the user’s alias identity, because it checks sysusers and finds the loginame before checking sysalternates, where the alias is listed.

===================

===================
sp_addgroup
———–
Adds a group to a database. Groups are used as collective names in granting and revoking privileges.

Syntax — sp_addgroup grpname

Every database is created with a group named “public”. Every user is automatically a member of “public”. Each user can be a member of one additional group (along with Public).
===================

===================
sp_addlogin
———–
Adds a new user account to Adaptive Server; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified login at creation.

Syntax — sp_addlogin loginame, passwd [, defdb]
[, deflanguage] [, fullname] [, passwdexp]
[, minpwdlen] [, maxfailedlogins]

Usage — After assigning a default database to a user with sp_addlogin, the Database Owner or System Administrator must provide access to the database by executing sp_adduser or sp_addalias.

— Although a user can use sp_modifylogin to change his or her own default database at any time, a database cannot be used without permission from the Database Owner.

===================

===================
sp_addmessage
————-
Adds user-defined messages to sysusermessages for use by stored procedure print and raiserror calls and by sp_bindmsg.

Syntax — sp_addmessage message_num, message_text
[, language [, with_log [, replace]]]

message_num — is the message number of the message to add. The message number for a user-defined message must be 20000 or greater.

with_log — specifies whether the message is logged in the Adaptive Server error log as well as in the Windows NT Event Log on Windows NT servers, if logging is enabled.

replace — specifies whether to overwrite an existing message of the same number and languid. If replace is specified, the existing message is overwritten; if replace is omitted, it is not. If you do not specify a value for replace, the parameter’s default behavior specifies that the existing message will not be overwritten.

Usage — print and raiserror recognize placeholders in the message text to print out. A single message can contain up to 20 unique placeholders in any order. These placeholders are replaced with the formatted contents of any arguments that follow the message when the text of the message is sent to the client.
===================

===================
sp_addtype
———-
Creates a user-defined datatype.

Syntax – sp_addtype typename, phystype [(length) | (precision [, scale])] [, “identity” | nulltype]

Examples
——–
sp_addtype ssn, “varchar(11)”
sp_addtype birthday, “datetime”, null
sp_addtype temp52, “numeric(5,2)”
sp_addtype “row_id”, “numeric(10,0)”, “identity”

identity — indicates that the user-defined datatype has the IDENTITY property.Enclose the identity keyword within single or double quotes. You can specify the IDENTITY property only for numeric datatypes with a scale of 0.

IDENTITY columns store sequential numbers, such as invoice numbers or employee numbers, that are generated by Adaptive Server. The value of the IDENTITY column uniquely identifies each row in a table. IDENTITY columns are not updatable and do not allow null values.

Usage — sp_addtype creates a user-defined datatype and adds it to the systypes system table. Once a user-defined datatype is created, you can use it in create table and alter table statements and bind defaults and rules to it.

— If nchar or nvarchar is specified as the phystype, the maximum length of columns created with the new type is the length specified in sp_addtype multiplied by the value of @@ncharsize at the time the type was added.

— If unichar or univarchar is specified as the phystype, the maximum length of columns created with the new type is the length specified in sp_addtype multiplied by the value of 2 at the time the type was added.

— If a user-defined datatype is defined with the IDENTITY property, all columns created from it are IDENTITY columns.

— You can specify IDENTITY, NOT NULL, or neither in the create or alter table statement. Following are three different ways to create an IDENTITY column from a user-defined datatype with the IDENTITY property:

create table new_table (id_col IdentType)
create table new_table (id_col IdentType identity)
create table new_table (id_col IdentType not null)

— When you create a column with the create table or alter table statement, you can override the null type specified with the sp_addtype system procedure:

• Types specified as NOT NULL can be used to create NULL or IDENTITY columns.
• Types specified as NULL can be used to create NOT NULL columns, but not to create IDENTITY columns.

===================

===================
sp_adduser
———-
Adds a new user to the current database.

Syntax — sp_adduser loginame [, name_in_db [, grpname]]

Usage — Specifying a name_in_db parameter gives the new user a name in the database that is different from his or her login name in Adaptive Server. The ability to assign a user a different name is provided as a convenience. It is not an alias, as provided by sp_addalias, since it is not mapped to the identity and privileges of another user.

— A user and a group cannot have the same name.
— A user can be a member of only one group other than the default group, “public”. Every user is a member of the default group, “public”. Use sp_changegroup to change a user’s group.
— In order to access a database, a user must either be listed in sysusers (with sp_adduser) or mapped to another user in sysalternates (with sp_addalias), or there must be a “guest” entry in sysusers.

===================

===================
sp_altermessage
—————
Enables and disables the logging of a system-defined or user-defined message
in the Adaptive Server error log.

Syntax – sp_altermessage message_id, parameter, parameter_value

message_id — Is the message number of the message to be altered. This is the number of the message as it is recorded in the error column in the sysmessages or sysusermessages system table.

parameter — is the message parameter to be altered. The maximum length is 30 bytes.The only valid parameter is with_log.

parameter_value — Is the new value for the parameter specified in parameter. The maximum length is 5 bytes. Values are true and false.

Example – sp_altermessage 2000, ‘with_log’, ‘TRUE’
===================

===================
sp_dropdevice
————-
drops a server DB device or dump device
===================

===================
sp_help
——-
returns help about a specified DB object [sp_help ]
===================

===================
sp_rename
———
used to rename a table [sp_rename ]
===================

===================
sp_helptext
———–
prints the text of a system procedure, trigger, view, default, rule, or integrity check constraint [sp_helptext ]
===================

===================
sp_helpsort
———–
displays Server’s default sort order and character set
===================

===================
sp_depends
———-
returns a list of all the dependant objects of a table
===================

===================
sp_helpgroup
————
reports information about a particular group or about all groups in a particular database

===================

===================
sp_dboption
———–
displays or changes database options

Syntax – sp_dboption [dbname, optname, {true | false}]

dbname — is the name of the database in which the option is to be set. You must be
using master to execute sp_dboption with parameters (that is, to change a database option). You cannot, however, change option settings in the master database.

optname — is the name of the option to be set. Adaptive Server understands any unique string that is part of the option name. Use quotes around the option name if it is a keyword or includes embedded blanks or punctuation.

true | false — true to turn the option on, false to turn it off.

Examples
——-
sp_dboption — List all database option

master..sp_dboption pubs2, “read”, true –> Makes the database pubs2 read only

pubs2..sp_dboption pubs2, “read”, false –> Makes the database pubs2 writable again

master..sp_dboption pubs2, “select into”, true

master..sp_dboption mydb, “auto identity”, true –> Automatically defines 10-digit IDENTITY columns in new tables created in mydb. The IDENTITY column, SYB_IDENTITY_COL, is defined in each new table that is created without specifying either a primary key, a unique constraint, or an IDENTITY column:

Automatically includes an IDENTITY column in the mydb –?????????
tables’ index keys, provided these tables already have an IDENTITY
column. All indexes created on the tables will be internally unique:
use master
go
sp_dboption mydb, “identity in nonunique index”,
true
go
use mydb
go
checkpoint
go

Example 7 Automatically includes an IDENTITY column with a unique, — ????
nonclustered index for new tables in the pubs2 database:
use master
go
sp_dboption pubs2, “unique auto_identity index”,
true
go
use pubs2
go
checkpoint
go

Usage — The master database option settings cannot be changed
— For a report on which database options are set in a particular database, execute sp_helpdb.
— After sp_dboption has been executed, the change does not take effect until the checkpoint command is issued in the database for which the option was changed.
— The no chkpt on recovery option disables the trunc log on chkpt option when both are set with sp_dboption for the same database. This conflict is especially possible in the tempdb database which has trunc log on chkpt set to on as the default.

===================

===================
sp_helpdb
———
it reports information about a specified DB or about all the DB on the Server [sp_helpdb ]

Syntax – sp_helpdb [dbname]

dbname — is the name of the database on which to report information. Without this optional parameter, sp_helpdb reports on all databases. dbname can include wildcard characters to return all databases that match the specified pattern.

Usage — sp_helpdb reports on the specified database when dbname is given. If no value is supplied for dbname, sp_helpdb reports on all the databases listed in master.dbo.sysdatabases.

===================

===================
sp_spaceused
————
it returns the space used by a specified table [ sp_spaceused ]
Displays estimates of the number of rows, the number of data pages, the size of indexes, and the space used by a specified table or by all tables in the current database.

Syntax – sp_spaceused [objname [,1] ]
objname
is the name of the table on which to report. If omitted, a summary of space used in the current database appears.

1
prints separate information on the table’s indexes and text/image storage.

sp_spaceused titles
name rows reserved data index_size unused
——- —– ———- —– ——— ——
titles 18 48 KB 6 KB 4 KB 38 KB

(0 rows affected)
===================

Check the Sybase Wiki @ sybasewiki.com
Categories: ASE Tags:
  1. No comments yet.
  1. No trackbacks yet.