Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date