Post reply

extracting non-system views

  • May 16, 2006 at 8:14 am

    #117568

    Hi, I need some help trying to extract a list of non system views from my database. I am creating a script to automate the process of dropping all user created views. The following query extracts a set of all the views from my database.

    select o.name from sysobjects o where o.type = 'v'

    this query returns the syssegments and sysconstraints views which i am not interested in. I just want a list of the user created views only.

    I hope someone can help!

  • May 16, 2006 at 8:34 am

    #638172

    Here's the quick solution:

    select o.name from sysobjects o where o.type = 'v'

    and o.name not in ('syssegments', 'sysconstraints')

    Haven't looked too close on them, but if these are flagged as userobjects, it isn't the first time that some 'system stuff' shows up among the 'user stuff'. Easiest is just to filter them out.

    /Kenneth

  • May 17, 2006 at 10:43 am

    #638505

    You can also add:

    and crdate not between X and Y

    add o.crdate to your select and you'll see what to do.

    Hope this helps!

    Gene

     

  • May 18, 2006 at 1:50 am

    #638645

    Thanks for your help guys, I got it sorted.

Viewing 4 posts - 1 through 3 (of 3 total)

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