List views owned by a particular schema

• Jim

  SSCrazy Eights

  Points: 8718

  More actions

  June 19, 2008 at 3:26 pm

  #122827

  I have searched but can't find how to list views owned by a particular schema. It is no longer in use and I would like to drop the schema but I can't since it owns 4,000+ views. I have to script this out as part of our conversion process so I'm thinking I'll probably use a simple cursor to delete them all. I just need to know how to identify them.

  Thanks!

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  June 19, 2008 at 3:34 pm

  #831440

  This will list them all:

  select *

  from Information_Schema.Tables

  Where Table_Type = 'View'

  And Table_Schema = '-Your Schema Name-'

  Of course, if you want to Delete them, you don't have to use a cursor. this will do it just fine:

  Declare @sql varchar(max)

  Select @sql = ''

  Select @Sql = @sql + '

  Drop View ['+Table_Schema+'].['+Table_Name+']'

  from Information_Schema.Tables

  Where Table_Type = 'View'

  And Table_Schema = '-Your Schema Name-'

  EXEC (@sql)

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]