Some Useful TSQL Code Snippets for Beginners

Select Coalesce(Col_length('authors','au_lname'),0)

Select 'Exists' From Information_schema.Schemata Where Catalog_name='pubs'

• Stored Procedures:

Select Routine_name
From Information_schema.Routines
Where Routine_type = 'PROCEDURE' and Objectproperty 
(Object_id(Routine_name), 'IsMsShipped') = 0
Order by Routine_name

• Views:

  
  Select Table_name as "View name"
  From Information_schema.Tables
  Where Table_type = 'VIEW' and Objectproperty 
  (Object_id(Table_name), 'IsMsShipped') = 0
  

• Tables:

  
  Select Table_name as "Table name"
  From Information_schema.Tables
  Where Table_type = 'BASE TABLE' and Objectproperty 
  (Object_id(Table_name), 'IsMsShipped') = 0
  

• Triggers:

  
  Select Object_name(so.parent_obj) as "Table name", so.[name] as "Trigger name"
  From sysobjects so
  Where Objectproperty(so.[id], 'IsTrigger')=1 and 
  Objectproperty(so.[id], 'IsMSShipped')=0
  Order by 1,2