Useful TSQL code snippets for beginners

For the benefit of the newbies in SQL Server I have listed down few of the basic queries which one would try to write now and then.

The following points or topics are covered:

1. How to find out whether a table column exists in a table or not?
2. Find out whether a database exists in a server or not
3. List out all User defined Stored procedures, Views, tables and triggers

Find below the T-Sql statements for the above questions:

1. To know whether a column exists in a table or not.

--If the column exists it would return a number greater than 0
Select Coalesce(Col_length('authors','au_lname'),0)

2. To know whether a database exists or not

--This would return "Exists" if the database exists.
Select 'Exists' From Information_schema.Schemata Where Catalog_name='pubs'

3. To list all user defined 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


4. To list all Views

Select Table_name as "View name"
From Information_schema.Tables
Where Table_type = 'VIEW' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

5. To list all tables

Select Table_name as "Table name"
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

6.To list all 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