System Stored Procedures

New and changed system procedures in Adaptive Server 15.0.2

New system stored procedures
System stored procedure Description
sp_downgrade Validates readiness for downgrade to an earlier 15.0.x release. Also downgrades the system catalog changes Adaptive Server 15.0.2 modified.
sp_spaceusage Reports the space usage for a table, index, or transaction log and estimates the amount of fragmentation for tables and indexes in a database. The estimates are computed using an average row-length for data and index rows, and the number of rows in a table. You can archive the space usage and fragmentation data for future reporting and trends analysis. sp_spaceusage supports a number of actions, including help, display, archive and report, to indicate the current Adaptive Server space usage.

Changed system stored procedures
System stored procedure Description of change
sp_autoformat

Now accepts columns of datatypes int (smallint, bigint, tinyint, unsigned int), numeric, money, date/time, and float, real, and double precision.
sp_changedbowner Changes the owner of a database. You can now execute it with either sa_role or sso_role privileges. The owner of thresholds for that database is also changed to the specified user.
sp_checksource Encrypts the text of user-defined functions.
sp_configure Now displays non-default value settings.
sp_depends Checks for any object dependencies for user-defined functions.
sp_deviceattr Displays a warning message if the dsync option is disabled for a database device on a file system.
sp_displaylogin includes these changes:
  • supports both a wildcard expression and a server user ID, and displays matching logins:
    sp_displaylogin ['user_id' | '[loginame | wildcard]'

    • user_id – user ID (suid) of the user whose login you are displaying.

    • wildcard – wildcard character used for search purposes.

    Displays the login account for the user with a suid of 56:
    sp_displaylogin '56'
    Displays the login account information for all users whose logins begin with “st”:
    sp_displaylogin 'st%'
sp_droplogin When sp_droplogin is unable to drop a login due to the existence—in any database—of a user in sysusers referencing the login suid, the names of databases in which the references are found are now displayed in the error message.
sp_help Displays information about user-defined functions.
sp_helpdevice The description column of sp_helpdevice displays information about the device type. The device type is one of: raw device, block device, or file system device.
sp_helprotect The new option, permission_name, in sp_helprotect provides information (grantor name, grantee name, table/column name, and grantability) for any specific permission granted in a given database.
sp_hidetext Encrypts the text for user-defined functions.
sp_locklogin The lock option to sp_locklogin, when used with a value for number of inactive days, locks inactive accounts that have not authenticated within that period. The following example locks all login accounts that have not authenticated within the past 60 days.
sp_modifylogin account

A new value for the 'max failed_logins' option indicates that the failed login count in the syslogins column login count, is updated whenever an authentication failure occurs, but that the account is not locked.

sp_modifystats Allows the System Administrator, or any user with permission to execute the procedure and update statistics on the target table, to modify the density values of columns in sysstatistics
sp_monitorconfig Enhanced to create a table to hold the result set, if the user passes a table name for result_tabl_name that does not already exist.
sp_passwordpolicy

  • The set and clear commands in sp_passwordpolicy are now audited, through audit event 115, “Password Administration.”

  • Additional syntax:
    sp_passwordpolicy
		“enable last login updates”,
		“allow password downgrade”
		“regenerate keypair”,
		“expire login passwords”, “[login_name | wildcard]”
		“expire role passwords”, “[role_name | wildcard]”
		“expire stale login passwords”, “datetime”
		“expire stale role passwords”, “datetime”
		"maximum failed logins", -1
sp_fixindex Now works on a set of indexes rather than on a single index. sp_fixindex rebuilds the data layer if the target table has a placement or clustered index (it reclaims the unused space in the data layer while working on the placement or clustered index of a system table).
sp_sendmsg In previous releases, the maximum length for a message sent with this system procedure was 255 characters. For Adaptive Server release 15.0.2, the maximum length of a sp_sendmsg message is 4096 characters.
sp_who A new column, tempdbname, displays temporary database names of all active sessions.
sp_helptext Reports the text of user-defined functions, and introduces the numlines and printops parameters.
sp_ldapadmin Introduces new parameters: set_max_ldapua_desc, set_num_retries, and set_log_interval.
sp_monitor Enhances the event and help parameters.
sp_tempdb Introduces the show and who parameters.

See the Reference Manual: Procedures.

Parent topic: System Changes in Adaptive Server 15.0.2