Post reply

List All Views In The Entire SQL Server Instance

  • 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?

  • 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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply