Export (0) Print
Expand All
Expand Minimize

sp_refreshsqlmodule (Transact-SQL)

New: 12 December 2006

Updates the metadata for the specified nonschema-bound stored procedure, user-defined function, or view. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.

Topic link icon Transact-SQL Syntax Conventions


sp_refreshsqlmodule [ @name = ] 'module_name' 

[ @name = ] 'module_name'

Is the name of the stored procedure, function, or view. module_name cannot be a common language runtime (CLR) stored procedure or a CLR function. module_name cannot be schema bound. module_name is nvarchar, with no default. module_name can be a multipart identifier, but can only refer to objects in the current database.

0 (success) or a nonzero number (failure)

sp_refreshsqlmodule should be run when changes are made to the objects underlying the stored procedure, user-defined function, or view that affect its definition. Otherwise, the object might produce unexpected results when it is queried or invoked. To refresh a view, you can use either sp_refreshsqlmodule or sp_refreshview with the same results.

sp_refreshsqlmodule does not affect any permissions, extended properties, or SET options that are associated with the object.

Any signatures that are associated with the object are dropped when you run sp_refreshsqlmodule.

Requires ALTER permission on the stored procedure, function, or view and REFERENCES permission on any CLR user-defined types and XML schema collections that are referenced by the object.

Additionally, for modules that are defined with the EXECUTE AS clause, IMPERSONATE permission is required on the specified principal. Generally, refreshing an object does not change its EXECUTE AS principal, unless the module was defined with EXECUTE AS USER and the user name of the principal now resolves to a different user than that at the time the module was created.

The following example refreshes a user-defined function. The example creates an alias data type, mytype, and a user-defined function, to_upper, that uses mytype. Then, mytype is renamed to myoldtype, and a new mytype is created that has a different definition. The to_upper function is refreshed so that it references the new implementation of mytype, instead of the old one.

-- Create an alias type.
USE AdventureWorks;
GO
IF EXISTS (SELECT 'mytype' FROM sys.types WHERE name = 'mytype')
DROP TYPE mytype;
GO

CREATE TYPE mytype FROM nvarchar(5);
GO

IF OBJECT_ID ('to_upper', 'FN') IS NOT NULL
DROP FUNCTION to_upper;
GO

CREATE FUNCTION to_upper (@a mytype)
RETURNS mytype
WITH ENCRYPTION
AS
BEGIN
RETURN upper(@a)
END;
GO

SELECT dbo.to_upper('abcde');
GO

-- Increase the length of the alias type.
sp_rename 'mytype', 'myoldtype', 'userdatatype';
GO

CREATE TYPE mytype FROM nvarchar(10);
GO

-- The function parameter still uses the old type.
SELECT name, type_name(user_type_id) 
FROM sys.parameters 
WHERE object_id = OBJECT_ID('dbo.to_upper');
GO

SELECT dbo.to_upper('abcdefgh'); -- Fails because of truncation
GO

-- Refresh the function to bind to the renamed type.
EXEC sys.sp_refreshsqlmodule 'dbo.to_upper';

-- The function parameters are now bound to the correct type and the statement works correctly.
SELECT name, type_name(user_type_id) FROM sys.parameters
WHERE object_id = OBJECT_ID('dbo.to_upper');
GO

SELECT dbo.to_upper('abcdefgh');
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft