MSSQL Tidbits (INFORMATION_SCHEMA, SYS.TABLES et al)

Hello World!



So here I was looking at a DB script for one of our products that
checks MSSQL Metadata for existence of an object before attempting to
do anything with it and my enquisitive self pestered me to checkout if
the (very) old way of checking for a DB objects existence as follows:



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[<schema>].[<table-name>]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

   BEGIN
     <do something>
      END
ELSE

   BEGIN
           <do something else>
   END
GO



has any easier, more readable way of doing in MSSQL 2005 or higher. I
then remembered seeing some thing like INFORMATION_SCHEMA and SYS which
can be queried for details of metadata. My search (GOOGLE: sys.tables
vs information_schema.tables) landed me on this
excellent article on the facts of the matter.



To Summarize, MSSQL 2000 onwards provides what are called as "SYSTEM
VIEWS" to look up metadata. For example, to check if a table exists,
one could write a query like:



IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'<TABLE-NAME>')
   BEGIN
     <do something>       
   END
ELSE
   BEGIN
           <do something else>
   END
GO


Phew! Much better and self explanatory! As it turns out, you could also
query "CATALOG VIEWS" for metadata and would get much more detailed
information, albeit at the cost of a slightly more detailed syntax. A similar query using CATALOG VIEW would then be


IF EXISTS (SELECT * FROM SYS.TABLES WHERE name =
'<TABLE-NAME>' AND type = 'U')
   BEGIN
     <do something>       
   END
ELSE
   BEGIN
           <do something else>
   END
GO


Happy Coding!

SM