Creating views under INFORMATION_SCHEMA in SQL 2005

Hello to everyone,

I have a couple of useful views created under INFORMATION_SCHEMA in SQL 2000. Now I am trying to create them in SQL 2005 environment. As an example simply run the following code as "sa" in SQL 2005 (it works in SQL 2000):

CREATE VIEW INFORMATION_SCHEMA.MyTABLES
AS
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

In my environment I received the following error:

Msg 2760, Level 16, State 1, Procedure MyTABLES, Line 3
The specified schema name "INFORMATION_SCHEMA" either does not exist or you do not have permission to use it.

I tried to create the view under other schema and change the owner to INFORMATION_SCHEMA and some other techniques that worked in SQL 2000 (as a direct update of sysobjects). Also I tried (using the system stored procedures as well as the GUI) to change the login/user/schema chain in order to overcome the problem with no success. Please let me know if you can run the above code successfully or if you have any other suggestions.

Peter Petrov

Hi,

The information views are not intended to be extendible. The Information Schema is an ANSI specification for obtaining metadata from your database. Since it is a standard it is ment to be used across multiple types of databases.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Thank you, Geert.

We all know that but sometimes as we also know we need some of the benefits of the system objects for temporary “relief”. So the question is not “To do or not to do that?” but “How we can do that?”. That’s why the “back doors” exist J