sp_checksource

Description

Checks for the existence of the source text of the compiled object, and for the existence of computed column source text. The compiled object may be the user-defined name of a predicate, or if the predicate has no external name, its internal name.

Syntax

sp_checksource [objname [, tabname [, username]]]

Parameters

objname

is the compiled object to be checked for the existence of its source text.

tabname

is the name of the table or view to be checked for the existence of all check constraints, defaults, and triggers defined on it.

username

is the name of the user who owns the compiled objects to be checked for the existence of the source text.

Examples

Example 1

Checks for the existence of the source text of all compiled objects in the current database:

sp_checksource

Example 2

Checks for the existence of the source text of the view named titleview:

sp_checksource titleview

Example 3

Checks for the existence of the source text of the view named titls_vu that is owned by Mary:

sp_checksource title_vu, @username = Mary

Example 4

Checks for the existence of the source text of the custom stored procedure list_phone_proc:

sp_checksource list_phone_proc

Example 5

Checks for the existence of the source text of all the check constraints, triggers, and declarative defaults defined on the table named my_tab:

sp_checksource @tabname = "my_tab"

Example 6

Checks for the existence of the source text of the view my_vu and all check constraints, triggers, and defaults defined on the table my_tab:

sp_checksource @objname = "my_vu", @tabname = "my_tab"

Example 7

Checks for the existence of the source text of all compiled objects owned by Tom:

sp_checksource @username = "Tom"

Example 8

Checks for the existence of the source text for the “pred1” predicate:

sp_checksource pred1

Msg 18404, Level 16, State 1:
Procedure 'sp_aux_text', Line 265:
Source text for compiled object pred1 (id = 592002109 
exists)

Usage

Permissions

Only a database owner or system administrator can execute sp_checksource to check for the existence of the source text of compiled objects that are owned by another user. Any user can execute sp_checksource to check for the existence of the source text for his or her own compiled objects.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_hidetext