locked
why cant i create a table in schema's like sys and information_schema? RRS feed

  • Question

  • hi all,

    actually i was trying to do something new to me. Question here is 

    why cant i create a table in schemas Information_Schema, Sys but where i can create tables in other schemas like Guest, db_accessadmin, db_datareader, db_datawriter, db_denydatareader, db_denydata_writer...?

    Can some please explain me how these schemas are different from other system (in-built) schemas?

    Thanks.

    Saturday, January 12, 2013 10:25 AM

Answers

  • These schemas are reserved for internal use for SQL Server. The other schemas just regular schemas and with the exception of the guest schema you can drop them if you like. (They exist for backwards compatibilty with SQL 2000 only.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kalman Toth Saturday, January 12, 2013 12:04 PM
    • Marked as answer by Kalman Toth Saturday, January 19, 2013 2:37 PM
    Saturday, January 12, 2013 11:27 AM

All replies

  • These schemas are reserved for internal use for SQL Server. The other schemas just regular schemas and with the exception of the guest schema you can drop them if you like. (They exist for backwards compatibilty with SQL 2000 only.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kalman Toth Saturday, January 12, 2013 12:04 PM
    • Marked as answer by Kalman Toth Saturday, January 19, 2013 2:37 PM
    Saturday, January 12, 2013 11:27 AM
  • Thanks a lot for ur response Erland.

    But do any object exists in those schemas? if not, what is the point of creating those schemas in every user_defined / system DB's?

    why cannot i drop Guest schema, what is the importance of it?

    Thanks.



    Saturday, January 12, 2013 8:02 PM
  • Yes, there are objects in these schemas in every database, since this is where the metadata resides. Then there some some special magic for these schemas, since the object definitions to a great extent are in the hidden resource database.

    You can think of these schemas as that door at the museum which has a sign which says "STAFF ONLY".

    I don't know why you cannot drop that schema. It is the default schema for the guest user, but you can change the default schcma of guest do, but you can still not drop the guest schema. Maybe they put in that check for some backwards compatibilty thing. Up to SQL 2000, owner and schema was the same thing, and for this reason there was a schema for each user or group. (But no one called it a schema in those days.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 12, 2013 8:22 PM
  • >why cannot i drop Guest schema, what is the importance of it?

    Generally it is not a good idea to change things which Microsoft ships. The reason: everybody (the entire SQL Server community) gets the same and aware of it.

    For example, Microsoft calls the the main database file .mdf . That is not a requirement, you can name it .xzy . The net result of such a change: confusion & loss of productivity.


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, January 12, 2013 10:09 PM