information_schema views in system procedures

Question

information_schema views in system procedures

vr8ce

SSC-Addicted

Points: 410
More actions
May 12, 2004 at 8:25 am

#179640

I would like to use the information_schema (hereafter called i_s) views instead of querying system tables directly, but the i_s views don't appear to do the magic that the system tables do when run from master.
For example, the following stored procedure, when compiled in master but run in pubs, yields two different result sets:
use master
go
CREATE PROCEDURE sp__test
AS
SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE'
SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name
Go
use pubs
go
exec sp__test
go
So, how do we use the i_s views from a system stored procedure? I can use dynamic SQL to prefix the db_name() to the query, but that seems to be a lot more trouble than using the system tables, especially when I need to get data from the i_s views rather than just issuing a query as above.
Thanks,
Vince

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

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

Chris Luscik SSC Veteran Points: 276 More actions · Answer 1

Don't know if the following will help as you mentioned that you don't want to use dynamic SQL. I've run the procedure above, but made the first statement dynamic, i.e.

EXEC('SELECT * FROM information_schema.tables WHERE table_type = ''BASE TABLE''')

This seems to use the current database. When you select data from the information_schema.tables view in a stored procedure, it uses the sysobjects table from the local database and not the active one.

Peter P SSCrazy Points: 2777 More actions · Answer 2

have you tried using the fully qualified names in your query?

vr8ce SSC-Addicted Points: 410 More actions · Answer 3

Well, it's really two things. One, having to use dynamic SQL seems like it's pretty unintuitive, so I wanted to make sure I wasn't doing something wrong. I don't really understand why MS would have the one work that we're not supposed to use and the one not work that we are supposed to use. But it is MS, so who are we to question why?

Second, I don't know if dynamic SQL works without getting really complex. For example, say I want to get the above tables in a variable. Or, more likely, say I want to get a subset of the tables in a variable (i.e. put a WHERE clause on the SELECT). I can add the where clause to the SQL, but how am I going to get the output into a variable, *easily*?

Thanks,

Vince