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

AB's SQL Server Day: An enriching experience

Hello World!

I had the opportunity to attend a session by Amit Bansal in Microsoft
(Embassy Golf Links, Bangalore) premises on Saturday, 20^th
November 2010. The discussion agenda was as follows:

• Keynote and Welcome by
  Vinod Kumar (Microsoft)
• Microsoft BI Platform
  developed around MS SQL 2008 R2 - Delivered by Sudhir Rawat (Microsoft)
• An introduction to
  SQLServerGeeks.com, a community portal in development - By Amit Bansal

I must mention here that the keynote and
welcome by Vinod worked as an appetizer that should've generated
curiosity and set expectations on what was to follow. Must say that the
demonstartion on how the TRUNCATE command behaves vis-a-vis DELETE in
terms of transaction logging, TRANSACTIONS themselves was really cool.
I never knew that a TRUNCATE could be issued within a transaction and
rolled back!!



I have had the oppurtunity to attend a session on MS SQL 2005 Reporting
and Analysis services by Amit Bansal in 2006 (The same Microsoft campus
as mentioned in the beginning). The man has only got better with time
in terms of an engaging style of delivery and the way he connects with
the audience. As most of us would agree, it is hard to concentrate on a
deep-dive technical session for more than 15 minutes but thanks to AB's
efforts, in the entire 1 hour of session, I hardly winked! Add to that
the fact that I only have theoretical knowledge about the subject
matter of Data Warehousing and Data Mining and you'd know what I'm
talking about! (PS: I am a .NET developer with only a working knowledge
of databases :)



I would appeal to all my friends to join the SQLServerGeeks.com community
as also the wetogether.in site. Amit and Peopleware are doing a
fantastic service to the community by organising such events. I have
honestly got motivated to be on the podium in one such event in the
very near future!


Happy Coding
SM