sp_checksource

Description

Checks for the existence of the source text of compiled objects such as views, defaults, rules, triggers, procedures, declarative defaults, check constraints, computed columns, function-based indexes and predicates. The predicate name may be a user-defined or 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

Usage

sp_checksource checks for the existence of the source text of the specified compiled object. If the source text exists for the specified object, sp_checksource returns 0. If the source text does not exist for the specified object, sp_checksource returns 1.
If you do not provide any parameters, sp_checksource checks the existence of the source text for all compiled objects in the current database.
To use sp_checksource with no parameters, you must be the database owner or system administrator.
sp_checksource encrypts the text of user-defined functions.

Permissions

The permission checks for sp_checksource differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be a user with manage database privilege 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.

Granular permissions disabled

With granular permissions disabled, you must be the database owner or a user with sa_role to check for the existence of the source text of compiled objects that are owned by another user.