Schemabinding option and function definition

1, query the metadata of the UDF through the sys.sql_modules and sys.objects Fields OBJECT_ID Association

SELECTO.object_id, O.name, O.type,o.type_desc, M.definition,m.is_schema_bound fromSys.sql_modules asmInner JOINSys.objects aso onM.object_id =O.object_id whereO.typeinch('FN','IF','TF')GO 

Determine the type of Object field:type

FN = SQL scalar function

IF = SQL inline table-valued function

TF = SQL table-valued-function

sys.sql_modules: Returns a row for each object this is an SQL language-defined module in SQL Server.

sys.objects: Contains a row for each user-defined, schema-scoped object which is created within a database.

Sys.sql_modules includes not only the definition information of the UDF, but also the definition of user-defined Usp,view. View MSDN sys.sql_modules

2, set Schemabinding option to create a strong dependency

Schemabinding option is used for view and UDF. When creating the View and UDF, the View and UDF refer to some underlying objects,schemabinding option to bind the schema and view of the underlying objects with the UDF, in V The column referenced in Iew and UDF cannot be deleted or modified, nor can the underlying objects be deleted.

Example

CREATE TABLE [dbo].[Dt_study](    [ID] [int]  not NULL PRIMARY KEY CLUSTERED ,    [name] [nvarchar]( -)NULL,    [Sex] [bit] NULL,)GOCreate Viewdbo. View_dt_study_male withschemabinding as    SelectId,name,sex fromDbo.dt_studywhereSex=0Go--can not alter the underlying object Colun because the view set schemabinding optionAlter Table [dbo].[Dt_study]Alter columnSexbit  not NULLGo

MSG 5074, Level A, State 1, line 1
The object ' View_dt_study_male ' is dependent on column ' sex '.
MSG 4922, Level A, State 9, line 1
Alter TABLE alter COLUMN sex failed because one or more objects access the this COLUMN.

If you do not use schemabinding option,underlying object is allowed to modify the schema and is deleted. When you try to query a view or UDF, if underlying objects does not exist or column does not exist, it causes a runtime error. If you use schemabinding option, you can avoid this error that modifies the referenced object schema. If you must modify underlying objects, you must first modify the UDF or view.

To support schemabinding option, an object definition must meet two requirement:

1. The query statement is not allowed to use * in the SELECT clause, and column name must be explicitly listed
2. When referencing an object, you must use the Two_part name, which is Schema_name.object_name

Best practices

If A user-defined function is isn't created with the SCHEMABINDING clause, changes that be made to underlying objects can a Ffect the definition of the function and produce unexpected results when it was invoked. We recommend that implement one of the following methods to ensure the function does not become outdated because of changes to its underlying objects:

• Specify the WITH SCHEMABINDING clause is creating the function. This ensures, the objects referenced in the function definition cannot being modified unless the function is also modifie D.

• Execute the sp_refreshsqlmodule stored procedure after modifying any object that's specified in the definition of the Ction.

Reference Documentation:

https://msdn.microsoft.com/en-us/library/ms186755 (v=sql.120). aspx

Metadata

The following table lists the system catalog views this you can use to return metadata about user-defined functions.

System View	Description
Sys.sql_modules	Displays the definition of Transact-SQL user-defined functions. The definition of functions created by using the encryption option cannot is viewed by using sys.sql_modules; However, other information about the encrypted functions is displayed.
Sys.assembly_modules	Displays information about the CLR user-defined functions.
Sys.parameters	Displays information about the parameters defined in user-defined functions.
Sys.sql_expression_dependencies	Displays the underlying objects referenced by a function.

Schemabinding option and function definition