Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sql_modules

 

This is a view in the mssqlsystemresource database.

Returns a row for each object that is an SQL language-defined module. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view.

Column name Data type Description

object_id

int

ID of the object of the containing object. Is unique within a database.

definition

nvarchar(max)

SQL text that defines this module.
NULL = Encrypted.

uses_ansi_nulls

bit

Module was created with SET ANSI_NULLS ON.
Will always be = 0 for rules and defaults.

uses_quoted_identifier

bit

Module was created with SET QUOTED_IDENTIFIER ON.

is_schema_bound

bit

Module was created with SCHEMABINDING option.

uses_database_collation

bit

1 = Schema-bound module definition depends on the default-collation of the database for correct evaluation; otherwise, 0. Such a dependency prevents changing the database's default collation.

is_recompiled

bit

Procedure was created WITH RECOMPILE option.

null_on_null_input

bit

Module was declared to produce a NULL output on any NULL input.

execute_as_principal_id

Int

ID of the EXECUTE AS database principal.
NULL by default or if EXECUTE AS CALLER.
ID of the specified principal if EXECUTE AS SELF or EXECUTE AS <principal>.
-2 = EXECUTE AS OWNER.

 




Syntax
CREATE VIEW sys.sql_modules AS
	SELECT object_id = o.id,
		definition = object_definition(o.id),
		uses_ansi_nulls = sysconv(bit, o.status & 0x40000), 			-- OBJMOD_ANSINULLS
		uses_quoted_identifier = sysconv(bit, o.status & 0x80000), 		-- OBJMOD_QUOTEDIDENT
		is_schema_bound = sysconv(bit, o.status & 0x20000), 			-- OBJMOD_SCHEMABOUND
		uses_database_collation = sysconv(bit, o.status & 0x100000), 	-- OBJMOD_USESDBCOLL
		is_recompiled = sysconv(bit, o.status & 0x400000), 				-- OBJMOD_NOCACHE
		null_on_null_input = sysconv(bit, o.status & 0x200000), 		-- OBJMOD_NULLONNULL
		execute_as_principal_id = x.indepid
	FROM sys.sysschobjs o
	LEFT JOIN sys.syssingleobjrefs x ON x.depid = o.id AND x.class = 22 AND x.depsubid = 0 -- SRC_OBJEXECASOWNER
	WHERE o.pclass <> 100
		AND ((o.type = 'TR' AND has_access('TR', o.id, o.pid, o.nsclass) = 1)
			OR (type IN ('P','V','FN','IF','TF','RF','IS') AND has_access('CO', o.id) = 1)
			OR (type IN ('R','D') AND o.pid = 0))

 
Last revision 2008RTM
See also

  all_sql_modules (View)
server_sql_modules (View)
sp_MSrepltrigredefine (Procedure)
sp_refreshsqlmodule (Procedure)
sp_refreshsqlmodule_internal (Procedure)
system_sql_modules (View)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash