Creating views under INFORMATION_SCHEMA in SQL 2005

  • Peter Petrov

    Default port

    Points: 1467

    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.< ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

    Any suggestions? Thank you in advance...

    Peter Petrov

  • This was removed by the editor as SPAM

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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: 323459

    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

    Default port

    Points: 1467

    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: 323459

    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

    Default port

    Points: 1467

    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: 148769

    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

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

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