SQL Server Views - INFORMATION_SCHEMA.VIEW_TABLE_USAGE

Question

When i query INFORMATION_SCHEMA.VIEWS it list all views but when i query INFORMATION_SCHEMA.VIEW_TABLE_USAGE it displays only few views. How can i rebuild all the views info in INFORMATION_SCHEMA.VIEW_TABLE_USAGE?

Answer 1

You can export the entire database as a script (right click database -> Tasks -> Generate Scripts). Running that script in a new database will generate the correct dependencies. You can then pump over the data from the old database. I'd expect that to be a lot of work.

There are commercial tools, like RedGate SQL Dependency Tracker, that make this much easier. They have a free demo, but be warned, you'll grow dependent on those tools quickly :)

Answer 2

Edit Actually I just noticed that you are only concerned with Views not sysdepends in general. It should be clear how to amend the below accordingly!

Original Answer In Management Studio you can script all stored procedures, functions, and views to a new query window then in the "Find and Replace" dialogue select "case insensitive" and "use regular expressions"

In the "Find What" enter

SET QUOTED_IDENTIFIER {(ON|OFF)}\nGO[:b\n]+CREATE[:b\n]+{(PROC|FUNCTION|VIEW)}

In the "Replace with" enter

SET QUOTED_IDENTIFIER \1\nGO\nALTER \2

This will generate an ALTER script for all stored procedures, functions, and views. Running this will recreate all dependencies.

There is a very small risk of this messing something up though if (say) you had a dynamic SQL string with the "find" text in. Also it will cause your procedure cache to be flushed as you are altering all the procedures.

Answer 3

You can simply call sp_refreshsqlmodule or sp_refreshview on non-schemabound views.

Also, this answer