Can I create my own System View?

Question

Can I create my own System View?

RBarryYoung

SSC Guru

Points: 143329
More actions
March 16, 2008 at 6:00 pm

#188052

Can anyone tell me if it is possible for me to create my own system view, similar to how a custom stored procedure would work?
This would be a view that resides in Master, but appears in every database and runs there as though it were local.
[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]

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

Absolutely... I have the steps to do it somewhere but it's been a 1000 years since I've done it... let me look for it...

--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)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

I found it... it's an article on this forum by a good fellow by the name of David Poole... I had it in my "favorites"...

http://www.sqlservercentral.com/articles/Administering/utilityprocedures/2272/

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Thanks! I've added it to my favorites too.

[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]

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

Ack... sorry Barry... the darned thing doesn't work in 2k5... gives a permissions error. I'm trying to find a back door but it doesn't look promising yet.

--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)

EdVassie SSC Guru Points: 60478 More actions · Answer 5

You cannot change the permissions on any system object in SQL Server 2005 & 2008. This is because the permissions are held in the mssqlsystemresource db, which you cannot access via T-SQL.

If you wanted to void your MS support, you could try copying mssqlsystemresource to another name, attacht it, make the changes, stop SQL, copy your new db on top of mssqlsystemresource, and hope everything works OK. It it does not, take comfort in that you deliberately decided to void your support.

Another option is to get your data via a sp in master. Start the proc with letters 'sp_' and you can call it from any DB. e.g. EXEC mydb..sp_in_master and sp_in_master will run from the master db but use the context of mydb.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

Yeah... I was thinking about the sp_ thing... maybe use it to create a temp table and use it as if it were a view.

--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)