Click here to Skip to main content
12,747,151 members (27,894 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


1 bookmarked
Posted 16 Dec 2011

Getting a list of SQL Server modules to refresh

, 25 Dec 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Using a Common Table Expression to produce a list of Views, Procedures, and Functions
If you have a table and a view based on that table, and alter the table (add a column perhaps), then you will need to refresh the view. This can be done with the sp_refreshview or sp_refreshsqlmodule procedure.

If you have many views, procedures, and functions that refer to each other, then picking which ones to refresh in which order may be a daunting task. When I was put in this situation recently, I decided to write a utility to refresh everything. The biggest task was to produce a list of all the modules with the bottom-most modules first.

A recursive Common Table Expression seemed the way to go, so I wrote this:

  SELECT DISTINCT lft.referenced_id [object_id]
  , 0 [Rank]
  FROM sys.sql_expression_dependencies lft
  LEFT OUTER JOIN sys.sql_expression_dependencies rgt
  ON lft.referenced_id=rgt.referencing_id
  WHERE rgt.referencing_id IS NULL
  SELECT lft.referencing_id [object_id]
  , [Rank] + 1
  FROM sys.sql_expression_dependencies lft
  INNER JOIN cte rgt
  ON lft.referenced_id=rgt.[object_id]
, so.[type]
, c.[Rank]
  SELECT [object_id]
  , MAX([Rank]) [Rank]
  FROM cte 
  GROUP BY [object_id]
) c
INNER JOIN sys.objects so 
ON c.[object_id]=so.[object_id]
WHERE so.[type] IN ( 'V' , 'P' , 'FN' ) 
ORDER BY c.[Rank],so.Name,so.[type]

This produces the list of modules; you can iterate it and execute sp_refreshsqlmodule on each in turn.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
United States United States
BSCS 1992 Wentworth Institute of Technology

Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.

OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged pedant and contrarian


"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]

"Typing is no substitute for thinking." -- R.W. Hamming

"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup

ZagNut’s Law: Arrogance is inversely proportional to ability.

"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon

"linq'ish" sounds like "inept" in German -- Andreas Gieriet

"Things would be different if I ran the zoo." -- Dr. Seuss

"Wrong is evil, and it must be defeated." –- Jeff Ello

"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw

“It’s always easier to do it the hard way.” -- Blackhart

“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart

"Omit needless local variables." -- Strunk... had he taught programming

You may also be interested in...

Comments and Discussions

GeneralReason for my vote of 5 That's a time saver I'll steal Pin
Duke Carey20-Dec-11 2:12
memberDuke Carey20-Dec-11 2:12 
GeneralRe: Oh man, someone stole my tip! :( Pin
PIEBALDconsult21-Dec-11 17:53
memberPIEBALDconsult21-Dec-11 17:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170215.1 | Last Updated 25 Dec 2011
Article Copyright 2011 by PIEBALDconsult
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid