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

Advertisements
Posted in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Comic for March 28, 2017
    Dilbert readers - Please visit Dilbert.com to read this feature. Due to changes with our feeds, we are now making this RSS feed a link to Dilbert.com.
%d bloggers like this: