Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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.

 

 

Sitemap