Database Diagram of Information_Schema.

  • In the past at the site: http://www.dbmaint.com there was a 'fake' diagram of the Information_Schema. This site doesn't exist anymore.

    Is there somewhere a (current) diagram of the Information_Schema ?

    Ben

  • For SQL Server, you should ignore the INFORMATION_SCHEMA views and use system views instead, such as sys.tables, sys.columns, etc..

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher - Tuesday, January 17, 2017 12:32 PM

    For SQL Server, you should ignore the INFORMATION_SCHEMA views and use system views instead, such as sys.tables, sys.columns, etc..

    The INFORMATION_SCHEMA views are acutyally the sys. views. For example this is the definition of such a view.
    CREATE VIEW [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS] WITH SCHEMABINDING
    AS
    SELECT
    DB_NAME()AS CONSTRAINT_CATALOG,
    SCHEMA_NAME(schema_id)AS CONSTRAINT_SCHEMA,
    nameAS CONSTRAINT_NAME,
    convert(nvarchar(4000), definition)AS CHECK_CLAUSE
    FROM
    sys.check_constraints

    Users are recommended to use the sys. views because of possible changes that could happen in the INFORMATION_SCHEMA views and that could break the backward compatibility.
    However, their usage is just correct.

    Igor Micev,My blog: www.igormicev.com

  • You can restore the mssqlsystemresource database and see the INFORMATION_SCHEMA views definitions.

    USE [master]
    GO
    CREATE DATABASE [mssqlsystemresource_copy] ON
    (FILENAME = N'C:\SqlData\mssqlsystemresource.mdf' )
    FOR ATTACH_rebuild_log


    Then you could probably build a database diagram for the Views (manually), or get some more information.

    Igor Micev,My blog: www.igormicev.com

  • All thanks for your effords.

    But for a number of purposes I do prefer the information_schema. Especially when advising to college's.
    It is less comprehensive and therefore easier to use. Also most views in the information_schema are more geared towards human usage. Columns for example contains the data type, the table_name in human readable format.

    And the 'fake' diagram which did exist (And is still taped to the wall next to me) has been very helpfull, but now I want a link which point to this diagram so others can use the diagram as well. The link I have see my first mail does not 'exist' anymore.

    The diagram was probably build creating tables of all the information schema views, with the relations and then making a diagram from that. That diagram has been proven very handy over the past 10 years. Offcourse using the print of the diagram (on the wall) the diagram can be rebuild. But this was allready done, so why not use e work which is allready done.
    It has been so usefull to me that there must be a place on the internet where it still exists. (Lurking in a dark corner ?)

    Maybe this site is a place to publish such a diagram. If the diagram can not be locatied, I am prepared to rebuild it. And supply it.

    Ben

  • Hi Ben,
    Using the following Google search expression, I was able to find the diagram you're looking for or at least something similar. It looks like there may be several options available.
    filetype:pdf information schema diagram

    https://www.snowflake.net/wp-content/uploads/Info-Schema-with-Descriptions.pdf

    As for merits of using INFORMATION_SCHEMA or SYS views, I tend to use INFORMATION_SCHEMA most of the time when building DDL deployment scripts, because it's an ANSI standard for querying meta-data a SQL database, and SQL Server, Oracle, MySQL, DB2, Postgress all support it. Having occasionally worked with Oracle and other SQL database platforms in the past, I found the consistent presence of INFORMATION_SCHEMA to be useful.
    https://en.wikipedia.org/wiki/Information_schema

    In SQL Server, INFORMATION_SCHEMA is a logical abstraction on top of (and a subset of) the SYS views. I do sometimes fall back to using SYS views for some things like checking for existence of certain types of constraints that arn't covered in the standard.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • ben.brugman - Tuesday, January 17, 2017 3:03 PM

    It has been so usefull to me that there must be a place on the internet where it still exists. (Lurking in a dark corner ?)

    Maybe this site is a place to publish such a diagram. If the diagram can not be locatied, I am prepared to rebuild it. And supply it.

    Ben

    I think that site was the old site for Tibor Karaszi. You could try contacting him through the site he has now - maybe you can ping him and see if he has it around. I looked on the site, his blog and didn't find it. Some of my links haven't been working right on the new site but here is my attempt to insert the link to his new site:
    Tibor Karaszi's SQL Server Pages

    Sue

  • ScottPletcher - Tuesday, January 17, 2017 12:32 PM

    For SQL Server, you should ignore the INFORMATION_SCHEMA views and use system views instead, such as sys.tables, sys.columns, etc..

    No challenge intended here but I'm curious why you make such a recommendation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, January 17, 2017 5:41 PM

    ScottPletcher - Tuesday, January 17, 2017 12:32 PM

    For SQL Server, you should ignore the INFORMATION_SCHEMA views and use system views instead, such as sys.tables, sys.columns, etc..

    No challenge intended here but I'm curious why you make such a recommendation.

    I_S views:
    -- Can't be used to determine schema.  From BOL: "Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view."  These warnings are in every view.  If these warnings aren't true, why keep stating them?!;
    -- Don't contain all the data available in the system views;
    -- Don't provide included index columns, only key columns;
    -- By my experience, for me personally, seem slower and to cause more blocking/deadlocks. Perhaps it's just because the I_S views contain extra info that I don't typically look up when using the system views, or the extra function calls in the I_S views cause issues.

    My feeling is, why bother with I_S views at all if you'll have to use the system view for some things anyway.  Develop your own views that aren't as heavy and/or complex for simpler catalog views.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

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