Change Collation Of Database

USE [master]

ALTER DATABASE [DbName]  SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

GO
ALTER DATABASE [DbName] COLLATE SQL_Latin1_General_CP1_CI_AS
GO
ALTER DATABASE [DbName] SET RECOVERY FULL WITH NO_WAIT
GO

Go
ALTER DATABASE [DbName]  SET MULTI_USER 
GO

Sql X Types

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
V = View
X = Extended stored procedure

Comparing Tables For Difference Version

Create table #nik
(
    table_Name Varchar(300),
    Column_Name Varchar(300)
)
Create Table #Temp 
(
    Table_Name Varchar(300)
) 

Insert Into #nik Select TABLE_NAME ,Column_Name From dbName.Information_Schema.columns 
Where Column_Name Not IN
(Select Column_Name From dbName2.Information_Schema.columns) 
Select * From #nik 

Select * From #nik  Where table_Name Not in (Select table_Name from #Temp)
Insert Into #Temp Values('Table')
Select * From #Temp 

Rename Computer Name When Sql Server Already Installed

To rename a computer that hosts a stand-alone instance of SQL Server
•    For a renamed computer that hosts a default instance of SQL Server, run the following procedures:

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
Restart the instance of SQL Server.
•    For a renamed computer that hosts a named instance of SQL Server, run the following procedures:

sp_dropserver <'old_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO
Restart the instance of SQL Server.
 After the Renaming Operation
________________________________________
After a computer has been renamed, any connections that used the old computer name must connect by using the new name.
To verify that the renaming operation has completed successfully
•    Select information from either @@SERVERNAME or sys.servers. The @@SERVERNAME function will return the new name, and the sys.servers table will show the new name. The following example shows the use of @@SERVERNAME.

SELECT @@SERVERNAME AS 'Server Name'

Kill Sleeping Connection in Sql

Create PROCEDURE [dbo].[SleepedConnKill] 
AS
 SET NOCOUNT ON;  
    Declare @StrKill Varchar(30)         
    Declare @SPID_Cur Numeric(18,0)
   
   Declare SysProcess_Cursor Cursor LOCAL  For
     Select SPID From sys.sysprocesses  Where SPID>50 And  Status='sleeping' And  DateDiff(MINUTE ,Last_Batch,GetDate())>5 
   Open SysProcess_Cursor
   Fetch Next From SysProcess_Cursor Into @SPID_Cur
   While @@Fetch_Status = 0                    
   Begin   
    
     Set @StrKill = 'KILL '+ convert(varchar(10),@SPID_Cur)            
     EXEC(@StrKill)    
    
   Fetch Next From SysProcess_Cursor Into @SPID_Cur
   End    
   Close SysProcess_Cursor
   Deallocate SysProcess_Cursor
    Return

UPDATE STATISTICS Fot all tables in Database

Here is the sp which update STATISTICS for all table.

EXEC sp_updatestats

Update STATISTICS is necessary to avoid deadlock in database.
 

Using ROW_NUMBER() Function Fetch Exact number of Row.

Fetch Exact 10th row from table where there is no primary key. 

select * from
(Select Emp_First_Name ,ROW_NUMBER() OVER(ORDER BY Emp_First_Name DESC) AS 'Emp' From Table)Qry Where Qry.emp = 10