Information Schema views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Information Schema views

Author

Topic

vr8ce
Starting Member

23 Posts

Posted - 2005-10-26 : 18:36:49

USE master
go
CREATE PROC dbo.sp__demo
AS
SELECT name FROM sysobjects WHERE type = 'U' ORDER BY 1
go

If the above procedure is compiled, then the database from which the SELECT is done is changed according to the EXEC line, i.e.
EXEC sp__demo
runs from the current database, while
EXEC otherdb..sp__demo
runs from the database 'otherdb'.

But, we're not supposed to be access the sys tables directly, instead using the INFORMATION_SCHEMA views. I've tried several ways to accomplish the above using them, but I haven't figured out how to do it without using dynamic SQL, e.g.

CREATE PROCEDURE sp__demo2
AS
DECLARE @db varchar(60), @sql varchar(2000)
SELECT @db = db_name()
SELECT @sql = 'SELECT table_name FROM ' + @db + '.information_schema.tables WHERE table_schema = ''dbo'' ORDER BY 1'
EXEC(@SQL)
go

That's not too bad (but it's not great, either), given this simple example, but when joining multiple sys tables together, doing subqueries, etc., it gets ugly (and impossible) in a hurry. My question is, am I missing something? Is there an easier way to accomplish this, specifically creating a "master" procedure that uses the info schema views and works when run for any database?

Thanks!

Vince

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-26 : 18:44:23

Why even bother wrapping this into a stored procedure? Just run the SELECT statement outside of it from within the database you wish to query and it'll bring back the data that you need for that database. That's how the INFORMATION_SCHEMA views are designed.

Tara

vr8ce
Starting Member

23 Posts

Posted - 2005-10-26 : 18:55:22

As I said in my original post, the supplied proc was just a simple example for purposes of discussion. The procedures I'm talking about are 10s or 100s of lines, with lots of other T-SQL, multi-(sys)table joins, sub-queries, etc.

Vince

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-26 : 19:03:47

I understand that. You need to use dynamic SQL to do what you are trying to do. Personally, I'd just run the queries without the stored procedure. You could always save the queries to a central location so that more than one person can access them.

Tara

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 01:40:00

"creating a "master" procedure that uses the info schema views and works when run for any database?"

Well ... if you call it "sp_YourName" and put it in the "master" database it will run when executed from any database, in the context of that database. Not sure if that's what you want, but if so I think that might be the answer!

Kristen

vr8ce
Starting Member

23 Posts

Posted - 2005-10-27 : 11:03:10

quote:
Originally posted by Kristen
Well ... if you call it "sp_YourName" and put it in the "master" database it will run when executed from any database, in the context of that database. Not sure if that's what you want, but if so I think that might be the answer!

No, not in the context of my original message. Please re-read the entire message. Thanks.

vr8ce
Starting Member

23 Posts

Posted - 2005-10-27 : 11:07:17

quote:
Originally posted by tduggan
Personally, I'd just run the queries without the stored procedure. You could always save the queries to a central location so that more than one person can access them.

Again, it's impossible to "save the queries". They are *stored procedures*, some of which have hundreds of lines. Those stored procedures have queries in them involving the sys tables.

So, the upshot is that it's impossible to duplicate the functionality of using the sys tables using the information schema views, and therefore IMO it's specious of MS to say "don't use the sys tables" when they're taking away significant functionality in doing so.

Vince

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-10-27 : 11:17:28

quote:
therefore IMO it's specious of MS to say "don't use the sys tables" when they're taking away significant functionality in doing so

They've been recommending that you NOT use the system tables for quite a few years. And especially since SQL Server 7.0 was released, they have been adding system views and functions that provide all of the information you could get from the system tables (OBJECTPROPERTY, INDEXPROPERTY, etc.) It's far more specious for you to say they're "taking away" anything, they've been adding all kinds of stuff you simply haven't taken advantage of.

It would help if you posted the actual code you're trying to reproduce, it would make it easier for use to provide a solution.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-27 : 12:31:17

quote:

Again, it's impossible to "save the queries". They are *stored procedures*, some of which have hundreds of lines. Those stored procedures have queries in them involving the sys tables.

I guess I don't understand then. All code in a stored procedure can be saved as a query.

Tara

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 14:23:54

"Please re-read the entire message"

Well in that case I have no idea what this bit means:

"specifically creating a "master" procedure that uses the info schema views and works when run for any database?"

a sproc called "sp_YourName" created in the master database will do just that, won't it?

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-27 : 15:06:08

Yes, however the INFORMATION_SCHEMA views don't work as he expects them to when run from a storeed procedure inside master. It does work if he goes directly to the system objects though. Hence, the problem and why he needs to use dynamic SQL as a work around.

Tara

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 20:29:53

OK, but from what I've seen so far the only thing the dynamic SQL is doing is putting the name of the DB into the query string.

Wouldn't an sp_YourName in master allow the INFORMATION_SCHEMA views to be referenced (without explicit database name) and actually reference them in the current database?

Maybe there's something about INFORMATION_SCHEMA views specifically that I'm missing in this context.

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-28 : 00:23:26

Try this:

USE master
GO

CREATE PROC sp_Test
AS

SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

USE pubs
GO

EXEC sp_Test

USE master
GO

DROP PROC sp_Test

You will return the tables in the master database rather than pubs.

Now try this:

USE master
GO

CREATE PROC sp_Test
AS

SELECT * FROM sysobjects WHERE type = 'U'
GO

USE pubs
GO

EXEC sp_Test

USE master
GO

DROP PROC sp_Test

In the first run, you'll see the tables in the master database. In the second run, you'll see the tables in the pubs database.

Hence, his problem. He needs to use dynamic SQL due to this.

NOTE: This code was written on the fly without testing it. I did test a similar version of it earlier today though, so it should work as explained.

Tara

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 04:31:35

You should create that sp in the Database you are working with than Master database.

Information_schema.tables will list the tables in which Database it is currently run

If you want it to run in Master database and expect it to return tables from other database then you need to supply database Name using Dynamic SQL. To avoid this why dont you create sp in the actual database itself?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-28 : 07:09:52

"In the first run, you'll see the tables in the master database. In the second run, you'll see the tables in the pubs database"

Ah, I see it now, thanks for that Tara. Seems a bit daft to me, "sp_test" should be running in the context of the current database for all resources :-(

Is there any sort of OPENQUERY type syntax that would run in the current database scope? (Although I'm not sure that's any different to dynamic SQL in practice)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 07:16:15

>>"sp_test" should be running in the context of the current database for all resources :-(

You expected it as that of sp_help. Isnt it Kris?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-28 : 07:25:30

"You expected it as that of sp_help. Isnt it Kris?"

Basically .... Yes!

vr8ce
Starting Member

23 Posts

Posted - 2005-10-28 : 08:06:55

quote:
Originally posted by robvolk
They've been recommending that you NOT use the system tables for quite a few years. And especially since SQL Server 7.0 was released, they have been adding system views and functions that provide all of the information you could get from the system tables (OBJECTPROPERTY, INDEXPROPERTY, etc.) It's far more specious for you to say they're "taking away" anything, they've been adding all kinds of stuff you simply haven't taken advantage of.

I know how long they've been recommending it, but that has little to do with anything. I *can't* take advantage of those views/functions, hence the post. With the Sys tables I have a very important capability (database specified on the command line specifies which databases sys tables are referenced in the sproc), and that capability does not exist with the system views and functions.

quote:
Originally posted by robvolkIt would help if you posted the actual code you're trying to reproduce, it would make it easier for use to provide a solution.

I did provide a sample.

vr8ce
Starting Member

23 Posts

Posted - 2005-10-28 : 08:10:29

quote:
Originally posted by madhivanan

You should create that sp in the Database you are working with than Master database.

Information_schema.tables will list the tables in which Database it is currently run

If you want it to run in Master database and expect it to return tables from other database then you need to supply database Name using Dynamic SQL. To avoid this why dont you create sp in the actual database itself?

That may be what we end up doing, but that is still a significant downgrade from the current situation, where one procedure installed in master will suffice, instead of having to install that one procedure in eight test databases on the same server.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-10-28 : 08:11:31

I didn't ask for a sample, Please re-read the entire message. Thanks.

vr8ce
Starting Member

23 Posts

Posted - 2005-10-28 : 08:13:59

quote:
Originally posted by robvolk

I didn't ask for a sample, Please re-read the entire message. Thanks.

I did read the entire message, you might want to re-read the entire thread. I think we can stop this merry-go-round now. All of the messages have proved my point, we can't do with the system views what we can currently do with the system tables. That's all I wanted to know.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources