List All Views In The Entire SQL Server Instance

• curious_sqldba

  SSC-Dedicated

  Points: 36502

  More actions

  August 25, 2010 at 7:31 pm

  #223110

  Is there any dmv which can tell me all the views,sp's in a particular instance. I have a script where my cursor loops through different servers and different databases. i need to find out list of views on all of these and have in one table. The table should have servername,database name,view name,create_date,modifydate. Any idea on how this can be done?

• WayneS

  SSC Guru

  Points: 95461

  More actions

  August 25, 2010 at 10:16 pm

  #1212045

  iqtedar (8/25/2010)

  ---

  Is there any dmv which can tell me all the views,sp's in a particular instance.

  No

  I have a script where my cursor loops through different servers and different databases. i need to find out list of views on all of these and have in one table. The table should have servername,database name,view name,create_date,modifydate. Any idea on how this can be done?

  How is this:

  if object_id('tempdb..#test') IS NOT NULL DROP TABLE #test

  CREATE TABLE #test (

  ServerName sysname,

  DatabaseName sysname,

  Name sysname,

  CreateDate datetime,

  ModifyDate datetime);

  execute sp_MSforeachdb '

  insert into #test

  select @@servername, db_name = ''?'', name, create_date, modify_date

  from [?].sys.views

  UNION ALL

  select @@servername, ''?'', name, create_date, modify_date

  FROM [?].sys.procedures'

  select * from #test

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2