devxlogo

Some Useful TSQL Code Snippets for Beginners

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
  4. Views:
    Select Table_name as "View name"From Information_schema.TablesWhere Table_type = 'VIEW' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
  5. Tables:
    Select Table_name as "Table name"From Information_schema.TablesWhere Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
  6. 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
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist