January 17, 2017 at 10:08 am
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
January 17, 2017 at 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..
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 17, 2017 at 1:05 pm
ScottPletcher - Tuesday, January 17, 2017 12:32 PMFor 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
January 17, 2017 at 1:07 pm
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
January 17, 2017 at 3:03 pm
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
January 17, 2017 at 3:47 pm
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
January 17, 2017 at 4:21 pm
ben.brugman - Tuesday, January 17, 2017 3:03 PMIt 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
January 17, 2017 at 5:41 pm
ScottPletcher - Tuesday, January 17, 2017 12:32 PMFor 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
Change is inevitable... Change for the better is not.
January 18, 2017 at 9:06 am
Jeff Moden - Tuesday, January 17, 2017 5:41 PMScottPletcher - Tuesday, January 17, 2017 12:32 PMFor 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply