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

Creating VIEWS (from Oracle to SQL Server)

Cordial greetings,

Again i need help with a couple of issues in migrating from Oracle to SQL Server 2005. I need the equivalent sentence in SQL Server of the following sentence:

CREATE OR REPLACE VIEW IBA_MPDATOSGENERALES AS (
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND (IBA_MPACCIONESXREQ.CERRADAAXR = 'S'
OR (IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND ((IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N' AND IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL) OR (IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S' AND IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL))))
)

I know that (+) is for outer joins (left or right). I also know that UNION may mean FULL OUTER JOIN. but this query? The real query is a lot more complex, well actually there are just more fields needed from more tables but with this exmple is enough. The bottom line is that i need data from various tables, most of them involved in a JOIN clause. Also check out that in SQL Server doesnt exists the REPLACE word or does it?
Thank you in advance,

Fernando Martinez

First I want to tell you few things,

1. (+) is for outer join (left/right)

Yes .. You are correct. It always best practice to follow the ANSI standard, it is very simple when you migrate from one database to another. As of now you can delete all the (+) signs and use =* on the corresponding logical expression...

example..

AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO (+)

change it to

AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO =* IBA_MPACCIONESXREQ.IDREQUERIMIENTO

2. Union may mean FULL OUTER JOIN.

Wrong . In any database Union means Union (set operator) only. So you need not to worry about this.

3.REPLACE Word in SQL Server.

Yes. SQL Server doesn't support REPLACE (Create or replace)

Instead of Create or Replace follow these,

When you create the View (at first time) use Create View .. statement.

If you want to replace the existing view (after created) use the Alter View.. statement. (Simple isn't).

I am not interested to change your query. (Its simple for me). Learn your self before doing anything. I know you are a Oracle developer and not interested to learn SQL Server(May be you assigned temp for this migration project ). If you learn the basic difference you can do it your self Buddy..!

All the best.

-Mani

|||

well there is not much alteration in ur query, while shifting it to sql server...

just replace the create or replace by CREATE (while creating the view) , and later replace the CREATE by ALTER when u need to make any changes..(as explained by mani )

(consider the rest as three different queries..union just puts the result together...:::

union vs Join -> union appends the ROWS to the result of first query...while a join will append columns based on some condition u give)

second the plus used in ur case is right outer joins...so replace them with the ANSI syntax...tableA right outer join tableB on tableA.col = tableB.col . (preferred)..else u can also use.. *= (aka += of oracle)for left outer and =* from rite outer join..

u'll get all the info for poring in sql server books online...including sqlserver equivalents (..and their usage) of oracle specific syntax (eg if u encounter rownum or tochar etc)..

|||Thank you guys, i apreciatte your help.