Back door on Information_Schema

Question

Back door on Information_Schema

Jeff Moden

SSC Guru

Points: 1004363
More actions
March 16, 2008 at 11:04 pm

#183347

There used to be a back door way to make an Information_Schema view in SQL Server 2000 ( see the following URL http://www.sqlservercentral.com/articles/Administering/utilityprocedures/2272/
I don't really care if it's an Information_Schema view or some other view, but is there a way to create a view in Master that would be available from all other databases? I know you can make a stored procedure in Master with sp_ that will do it, but I'd like to know if there's a way to create a view in Master that would automatically be available from all databases and the view would show the "local" table for whatever database is was called from.
Thanks folks.
p.s. Yep... I already know about the recommendations not to do this and why.
--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)

Viewing 15 posts - 1 through 15 (of 16 total)

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

Matija Lah SSCertifiable Points: 6429 More actions · Answer 1

No, you cannot make a view a system object (as you can with procedures). You can create the view in the master database, but you'd still have to reference it using its three-part name ( ).

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736270

April 7, 2008 at 9:00 am

#799222

Create it in model 😉

Matija Lah SSCertifiable Points: 6429 More actions · Answer 3

Steve Jones - Editor (4/7/2008)
Create it in model 😉

That could work. With limitations. 😀

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

I know this is a REALLY ugly trick.... put "SP_" as the prefix. by doing that - it will go try to find the view in MASTER. Yes - the trick works for views too....

I don't think this will change its context, etc... but you don't need three-part naming.....

update: Even tagging it as a "system object" doesn't make a hill of beans of difference..... It uses master as its context....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matija Lah SSCertifiable Points: 6429 More actions · Answer 5

Almost. If there's a view of the same name in the current database then the local view will be used.

Try this:

-- Create view in tempdb

usetempdb

go

create viewdbo.sp_theview

as

selectgetdate() as Today

go

-- Create view in master

usemaster

go

create viewdbo.sp_theview

as

selectnewid() as Guid

go

-- Access view in tempdb

usetempdb

select*

,'from tempdb' as WhereAmI

fromdbo.sp_theview

go

-- Access view in AdventureWorks

useAdventureWorks

select*

,'from AdventureWorks' as WhereAmI

fromdbo.sp_theview

go

-- Drop views

usetempdb

go

drop viewdbo.sp_theview

go

usemaster

go

drop viewdbo.sp_theview

go

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

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

Matt Miller (4/7/2008)
I know this is a REALLY ugly trick.... put "SP_" as the prefix. by doing that - it will go try to find the view in MASTER. Yes - the trick works for views too....

Yeah... that's what I said in my original post... was hoping to stay away from that ugly little trick... Thanks for trying Matt.

--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: 1004363 More actions · Answer 7

Matija Lah (4/7/2008)
Almost. If there's a view of the same name in the current database then the local view will be used.

Thanks Matija Lah... I agree... that's why it's never a good idea to name anything with a prefix of SP_ in any database but Master.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (4/7/2008)
Matt Miller (4/7/2008)
I know this is a REALLY ugly trick.... put "SP_" as the prefix. by doing that - it will go try to find the view in MASTER. Yes - the trick works for views too....
Yeah... that's what I said in my original post... was hoping to stay away from that ugly little trick... Thanks for trying Matt.

There's an even dirtier trick that would work under certain circumstances...but I hate to even mention it (since I think I know the answer I'm about to get:)). You could do it...as a UDF in SQLCLR...

The CLR function can return what database it's being called from so the command can be made contextual to the database you're in.

I know....I'll go and put the dunce hat on now....:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Matt Miller (4/7/2008)
Jeff Moden (4/7/2008)
Matt Miller (4/7/2008)
I know this is a REALLY ugly trick.... put "SP_" as the prefix. by doing that - it will go try to find the view in MASTER. Yes - the trick works for views too....
Yeah... that's what I said in my original post... was hoping to stay away from that ugly little trick... Thanks for trying Matt.
There's an even dirtier trick that would work under certain circumstances...but I hate to even mention it (since I think I know the answer I'm about to get:)). You could do it...as a UDF in SQLCLR...
The CLR function can return what database it's being called from so the command can be made contextual to the database you're in.
I know....I'll go and put the dunce hat on now....:)

That level of detail ain't going to help you much.

In short - it's a CLR version of the dynamic SQL a stored proc might do roughly what this would do

declare @dbname varchar(50)

declare @sql varchar(4000)

set @sql='USE ' +@dbname + '; select whatever from MyTable where MyCriteria=true'

EXEC @sql

except it would be a table var, and would automatically detect what database you're in.

so you could run:

use master

Exec master.dbo.MyCLRFunc();

use tempdb

Exec master.dbo.MyCLRFunc();

use model

Exec master.dbo.MyCLRFunc();

and assuming that the three DB's have what it takes to run this - you'd get distinct results based on what database you're in when calling the function in MASTER.

So - it's context-sensitive, yes, and it's table-like, but its context sensitivity works a little differently from the DMV's and catalog views.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 10

Yikes!

Just put it in the Version Control script for database creation 🙂

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Matt Miller (4/7/2008)
That level of detail ain't going to help you much.

Matt... thanks for the help. You're right, though... not a big fan of CLRs but could be convinced if the performance is equal to a view in Master. So, the next question would be, how's the performance on such a thing?

--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: 1004363 More actions · Answer 12

Steve Jones - Editor (4/7/2008)
Yikes!
Just put it in the Version Control script for database creation 🙂

Hi Steve,

As with Matt, I always appreciate it when you jump in on one of my questions...

Are you saying to add a view to everydatabase? In this case (not my design... it's 3rd party design I can't change), I have almost 500 "CDR Usage" databases that I'd be trying to add the view to and then have to maintain if it changes. While it's certainly simple enough to do that, I was hoping to not have to do such a thing. The old method available in 2k of adding a custom Information_Schema view would have been perfect if I could get it working in 2k5.

If the "Version Control Script" you speak of is something different, what are you talking about?

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Jeff Moden (4/7/2008)
Matt Miller (4/7/2008)
That level of detail ain't going to help you much.
Matt... thanks for the help. You're right, though... not a big fan of CLRs but could be convinced if the performance is equal to a view in Master. So, the next question would be, how's the performance on such a thing?

Well - from the outside it's going to look just like a T-SQL function, meaning what comes back is a table variable. Meaning - it shouldn't be all that ugly unless you're talking about thousands upon thousands of rows.

I'd just be curious what you need to implement, and possibly how you'd want to use it. If you have a query in mind - post it, or PM me if you would prefer to. It's not that fancy a thing (it's a "straight" SQLcommand object being fed a database name, and returning the rows it returns)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

It would be an aggregate query to sum all the CDRs (Call Detail Records for Telephone Billing) for a about 120 thousand customers in a 4-5 million row table. I'm thinking a table variable would fall on the floor and break. Not a problem and I believe I'm all set... I have some experimentation to do with sp_ views.

I sure do appreciate your help though, Matt. Thanks.

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