Recreate All Inline Table Valued Functions

You can use this script to recreate all inline table-valued functions in case database collation or underlying schema has been changed. You can use it to refresh all sql modules when the filter will be removed.

SET NOCOUNT ON

DECLARE @Object_Id INT
DECLARE @Object_Name NVARCHAR(500)
DECLARE @Definition NVARCHAR(MAX)
DECLARE @Stmt NVARCHAR(MAX)

DECLARE @Modules TABLE (
	[object_id] INT NOT NULL PRIMARY KEY,
	[object_name] NVARCHAR(500) NOT NULL,
	[definition] NVARCHAR(MAX) NOT NULL
)

INSERT INTO @Modules
	(	[object_id], [object_name], [definition] )
	SELECT 
		[m].[object_id], QUOTENAME([s].[name]) + '.' + QUOTENAME([o].[name]), [m].[definition]
	FROM [sys].[sql_modules] [m]
		INNER JOIN [sys].[objects] [o] ON [o].[object_id] = [m].[object_id]
		INNER JOIN [sys].[schemas] [s] ON [s].[schema_id] = [o].[schema_id]
	WHERE [o].[type_desc] = 'SQL_INLINE_TABLE_VALUED_FUNCTION' AND [m].[is_inlineable] = 1
	ORDER BY [s].[name], [o].[name]

WHILE EXISTS (SELECT * FROM @Modules)
BEGIN
    
	SELECT TOP(1) 
		@Object_Id = [object_id],
		@Object_Name = [object_name],
		@Definition = [definition]
	FROM @Modules
	ORDER BY [object_id]

	PRINT @Object_Name

	SET @Stmt = 'DROP FUNCTION ' + @Object_Name + ';'

	EXEC [sys].[sp_executesql] @Stmt

	EXEC [sys].[sp_executesql] @Definition

	DELETE FROM @Modules WHERE [object_id] = @Object_Id

END
GO