[T-SQL] Difference between @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

SELECT @@IDENTITYIt returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

[T-SQL] Difference between Index Scan and Index Seek

Index Scan:Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

[T-SQL] How to Rename a Column Name or Table Name

This morning i needed to rename column name and table name itself , so tought to share it on blog.

The script for renaming any column :
sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

The script for renaming any object (table, sp etc) :
sp_RENAME 'OldTableName' , 'NewTableName'

This example renames the customers table to custs.

EXEC sp_rename 'customers', 'custs'

This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

[T-SQL] Add new column [Not Null] column in a table already having data

This is a very simple question and asked many times, and answer is much simpler then the question.

Here is one example that we have to add a Transactiondate column into table CommissionEarned.

So here are the steps:

1. Add column without specifying not null
ALTER TABLE COMMISSIONEARNED ADD [CALCULATEDDATE] [datetime]
2. Add default constraint if its your business requirement
ALTER TABLE COMMISSIONEARNED ADD CONSTRAINT [DF_CE_CALCULATEDDATE] DEFAULT (getdate()) FOR [CALCULATEDDATE]
3. Update field with the data according to your business logic
UPDATE COMMISSIONEARNED SET CALCULATEDDATE=GETDATE()
4. Update field so that it should not entertain not null any more
ALTER TABLE COMMISSIONEARNED ALTER COLUMN [CALCULATEDDATE] [datetime] NOT NULL

Let me know for any questions

[T-SQL] My favourite SQL shortcuts

Today i would tell you my favourite sql shortcuts and the way to setup.

In SSMS , go to Tools and then Options.

In the tab of Options , you can setup what sql statements you run often.

You can execute them very simply , just highligh the table and press what ever key you have configured.

[T-SQL] Stored Procedure Advantage

One of the SQL Expert was asked this question and which one is the best advantage, so here is the answer:

There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question.
I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)

Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.

Execution plan retention and reuse
Query auto-parameterization
Encapsulation of business rules and policies
Application modularization
Sharing of application logic between applications
Access to database objects that is both secure and uniform
Consistent, safe data modification
Network bandwidth conservation
Support for automatic execution at system start-up
Enhanced hardware and software capabilities
Improved security
Reduced development cost and increased reliability
Centralized security, administration, and maintenance for common routines

[T-SQL] Move TempDB from one drive to another drive if TempDB is full.

If you come across following errors in log file, please follow the direction below.

Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow.

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to other drive.
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.
USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB
along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files.
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:datatemplog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

[T-SQL] How to remove duplicate data

One of the very common question that is asked many times , tell the way how can we delete the duplicate data or an easiest way or fastest way.

This method has one limitation that the table should have a primary key.

DELETE
FROM TableName
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TableName
GROUP BY Col1, Col2, Col3)

*TableName should be replaced with Table Name
*Col1 , Col2 and Col3 must be replaced with the criteria on which records will qualify as duplicate. It can be increase and decrease.

[T-SQL] How to generate CSV from Table Data

Here is a very simple code:

DECLARE @Str VARCHAR(MAX)
SELECT @Str = COALESCE(@Str+',' , '') + FieldName
FROM TableName
SELECT @Str

*Field Name should be replaced with Field Name
*Table Name should be replaced with Table Name

[T-SQL] Query Analyzer Shortcuts

Shortcut	Function		Shortcut	Function
ALT+BREAK	Cancel a query		CTRL+SHIFT+F2	Clear all bookmarks
ALT+F1	Database object information		CTRL+SHIFT+INSERT	Insert a template
ALT+F4	Exit		CTRL+SHIFT+L	Make selection lowercase
CTRL+A	Select all		CTRL+SHIFT+M	Replace template parameters
CTRL+B	Move the splitter		CTRL+SHIFT+P	Open
CTRL+C	Copy		CTRL+SHIFT+R	Remove comment
CTRL+D	Display results in grid format		CTRL+SHIFT+S	Show client statistics
CTRL+Delete	Delete through the end of the line		CTRL+SHIFT+T	Show server trace
CTRL+E	Execute query		CTRL+SHIFT+U	Make selection uppercase
CTRL+F	Find		CTRL+T	Display results in text format
CTRL+F2	Insert/remove bookmark		CTRL+U	Change database
CTRL+F4	Disconnect		CTRL+V	Paste
CTRL+F5	Parse query and check syntax		CTRL+W	Window selector
CTRL+G	Go to line		CTRL+X	Delete
CTRL+H	Replace		CTRL+Z	Undo
CTRL+I	Index Tuning Wizard		F1	Help for Query Analyzer
CTRL+K	Display/hide execution plan		F2	Move to next bookmark
CTRL+L	Display execution plan		F3	Repeat last search
CTRL+N	New query window		F4	Object Search
CTRL+O	Connect		F5	Execute a query
CTRL+P	Print		F6	Switch between query and result panes
CTRL+R	Show/Hide results pane		F8	Show/hide Object Browser
CTRL+S	Save		SHIFT+F1	Transact-SQL help
CTRL+SHIFT+0	Show options		SHIFT+F2	Move to previous bookmark
CTRL+SHIFT+C	Comment out code		SHIFT+TAB	Decrease indent
CTRL+SHIFT+DEL	Clear the active Editor pane		SHIFT+F6	Switch panes
CTRL+SHIFT+F	Save results to file		TAB	Increase indent

[T-SQL] What is – DML, DDL, DCL and TCL – Introduction and Examples

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

[My-Exp] Job Description for DBA/DB Developer

This JD is copied from one of the SQL Server Experts:

Job Description of Database Administrator (DBA) or Database Developer

Develop standards and guidelines to guide the use and acquisition of software and to protect vulnerable information.

Modify existing databases and database management systems or direct programmers and analysts to make changes.

Test programs or databases, correct errors and make necessary modifications.

Plan, coordinate and implement security measures to safeguard information in computer files against accidental or unauthorized damage, modification or disclosure.

Approve, schedule, plan, and supervise the installation and testing of new products and improvements to computer systems, such as the installation of new databases.

Train users and answer questions.

Establish and calculate optimum values for database parameters, using manuals and calculator.

Specify users and user access levels for each segment of database.

Develop data model describing data elements and how they are used, following procedures and using pen, template or computer software.

Develop methods for integrating different products so they work properly together, such as customizing commercial databases to fit specific needs.

Review project requests describing database user needs to estimate time and cost required to accomplish project.

Review procedures in database management system manuals for making changes to database.

Work as part of a project team to coordinate database development and determine project scope and limitations.

Select and enter codes to monitor database performance and to create production database.

Identify and evaluate industry trends in database systems to serve as a source of information and advice for upper management.

Write and code logical and physical database descriptions and specify identifiers of database to management system or direct others in coding descriptions.

Review workflow charts developed by programmer analyst to understand tasks computer will perform, such as updating records.

Revise company definition of data as defined in data dictionary.

[T-SQL] Create and Encrypted Stored Procedure

An answer to a very simple question , how to create a stored procedure any problem if we create it.

Here is the script

USE AdventureWorks
GO
-- Create Encyrpted SP
CREATE PROCEDURE uspEnc
WITH ENCRYPTION
AS
SELECT *
FROM Sales.SalesOrderDetail
UNION
SELECT *
FROM Sales.SalesOrderDetail
GO
-- Execute SP
EXEC uspEnc
GO

Question: if any stored procedure is encrypted can we see its definition in Activity Monitor.
Answer: No

Question: Any tool to decrypt the SP
Answer: SQL Decryptor, Decrypt SQL

Question: Should a DBA recommend it
Answer: Encrypted stored procedures are horrible. You can’t see what queries are executed, you don’t have a clue what the execution plan is. So if users are complaining about the slowness of the system, and everything is encrypted… then it will not be possible to look into it.

[T-SQL] Get Running Query from the Sessions

If you want to get the query running of one of the session then you can get from the query below , just answering that if you want to know the running sessions then you can use sp_who and sp_who2.

Before executing this statement please change the option from result to grid to result to text. For this you can use shortcut CTRL+T.

Here is the script, 55 can be replaced from your desired session id.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 55
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

[T-SQL] Get Numbers from Alpha Numeric Value

Here is the procedure i just saw from one of the sql expert blog that will rreturn Numbers from Alpha Numeric Values.

CREATE PROCEDURE [dbo].[GetNumbers]
@alpha VARCHAR(50),
@decimal DECIMAL(14, 5) OUTPUT
AS BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMsg VARCHAR(50)
DECLARE @Pos INT
DECLARE @CommaPos INT
DECLARE @ZeroExists INT
DECLARE @alphaReverse VARCHAR(50)
DECLARE @NumPos INT
DECLARE @Len INT
-- 1 Reverse the alpha in order to get the last position of a numeric value
SET @alphaReverse = REVERSE(@alpha)
-- 2 Get the last position of a numeric figure
SET @NumPos = PATINDEX('%[0-9]%', @alphaReverse)
-- 3 Get the lenght of the string
SET @Len = LEN(@alpha)
-- 4 Add a comma after the numeric data in case it's no decimal number
SET @alpha = SUBSTRING(@alpha, 1, (@Len - @NumPos + 1))
+ ','
+ SUBSTRING(@alpha, (@Len - @NumPos + 2), 50)
-- Check if there is a zero (0) in the @alpha, then we later set the @decimal to 0
-- if it's 0 after the handling, else we set @decimal to NULL
-- If 0 no match, else there is a match
SET @ZeroExists = CHARINDEX ( '0' , @alpha ,1 )
-- Find position of , (comma)
SET @CommaPos = 1
SET @CommaPos = PATINDEX('%,%', @alpha)
IF (@CommaPos = '') BEGIN
SET @CommaPos = 20
END
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
-- Replaces any aplha with '0' since we otherwice can't keep track of where the decimal
-- should be put in. We assume the numeric number has no aplhe inside. The regular way
-- to solve this is to replace with ”, but then we miss the way to find the place to
-- put in the decimal.
WHILE (@Pos > 0) BEGIN
SET @alpha = STUFF(@alpha, @pos, 1, '0')
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
END
IF (@alpha IS NOT NULL AND @alpha != '') BEGIN
SET @decimal = CONVERT(DECIMAL(14, 5), SUBSTRING(@alpha, 1, (@CommaPos - 1))
+ '.'
+ SUBSTRING(@alpha, (@CommaPos + 1), 20))
END
-- Since we in this case don't want to set 0 if where is no numeric value, we set NULL to be safe
IF (@decimal = 0 AND @ZeroExists = 0) BEGIN
SET @decimal = NULL
END
END
GO

If you run above SP as shown below it will work
DECLARE @myVal DECIMAL(14,5)
EXEC [GetNumbers] 'ABC455,88ghf', @myVal OUTPUT
SELECT @myVal RValue

RValue
---------------------------------------
455.88000

[My-Exp] Query Optimized

Few days back i blogged that my manager said to me to optimize an sp , so here is the story for this:

The sp was not running for one day data even in 45 mins, but now its running in 4 mins for one month data. So here are the tips of what i did.

1. Added "with recompile" in sp.
2. Added "SET NOCOUNT ON;  "
3. Applied Parameter sniffing
4. Created two Non clustered indexes on one of the table.

[T-SQL] Clear Cache and Buffer of Stored Procedure

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

[T-SQL] Index rebuild or reorganize , which one to use ?

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

[T-SQL] How to find index fragmentation

This script will tell you the index fragmentation stats:

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID

[T-SQL] Identify Blocking Query / Session

One of the most common problem that DBA faces is that one or many sessions get blocked by some sessions due to long execution or deadlock. I have many times faced this problem and also read the same problem which is faced by many DBAs every day.

Here is the query by which you can kill the blocking session:
Kill 3 ( 3 is the blocking session )

But now your question would be how to identify the blocking session , so here we go...

SELECT


db.name DBName,

tl.request_session_id,

wt.blocking_session_id,

OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,

tl.resource_type,

h1.TEXT AS RequestingText,

h2.TEXT AS BlockingTest,

tl.request_mode

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id

INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id

INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id

INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id

CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1

CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

GO

[T-SQL] Optimize Stored Procedure

Today in morning when i came , i was having an email of my boss telling that there is an high priority issue which need to solved on immediate basis. And it was to optimize sp as it was creating a huge problem. I am currently working on it but here are some tips to get the job done:

1. Include SET NOCOUNT ON statement:

With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

2. Use schema name with object name:

The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

3. Do not use the prefix “sp_” in the stored procedure name:

If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')
Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

4. Try to avoid using SQL Server cursors whenever possible:

Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.

5. Use TRY-Catch for error handling:

Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

[T-SQL] Restore database trough mdf file only , ldf missing

One of my friend just call me and told that he has an mdf file but no ldf file , so if want to restore db then what to do , i searched over internet and found two fastest methods for it

USE [master]
GO
-- Method 1: I use this method
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
GO
-- Method 2:
CREATE DATABASE TestDb ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO

[T-SQL] Kill All Process in Database

I was just going trough one query and find out that it was giving one error , but its really useful script for killing all process. One thing just be sure that you are not killing the process from the db instance from where you are running the query.

CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150), rquest_id INT)
INSERT INTO #TmpWho
EXEC sp_who
DECLARE @spid INT
DECLARE @tString VARCHAR(15)
DECLARE @getspid CURSOR
SET @getspid = CURSOR FOR
SELECT spid
FROM #TmpWho
WHERE dbname = 'mydb'OPEN @getspid
FETCH NEXT FROM @getspid INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))
EXEC(@tString)
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid
DROP TABLE #TmpWho
GO

[T-SQL] Renaming the Physical Filename for Datafiles in SQL Server 2008

I just got a question in morning , that how can one rename the physical filename for data files in sql server 2008. So here is the link and then some of the methods which ever suits you

http://technet.microsoft.com/en-us/library/ms174269.aspx

There are several ways to make this change, however to rename the physical database files at operating system level you will have to take the database offline

1. Use SSMS to take the database Offline (right-click on Database, select Tasks, Take Offline), change the name of the files at the OS level and then Bring it Online.

2. You could Detach the database, rename the files and then Attach the database pointing to the renamed files to do so.

3. You could Backup the database and then restore, changing the file location during the restore process.

4. using T SQL

ALTER DATABASE databaseName SET OFFLINE
GO


ALTER DATABASE databaseNAme MODIFY FILE (NAME =db, FILENAME = 'C:\Program
Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf')
GO

--if changing log file name
ALTER DATABASE databaseNAme MODIFY FILE (NAME = db_log, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf')
GO

ALTER DATABASE databaseName SET ONLINE
GO