Sunday, March 27, 2005

Sql 2k -- Yukon -- Acadia

I am sure most of the developer community knows the code name of SQL Server 2005. For those who aren't aware it is "Yukon". Now do you know what is the code name for the release after SQL Server 2005?

The next version of SQL Server due out after Yukon/SQL Server 2005 ships is code-named "Acadia." According to Microsoft officials we could expect that version after 2006.


Wednesday, March 23, 2005

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