This was asked on twitter the other day and I emailed the person the solution to this. The solution uses dynamic management views and it is not perfect because of a couple of reasons.

1) The dynamic management views don't keep this information forever, restart the server and your data is gone

2) If your table name is in a comment it will be picked up by this query

3) If the table name is part of another object it will also be picked up, for example if you have a table name customer and a view name customers then it will return a row if customers was part of the query but y...

This will not work on SQL Server 2000 since the sp_refreshsqlmodule does not exists on that version!

A while back in the What is deferred name resolution and why do you need to care? blogpost I showed you that sp_depens is not reliable because you can create procedures that reference objects that have not been created yet.

You can use sp_refreshsqlmodule to help 'fix' that

let's take a look at how that works

First create this awesome stored procedur...

Take a look at this code

String or binary data would be truncated. How can I suppress that message?

This is a frequent enough question on various forums, i answered this one also today and decided to create a quick blog post about this.

Run this code

sp_refreshview or how to make sure that the view will have the underlying table changes

Got a question about this on our Microsoft SQL Server Programming Forum so you know it is time for a quick blog post.

Did you know that when you create a view and then later change the table the view is not updated?

let me show you what I mean.

Run the following block of code