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

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



Application Security Testing: An Integral Part of DevOps

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