Devart Blog

Refreshing Objects of SQL Server Databases

Posted by on November 2nd, 2010

In some cases, for example, when migrating or updating objects, it’s necessary to update metadata for views created without evident listing of columns in the select-list, i. e. for views of the SELECT * FROM some_table type. The built-in sp_refreshview procedure is developed for these goals, it updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Metadata of other objects containing the code can be changed using the built-in sp_refreshsqlmodule procedure, that is designed to update metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger or database-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.

So using the script specified below, the updates of all objects containing the code for the current database can be performed (i.e. update metadata information for all VIEWS, DML TRIGGERS, PROCEDURES, FUNCTIONS, DDL TRIGGERS):

USE [your_db]
GO
PRINT ' -- Refreshing all VIEWS in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshview '''
+QUOTENAME(ss.name)+'.'
+QUOTENAME(so.name)+'''' as stmt_refresh_views
FROM sys.objects AS so 
   INNER JOIN sys.sql_expression_dependencies AS sed 
      ON so.object_id = sed.referencing_id 
   INNER JOIN sys.schemas AS ss
      ON so.schema_id = ss.schema_id
WHERE so.type = 'V' AND sed.is_schema_bound_reference = 0
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
   BEGIN
       print @stmt_refresh_object
       exec sp_executesql @stmt_refresh_object
      FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
   END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO
PRINT ' -- Refreshing all DML TRIGGERS in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''
+QUOTENAME(schemas.name)+'.'
+QUOTENAME(triggers.name)+'''' 
as stmt_refresh_dml_triggers
FROM sys.triggers AS triggers WITH(NOLOCK)
  INNER JOIN sys.objects AS objects WITH(NOLOCK) 
     ON objects.object_id = triggers.parent_id
  INNER JOIN sys.schemas AS schemas WITH(NOLOCK) 
     ON schemas.schema_id = objects.schema_id
  LEFT JOIN sys.sql_modules AS sql_modules WITH(NOLOCK) 
     ON sql_modules.object_id = triggers.object_id
  LEFT JOIN sys.assembly_modules AS assembly_modules WITH(NOLOCK) 
     ON assembly_modules.object_id = triggers.object_id
  LEFT JOIN sys.assemblies AS assemblies WITH(NOLOCK) 
     ON assemblies.assembly_id = assembly_modules.assembly_id
  LEFT JOIN sys.database_principals AS principals WITH(NOLOCK) 
     ON principals.principal_id = assembly_modules.execute_as_principal_id 
       OR principals.principal_id = sql_modules.execute_as_principal_id
WHERE RTRIM(objects.type) IN ('U','V') and parent_class = 1 
    AND sql_modules.is_schema_bound = 0
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
   BEGIN
       print @stmt_refresh_object
       exec sp_executesql @stmt_refresh_object
      FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
   END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO
PRINT ' -- Refreshing all PROCEDURES in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''
+QUOTENAME(s.name)+'.'
+QUOTENAME(p.name)+''''
as stmt_refresh_procedures
FROM
  sys.procedures AS p WITH(NOLOCK)
LEFT JOIN
  sys.schemas AS s WITH(NOLOCK)
     ON p.schema_id = s.schema_id
LEFT JOIN
  sys.sql_modules AS sm WITH(NOLOCK)
     ON p.object_id = sm.object_id
LEFT JOIN
  sys.assembly_modules AS am WITH(NOLOCK)
     ON p.object_id = am.object_id
LEFT JOIN
  sys.assemblies AS a
     ON a.assembly_id = am.assembly_id
LEFT JOIN
  sys.objects AS o WITH(NOLOCK)
     ON sm.object_id = o.object_id
LEFT JOIN
  sys.database_principals AS dp WITH(NOLOCK)
     ON sm.execute_as_principal_id = dp.principal_id 
        OR am.execute_as_principal_id = dp.principal_id
LEFT JOIN
  sys.database_principals AS dp1 WITH(NOLOCK)
     ON o.principal_id = dp1.principal_id
WHERE
  (CAST(CASE  WHEN p.is_ms_shipped = 1 then 1
            WHEN (SELECT major_id FROM sys.extended_properties WHERE
                  major_id = p.object_id AND minor_id = 0 AND class = 1 AND
                  name = 'microsoft_database_tools_support') IS NOT NULL THEN 1
            ELSE 0 END AS bit)=0)
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
   BEGIN
       print @stmt_refresh_object
       exec sp_executesql @stmt_refresh_object
      FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
   END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO
PRINT ' -- Refreshing all FUNCTIONS in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''
+QUOTENAME(SCHEMA_NAME(o.schema_id))+'.'
+QUOTENAME(o.name)+'''' 
as stmt_refresh_functions
FROM sys.objects AS o WITH(NOLOCK)
  LEFT JOIN sys.sql_modules AS sm WITH(NOLOCK) 
      ON o.object_id = sm.object_id
  LEFT JOIN sys.assembly_modules AS am WITH(NOLOCK) 
      ON o.object_id = am.object_id
  LEFT JOIN sys.database_principals p1 WITH(NOLOCK) 
      ON p1.principal_id = o.principal_id
  LEFT JOIN sys.database_principals p2 WITH(NOLOCK) 
      ON p2.principal_id=am.execute_as_principal_id
  LEFT JOIN sys.database_principals p3 WITH(NOLOCK) 
      ON p3.principal_id=sm.execute_as_principal_id
  LEFT JOIN sys.assemblies AS ass WITH(NOLOCK) 
      ON ass.assembly_id = am.assembly_id
WHERE o.type IN ('FN','IF','TF','AF','FS','FT') and sm.is_schema_bound = 0
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
   BEGIN
       print @stmt_refresh_object
       exec sp_executesql @stmt_refresh_object
      FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
   END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO
PRINT ' -- Refreshing all DDL TRIGGERS on database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''
+QUOTENAME(t.name)+''','
+'''DATABASE_DDL_TRIGGER''' as stmt_refresh_ddl_triggers
FROM sys.triggers AS t WITH(NOLOCK)
  LEFT JOIN sys.sql_modules AS sm WITH(NOLOCK) 
     ON t.object_id = sm.object_id
  LEFT JOIN sys.assembly_modules AS am WITH(NOLOCK) 
     ON t.object_id = am.object_id
  LEFT JOIN sys.assemblies AS assemblies WITH(NOLOCK) 
     ON assemblies.assembly_id = am.assembly_id
  LEFT JOIN sys.database_principals AS principals WITH(NOLOCK) 
     ON principals.principal_id = sm.execute_as_principal_id 
        OR principals.principal_id = am.execute_as_principal_id
WHERE parent_class = 0
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
   BEGIN
       print @stmt_refresh_object
       exec sp_executesql @stmt_refresh_object
      FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
   END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO
PRINT 'Metadata update for non-schema-bound objects is done.'
GO
USE [master]
GO

Here the result in Microsoft SQL Server Management Studio:

MSSMS

MSSMS

Leave a Reply