Just Learned - SQL Server - Use INFORMATION_SCHEMA views over sysobjects as internal tables might change between versions

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

SQL Server - Use INFORMATION_SCHEMA views over sysobjects as internal tables might change between versions

Mar 14 2012 12:00AM by vanne040

I was looking into some table creation scripts in source control today. I found that the developers used two ways to check if the table exists in the database.

Few scripts employed information_schema.tables vs sys.objects. I personally, have been using the sys.objects to do the same. This got me wondering today which is a better method and found that INFORMATION_SCHEMA views are good and System Tables/views are bad.

The biggest problem is that internal system tables change between versions, so scripts are generated for one specific SQL Server version. The INFORMATION_SCHEMA views, on the other hand, are part of the SQL92 standard, so they don't change between versions.

There are lot of articles already on the web, but I found the below link interesting of all!

3 Comments

Too bad SQL Server Management Studio generates scripts using sys.objects when trying to drop an object (table, etc.) then.

commented on Mar 14 2012 11:26AM

Sergejack

41 · 4% · 1393

Whenever we (a colleague of mine and I) perform code reviews, we always prefer the use of INFORMATIONSCHEMA for this very reason. We have been working with the same code for 7 years now all the way from SQL2000 to 2008R2, and INFORMATIONSCHEMA has never failed us.

commented on Mar 14 2012 1:43PM

Nakul Vachhrajani

4 · 33% · 10587

Its recommended to use INFORMATIONSCHEMA whenever possible. But there are cases where sys views need to be used. Few such instances where I could not away with are when checking for triggers and synonyms(found till now).

commented on Mar 14 2012 3:58PM

vanne040

83 · 2% · 657

Your Comment

Sign Up or Login to post a comment.

"SQL Server - Use INFORMATION_SCHEMA views over sysobjects as internal tables might change between versions" rated 5 out of 5 by 11 readers