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_nameFrom Information_schema.RoutinesWhere Routine_type = 'PROCEDURE' and Objectproperty (Object_id(Routine_name), 'IsMsShipped') = 0Order by Routine_name
    • Views:
      Select Table_name as "View name"From Information_schema.TablesWhere Table_type = 'VIEW' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
    • Tables:
      Select Table_name as "Table name"From Information_schema.TablesWhere 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 soWhere Objectproperty(so.[id], 'IsTrigger')=1 and Objectproperty(so.[id], 'IsMSShipped')=0Order by 1,2
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: