Creating views under INFORMATION_SCHEMA in SQL 2005

Question

Creating views under INFORMATION_SCHEMA in SQL 2005

Peter Petrov

SSCommitted

Points: 1517
More actions
January 12, 2007 at 4:34 pm

#122721

I had a couple of views under INFORMATION_SCHEMA in SQL 2000 and I am having troubles to create them in SQL 2005. I have tried to create them directly (as "sa"), to create under “dbo” and change the object owner and even to update sysobject directly. Also I tried to use the “Server Management Studio” with no success.
Any suggestions? Thank you in advance...
Peter Petrov
January 15, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Peter

I'm not sure what it is you're trying to do. Information_schema views are built in so you can't create them. Please post the SQL that you are trying to run (or talk us through it if you're doing it in the GUI).

John

Lowell SSC Guru Points: 323518 More actions · Answer 2

that's not true; I've created lots of information_schema views in sql 2000;

like Peter outlined, you simply create a view in master and change the owner to INFORMATION_SCHEMA; I've done this myself to create schemas that have more detailed index information,foreign keys, as well as some that contain the table DDL's using a function I wrote.

he;s having trouble in 2005, but I'm waiting to see why.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Peter Petrov SSCommitted Points: 1517 More actions · Answer 3

Thank you both for your postings. May be my description of the problem wasn't clear. I don't have any problems creating views under INFORMATION_SCHEMA in SQL 2000 at all. The problem arose when I tried to create them in SQL 2005 environment. You can simply try to run the following code (as "sa") in SQL 2005:

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

Thanks again.

Peter Petrov

Lowell SSC Guru Points: 323518 More actions · Answer 4

peter I'm having a similar issue when i try to do it as well in my 2005 test server.

I never created my info views based on other views, so in your example, on 2000, i would have used the following to create it. it fails in 2005 with the same error you raise when i try to change object owner, or if I tried top create it directly as 'INFORMATION_SCHEMA.MYTABLES'

create view MYTABLES

as

select distinct

db_name() as TABLE_CATALOG

,user_name(o.uid) as TABLE_SCHEMA

,o.name as TABLE_NAME

,case o.xtype

when 'U' then 'BASE TABLE'

when 'V' then 'VIEW'

end as TABLE_TYPE

from

sysobjects o

where

o.xtype in ('U') and

permissions(o.id) != 0

GO

sp_changeobjectowner 'dbo.mytables','INFORMATION_SCHEMA'

GO

SELECT * FROM INFORMATION_SCHEMA.MYTABLES

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Peter Petrov SSCommitted Points: 1517 More actions · Answer 5

Lowell,

Thank you for your response. I brought my example just for simplicity. The viewes that I am using looks something like this one (there are some more user tables involved):

CREATE

VIEW INFORMATION_SCHEMA.My_Table_Indexes

AS

SELECT DB_NAME() AS DATABASE_NAME

, OBJECT_NAME(i.[id]) AS TABLE_NAME

, i.[id] AS TABLE_ID

, i.name AS INDEX_NAME

, i.indid AS INDEX_ID

, g.groupname AS FILE_GROUP

, INDEXPROPERTY(i.id, i.name, 'IsUnique') AS IsUnique

, INDEXPROPERTY(i.id, i.name, 'IsClustered') AS IsClustered

, COALESCE(OBJECTPROPERTY(OBJECT_ID(i.name),'IsPrimaryKey'),0) AS IsPrimaryKey

, COALESCE(OBJECTPROPERTY(OBJECT_ID(i.name),'IsUniqueCnst'),0) AS IsUniqueCnst

FROM sysindexes i

JOIN sysfilegroups g ON g.groupid = i.groupid

WHERE i.[indid] BETWEEN 1 AND 250

AND 1 NOT IN (INDEXPROPERTY(i.id, i.name, 'IsStatistics'), INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics'))

AND COALESCE(i.name,'') NOT LIKE 'index[_][0-9]%'

AND COALESCE(i.name,'') NOT LIKE '[_]WA[_]Sys[_]%'

AND OBJECT_NAME(i.[id]) NOT LIKE 'sys%';

GO

Thanks again.

Peter Petrov.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Peter

This may help you:

http://msdn2.microsoft.com/en-gb/library/ms173423.aspx

Since schemas and owners are different concepts in SQL Server 2005, you shouldn't try to change the "owner" of a view to information_schema. And if you use the ALTER...TRANSFER syntax, you can't specify information_schema as the schema to alter.

John