INFORMATION_SCHEMA views

Question

INFORMATION_SCHEMA views

Lynn Pettis

SSC Guru

Points: 442467
More actions
August 8, 2017 at 11:02 am

#402402

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.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 101118 More actions · Answer 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) "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".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

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

ScottPletcher SSC Guru Points: 101118 More actions · Answer 5

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) "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".

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

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

drew.allen SSC Guru Points: 76996 More actions · Answer 7

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

Tom_Hogan Hall of Fame Points: 3903 More actions · Answer 8

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 9

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