Executing System Procedures

If a system procedure is executed in a database other than sybsystemprocs, it operates on the system tables in the database in which it was executed.

For example, if the database owner of pubs2 runs sp_adduser in pubs2, the new user is added to pubs2..sysusers.

Run a system procedure in a specific database by either:
  • Opening that database with the use command and execute the procedure, or

  • Qualifying the procedure name with the database name.

For example, the user-defined system procedure sp_foo, which executes the db_name system function, returns the name of the database in which it is executed. When executed in the pubs2 database, it returns the value “pubs2”:
exec pubs2..sp_foo
------------------------------ 
pubs2
(1 row affected, return status = 0)
When executed in sybsystemprocs, it returns the value “sybsystemprocs”:
exec sybsystemprocs..sp_foo
------------------------------ 
sybsystemprocs 
(1 row affected, return status = 0)