INFORMATION_SCHEMA views

  • Just curious, how many of you use the INFORMATION_SCHEMA views?  Personally I prefer to use the DMVs like sys.tables, sys.columns, etc., but I find myself reviewing code written by someone else that seems to want to use the INFORMATION_SCHEMA views.

  • Lynn Pettis - Tuesday, August 8, 2017 11:02 AM

    Just curious, how many of you use the INFORMATION_SCHEMA views?  Personally I prefer to use the DMVs like sys.tables, sys.columns, etc., but I find myself reviewing code written by someone else that seems to want to use the INFORMATION_SCHEMA views.

    I occasionally use the INFORMATION_SCHEMA views. As they don't have the whole information, I tend to use the system views. However, I wouldn't reject any code if it uses the IS views.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I never use the I_S views.  I've found them to be very slow and to cause blocking at times.  I know their official view definitions don't show why that would be the case, I just know that it actually is.  There's nothing in them you can't get from the current SQL Server system views.  Portability is not really realistic anyway, which would be the only reason I could see for claiming to want to stick to the I_S views.

    If you like their naming, just create equivalent views of your own under a 'dbo' or other schema.

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

  • Not sure about the portability of INFORMATION_SCHEMA views.  Worked with Oracle for a year and never saw them there, not even a mention in the documentation I had.  Maybe I just didn't look in the right places.
    I prefer the sys views as you can actually get more information from them.

  • Used the IS views across several different platforms, IIRC even SQLite.
    😎

  • I believe the I_S views are ANSI/ISO standard.  MS themselves were pushing them at one time. But they are too out of sync with the actual system catalog, i.e., too generic.

    Edit: Changed "ANSI" to "ANSI/ISO", as they are related.

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

  • I like INFORMATION_SCHEMA views because they are denormalized and easy to use. For example, the COLUMNS view returns information that would require joining a half dozen SYS tables. If I need something like a quick ad-hoc query for all tables having the column OrderID, then I'll use the views. However, for DDL deployment scripts, I tend to use SYS tables, because they cover a wider variety of object types (like check and default constraints) and properties (like when the object was created).

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

  • I used to use INFORMATION_SCHEMA exclusively, until I was trying to use them to quickly review the definitions of stored procedures and found out that the definitions are limited to the first 4000 characters.  🙁  That's when I started using the DMVs.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I stopped using them once I read that they were unreliable for determining the schema of an object.

  • I started using IS with SQL Server 2005 because I was told, or read somewhere, that they were better; I don't believe this anymore. I use them now more often just out of habit and because I know them inside out. I use sys schema tables/views when I can't get what I'm looking for in the IS. 

    Drew touched on the IS 4K object definition limit -- regardless of what I use (sys or IS) I use OBJECT_DEFINITION(OBJECT_ID(<object>)) to get DDL.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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