Login | Register   
LinkedIn
Google+
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

WEBINAR:

On-Demand

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017


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
Thanks for your registration, follow us on our social networks to keep up-to-date