Monday, May 24, 2010

How to rename table column

How to rename table column using T-SQL

 

Again very simple just use system SP

SP_RENAME

 

 

Make sure that you are in same DB whose table’s column you want to change

EXEC sp_rename 'Schema.Table.ColumnName', 'NewName', 'COLUMN';

 

 

 

How to rename table

How to rename table in SQL Server using T-SQL

 

How to rename big\huge tables?

 

The best way of renaming table is using system SP

SP_RENAME

 

Make sure that you are in same DB whose table you want to change.

 

Use DB

SP_RENAME ‘Table1’,’NewName’

 

SP_RENAME automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename.

 

How to rename SQL Server database

How to rename database using T-SQL in SQL Server 2005/2000/2008?

 

Very simple J Microsoft have provided one system SP which can be used here.

Sp_RENAMEDB

 

This SP needs two parameters 1st Database name which needs to be change

And 2nd New name.

 

SP_RENAMEDB ‘MyDB’ , ‘YourDB’

 

That’s it.

Friday, May 14, 2010

Getting Deadlock information in SQL Server

How to predict deadlock in SQL Server?

 

To know the process details involved in the deadlock one need to add two trace flag. These flag will enable log writer to write full information of deadlock in SQL server error logs.

 

DBCC TRACEON(1222,-1)

DBCC TRACEON(1205,-1)

 

After this when deadlock happened use xp_readerrorlog to read error log.

 

Or open SQL Server logs.

 

 

 

 

Monday, May 10, 2010

Order in which Query got executed by SQL Server

Here is the know fact,

 

The way you write query is never executed in same order, so today I am post the order in which query got executed by SQL Server

 

  1. FROM
  2. JOINS
  3. WHERE CLAUSE
  4. GROUP BY
  5. HAVING
  6. SELECT
  7. DISTINCT
  8. ORDER BY
  9. TOP