Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Beginner
Jun 29, 2005



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Some Useful TSQL Code Snippets for Beginners

For the benefit of the newbies in SQL Server, here's a list of a few basic queries:
  1. How to determine whether a table column exists in a table: Run this query, and if the column exists, it will return a number greater than 0:
    Select Coalesce(Col_length('authors','au_lname'),0)
  2. Determine whether a database exists in a server or not. Run this query, and if the database exists, it will return "Exists":
    Select 'Exists' From Information_schema.Schemata Where Catalog_name='pubs'
  3. List user-defined stored procedures, views, tables, or triggers:
    • 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
Vadivel Mohanakrishnan
Thanks for your registration, follow us on our social networks to keep up-to-date