Custom system views

• Chris Chilvers

  SSCrazy

  Points: 2260

  More actions

  March 13, 2008 at 9:05 am

  #120766

  I was wondering if it was possible to create custom views in the master database that can be used from any other database.

  The reason for this is that I have several views that query custom extended attributes from tables and columns used for documentation (e.g. MS_Description).

  Currently I have to create these same views for every database, and when making updates to the views, remember to update every database.

  I would rather create one single set of views in the master database that can run against any database, similar to the information_schema views.

  Anyone have any ideas on if this is possible?

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  March 13, 2008 at 9:19 am

  #789137

  I would assume it is possible, although I do not know if you can add then to the sys sheba. I have added custom SPs to master for the same reasons. Of course I kept a copy of the create scripts elsewhere in case service packs or upgrades blasted them.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Chris Chilvers

  SSCrazy

  Points: 2260

  More actions

  March 17, 2008 at 3:45 am

  #790253

  Interestingly I can get it to work if I prefix the name of the view with sp_ but unfortunatly that doesn't work when I try to put the views under their own schema.

• Jeff Moden

  SSC Guru

  Points: 1004581

  More actions

  March 17, 2008 at 6:54 am

  #790301

  There's a back door that works in 2000, but the same method fails a permissions test in 2k5. Haven't found a way around it yet...

  Funny how things are... I think 4 of us have all asked a similar question in the last 24 hours...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)