CODEWITHSUNDEEP
t-sqlsql-server-2012
Question3CPO
Question3CPO

Reputation: 1202

Table or View For System Views

Problem: I'm using SQL Server 2012 Enterprise Management Studio and Intellisense is working on and off. It varies by the day, provided that I'm connected to a 2012 server and instance. When I'm connected to a 2008R2 server and instance, it consistently functions appropriately (absurd). Aaron Bertrand wrote an absolutely awesome article here about ways we can troubleshoot this in 2012. I've gone through the checklist and everything should be done correctly, meaning this probably fits the last item (since it works on and off), though it working fine on 2008R2 seems odd.

Question: Instead, I am just going to use scripts to look through objects, but am not finding a script to look at all the system views options, so that I can perform like queries to find if there's anything related. For instance:

sys.assemblies
sys.assembly_files
sys.assembly_modules
sys.assembly_references
sys.assembly_types

If I were searching for:

SELECT *
FROM sys.[TheSourceI'mSeeking]
WHERE name LIKE '%assembl%'

This way I could quickly look through the available views that I have (which is where intellisense can really help) instead of opening everything. When I look through sys.objects, I don't find system views - is there another way around this?

Answer (I think this is it):

SELECT *
FROM sys.all_views
WHERE is_ms_shipped = 1

Upvotes: 0

Views: 60

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

System views are stored in sys.all_views.

If you want to search the code of the views for a specific reference, you can do:

SELECT v.name, m.definition
  FROM sys.all_views AS v
  INNER JOIN sys.all_sql_modules AS m
  ON v.[object_id] = m.[object_id]
  WHERE v.is_ms_shipped = 1
  AND m.definition LIKE '%assembl%';

Of course, this will return every view that references an object named assembl% and also every view that has that name. If you want to filter out the latter:

  AND v.name NOT LIKE '%assembl%';

But I'm not quite sure what your goal is.

Upvotes: 1

Related Questions