VIEW_TABLE_USAGE SQL 2005

5 pts.
Tags:
SQL
SQL 2005
In MS SQL 2005, when I select * from VIEW_TABLE_USAGE, I get incomplete results. At least one object that is used in at least one of the views is not listed. View "PTP__MARA_MARC" uses two tables and one view, but VIEW_TABLE_USAGE only lists one table and one view.  Can you please help?

Software/Hardware used:
Microsoft SQL 2005

Answer Wiki

Thanks. We'll let you know when a new response is added.

The INFORMATION_SCHEMA.VIEW_TABLE_USAGE uses the sys.sql_dependencies catalog view to figure out what the objects that are used are. If a table was renamed or dropped and recreated then sys.sql_dependencies won’t be correct, and VIEW_TABLE_USAGE won’t be correct.

Try altering the view which isn’t returning correctly to rebuild the information in sys.sql_dependencies.

[kccrosser] You can also try:

<pre>exec sp_refreshview ‘viewname';</pre>

This will cause the metadata around the view to be updated.

FYI – I also commonly use sp_refreshsqlmodule when trying to build dependency trees of stored procedures and functions. Otherwise the dependency relations are often stale and fail to reflect new compiles, drops, and alters.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Do you have permissions on the missing table ? VIEW_TABLE_USAGE only returns information about the objects to which the user has permissions.
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following