Schema binding an object such as a user-defined function (UDF) is considered good practice, as it prevents changes being done to any objects that it references that could inadvertently break the function.

You can schema bind a user-defined function at the time you create it, or you can alter later on.

Normally, you can check if a UDF is schema bound in SQL Server by viewing its definition. You can usually do this via the GUI by selecting “Script as Create” or similar.

You can also do it using T-SQL by selecting the definition column of the sys.sql_modules system catalog view.

But this will only work if the UDF isn’t encrypted.

However, there is another column in the sys.sql_modules view that serves our purpose whether the UDF is encrypted or not: is_schema_bound

Example 1 – Encrypted UDF

Here’s an example of finding out whether an encrypted user-defined function called udf_CatsByName_ITVF is schema bound or not.

SELECT 
  definition,
  is_schema_bound
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('udf_CatsByName_ITVF');

Result:

+--------------+-------------------+
| definition   | is_schema_bound   |
|--------------+-------------------|
| NULL         | 1                 |
+--------------+-------------------+

In this case, the UDF is schema bound.

Also notice that the definition column returns NULL because the function has had encryption applied.

Example 2 – UDF Without Encryption

If encryption hadn’t been applied, we could have seen the full definition in that column, and we would have seen the argument WITH SCHEMABINDING in the definition.

Here’s the query again when the function is not encrypted.

SELECT 
  definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('udf_CatsByName_ITVF');

Result:

+--------------+
| definition   |
|--------------|
| 
CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
    RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName
    );

              |
+--------------+

I’ve removed the is_schema_bound column from the query to make it easier to read.

Either way, the is_schema_bound column can be used whether the UDF is encrypted or not.