Reduce log file size in sql server

Dear all these are the query to reduce 

size of log file in sql 

Query 1

ALTER DATABASE databasename

SET RECOVERY SIMPLE;

GO

Query 2

DBCC SHRINKFILE (nameoflogfile, 1)

Query 3

ALTER DATABASE databasename

SET RECOVERY FULL;

In this query

Step 1 go to property of database

Step 2 go to file option

Step 3 copy file name of log file

Step 4 replace that name in above query

And run query in given sequence

After it check the size of log file

*** be careful copy only log file name

Read More

Right Use of Transaction Using TRY catch

This is right use of SQL transaction Using TRY Catch for 
Avoid DeadLock Condition

BEGIN TRANSACTION TransRavi

BEGIN TRY

<Do Your Task Here>


COMMIT TRANSACTION TransRavi

END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION  TransRavi
END CATCH

Read More

Find text in whole database procedures

For Finding particular text in stored procedures 

please put your text on the place of <Text>
and get result...



SELECT  c.name
FROM syscomments B
inner join sysobjects c on c.id=b.id where b.text like '%<Text>%'

Read More

Get total no of connections in sql server

Run this query and get total no of connections in sql server


SELECT DB_NAME(dbid) AS databasename,
COUNT(dbid) AS Connections,
loginame
FROM    sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)

Read More

Get Foriegn Keys used in Other Tables

Please Pass your table name 
To find out all references used in tables 


select A.name as ForiegnKeyTable, 
 B.name as ForeignKeyColumn
from sys.foreign_key_columns as C
inner join sys.tables as A on C.parent_object_id = A.object_id
inner join sys.columns as B on C.parent_object_id = B.object_id and
 C.parent_column_id = B.column_id
where C.referenced_object_id = (select object_id from sys.tables
where name = '<Table Name>')
order by ForiegnKeyTable 

Read More

Get Complete Dependency of A table in databse

Please pass your table name in this query and 
Get name of all object which are dependent on that



SELECT distinct A.name
FROM syscomments B
INNER JOIN sysobjects A ON B.id = A.id
WHERE charindex('<Table Name>', text) > 0

Read More

Function for converting Numeric to Word

Step 1 :-- 
Run this script 

CREATE TABLE [dbo].[M_Words](
    [Code] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
    [WNumber] [int] NOT NULL,
    [Wwords] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[M_Words] ON
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(1 AS Numeric(9, 0)), 0, N'Zero')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(2 AS Numeric(9, 0)), 1, N'One')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(3 AS Numeric(9, 0)), 2, N'Two')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(4 AS Numeric(9, 0)), 3, N'Three')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(5 AS Numeric(9, 0)), 4, N'Four')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(6 AS Numeric(9, 0)), 5, N'Five')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(7 AS Numeric(9, 0)), 6, N'Six')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(8 AS Numeric(9, 0)), 7, N'Seven')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(9 AS Numeric(9, 0)), 8, N'Eight')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(10 AS Numeric(9, 0)), 9, N'Nine')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(11 AS Numeric(9, 0)), 10, N'Ten')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(12 AS Numeric(9, 0)), 11, N'Eleven')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(13 AS Numeric(9, 0)), 12, N'Tweleve')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(14 AS Numeric(9, 0)), 13, N'Thirteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(15 AS Numeric(9, 0)), 14, N'Fourteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(16 AS Numeric(9, 0)), 15, N'Fifteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(17 AS Numeric(9, 0)), 16, N'Sixteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(18 AS Numeric(9, 0)), 17, N'Seventeen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(19 AS Numeric(9, 0)), 18, N'Eighteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(20 AS Numeric(9, 0)), 19, N'Ninteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(21 AS Numeric(9, 0)), 20, N'Twenty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(22 AS Numeric(9, 0)), 30, N'Thirty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(23 AS Numeric(9, 0)), 40, N'Fourty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(24 AS Numeric(9, 0)), 50, N'Fifty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(25 AS Numeric(9, 0)), 60, N'Sixty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(26 AS Numeric(9, 0)), 70, N'Seventy')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(27 AS Numeric(9, 0)), 80, N'Eighty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(28 AS Numeric(9, 0)), 90, N'Ninty')
SET IDENTITY_INSERT [dbo].[M_Words] OFF
 

Step 2 

Make This Function


CREATE FUNCTION dbo.[Ravi_NumToWords](@Number Numeric(18,2),@CPaise Char(1))
RETURNS varchar(100) AS 
BEGIN
   
    Declare @StrNumber varchar(10), @SLacs char(2), @SThou char(2), @SHun char(2)
    Declare @STenUnt char(2), @STen char(2), @SUnt char(2), @SDecimal char(2)
    Declare @ILacs Int, @IThou Int, @IHun Int, @ITenUnt Int, @ITen Int, @IUnt Int, @IDecimal Int
    Declare @SNumToWords varchar(100), @Wwords varchar(10)

    Select @StrNumber = Replicate('0',10-Len(LTrim(RTrim(convert(varchar,@Number))))) + LTrim(RTrim(Convert(varchar,@Number)))
    --Print @StrNumber
    --Print Len(@StrNumber)
    Select @SNumToWords = ''

    --Print Len(LTrim(RTrim(convert(varchar,@Number))))
    If Len(LTrim(RTrim(convert(varchar,@Number)))) > 4
    Begin
        --Print Len(@StrNumber)
        Select @SLacs = Substring(@StrNumber,1,2)
        --Print @SLacs
        Select @ILacs = Convert(int,@SLacs)
        If @ILacs > 0
        Begin
            Select @STen = Substring(@StrNumber,1,1)
            Select @SUnt = Substring(@StrNumber,2,1)

            if Convert(int,@STen) = 1
            Begin
                Select @ITen = Convert(int,Substring(@StrNumber,1,2))
                Select @IUnt = 0
            End
            Else
            Begin
                Select @ITen = Convert(int,@STen)*10
                Select @IUnt = Convert(int,@SUnt)
            End

            If @ITen > 0
            Begin           
                Select @Wwords = ''
                Select @Wwords = Wwords From M_Words Where WNumber = @ITen
                --Print @Wwords
                Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
            End

            If @IUnt > 0
            Begin           
                Select @Wwords = ''
                Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
                --Print @Wwords
                Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
            End

            --Select @Wwords = ''
            --Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
            --Print @Wwords
            Select @SNumToWords = @SNumToWords + ' Lakh'
        End

        Select @SThou = Substring(@StrNumber,3,2)
        --Print @SThou
        Select @IThou = Convert(int,@SThou)
        If @IThou > 0
        Begin
            Select @STen = Substring(@StrNumber,3,1)
            Select @SUnt = Substring(@StrNumber,4,1)

            if Convert(int,@STen) = 1
            Begin
                Select @ITen = Convert(int,Substring(@StrNumber,3,2))
                Select @IUnt = 0
            End
            Else
            Begin
                Select @ITen = Convert(int,@STen)*10
                Select @IUnt = Convert(int,@SUnt)
            End
           
            If @ITen > 0
            Begin           
                Select @Wwords = ''
                Select @Wwords = Wwords From M_Words Where WNumber = @ITen
                --Print @Wwords
                Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
            End

            If @IUnt > 0
            Begin           
                Select @Wwords = ''
                Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
                --Print @Wwords
                Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
            End

            --Select @Wwords = ''
            --Select @Wwords = Wwords From M_Words Where WNumber = @IThou
            --Print @Wwords
            --Select @SNumToWords = @SNumToWords + @Wwords + ' Thousand '
            Select @SNumToWords = @SNumToWords + ' Thousand '
        End

        Select @SHun = Substring(@StrNumber,5,1)
        --Print @SHun
        Select @IHun = Convert(int,@SHun)
        If @IHun > 0
        Begin
            Select @Wwords = ''
            Select @Wwords = Wwords From M_Words Where WNumber = @IHun
            --Print @Wwords
            Select @SNumToWords = @SNumToWords + @Wwords + ' Hundred'
        End

        Select @STenUnt = Substring(@StrNumber,6,2)
        ---Print @STenUnt

        Select @ITenUnt = Convert(int,@STenUnt)

        If @ITenUnt > 0
        Begin
            Select @STen = Substring(@StrNumber,6,1)
            Select @SUnt = Substring(@StrNumber,7,1)

            if Convert(int,@STen) = 1
            Begin
                Select @ITen = Convert(int,Substring(@StrNumber,6,2))
                Select @IUnt = 0
            End
            Else
            Begin
                Select @ITen = Convert(int,@STen)*10
                Select @IUnt = Convert(int,@SUnt)
            End

           
            If @ITen > 0
            Begin           
                Select @Wwords = ''
                Select @Wwords = Wwords From M_Words Where WNumber = @ITen
                --Print @Wwords
                Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
            End

            If @IUnt > 0
            Begin           
                Select @Wwords = ''
                Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
                --Print @Wwords
                Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
            End
        End

        Select @SNumToWords = @SNumToWords + Space(1) + 'Rupees Only/-'        --Only/-



    End
    Else
    Begin
        --Print Len(@StrNumber)
        --Print LTrim(RTrim(convert(varchar,@Number)))
        Select @SLacs = Substring(LTrim(RTrim(convert(varchar,@Number))),1,1)
        --Print @SLacs
        Select @ILacs = Convert(int,@SLacs)
        If @ILacs > 0 and @ILacs <> 1
        Begin
            Select @Wwords = ''
            Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
            --Print @Wwords
            Select @SNumToWords = @SNumToWords + Space(1) + @Wwords + Space(1) + 'Rupees'
        End
        Else
        Begin
            Select @Wwords = ''
            Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
            --Print @Wwords
            Select @SNumToWords = @SNumToWords + @Wwords + Space(1) + 'Rupee'
        End
    End

    If @CPaise = 'Y'
    Begin
        Select @SDecimal = Substring(@StrNumber,9,2)
        Select @IDecimal = Convert(int,@SDecimal)
        If @IDecimal > 0
        Begin
            Select @SNumToWords = @SNumToWords + ' and'
            Select @STen = Substring(@SDecimal,1,1)
            Select @SUnt = Substring(@SDecimal,2,1)
   
            if Convert(int,@STen) = 1
            Begin
                Select @ITen = Convert(int,Substring(@StrNumber,9,2))
                Select @IUnt = 0
            End
            Else
            Begin
                Select @ITen = Convert(int,@STen)*10
                Select @IUnt = Convert(int,@SUnt)
            End
           
            If @ITen > 0
            Begin           
                Select @Wwords = ''
                Select @Wwords = Wwords From M_Words Where WNumber = @ITen
                --Print @Wwords
                Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
            End
   
            If @IUnt > 0
            Begin           
                Select @Wwords = ''
                Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
                --Print @Wwords
                Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
            End
   
            Select @SNumToWords = @SNumToWords + Space(1) + 'Paise'   
   
        End
    End

    --Print LTrim(RTrim(@SNumToWords))
    Return LTrim(RTrim(@SNumToWords))

End 


Step 3 

Use Like This

select dbo.[Ravi_NumToWords] (15264,'y') 

Note :- This Function's max limit is 1 to 9999999
 

Read More

Shrink Database File

By Using This query we can reduce database file

Firstly we should get File Name by Using This


with TblSpace as
(
    select
        Name,
        size * 8.0 / 1024 as TotalSize,
        fileproperty(name, 'SpaceUsed') * 8.0 / 1024 as UsedSpace
    from sys.database_files
)
select
    Name,
    TotalSize,
    UsedSpace,
    convert(decimal(5, 2), UsedSpace / TotalSize * 100) as UsedSpacePer
from TblSpace

Get File Name and use that file name in this query to reduce 

dbcc shrinkfile(<Database_File_Name>, <Target_Size_in_MB>)

Read More

Query to Get Database file and Spaces

By using this query we can get all using database files and their space status

with TblSpace as
(
    select
        Name,
        size * 8.0 / 1024 as TotalSize,
        fileproperty(name, 'SpaceUsed') * 8.0 / 1024 as UsedSpace
    from sys.database_files
)
select
    Name,
    TotalSize,
    UsedSpace,
    convert(decimal(5, 2), UsedSpace / TotalSize * 100) as UsedSpacePer
from TblSpace

Read More

Kill Thread in Database

Please use this query with your database name 
this query will clear all threads ...

ALTER DATABASE <Database Name>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE  <Database Name>
SET MULTI_USER;

Read More

How to Show Single quotation mark apostrophe mark in sql

Use Quotaion Mark like this for show this

select 'Hello, He is Ravi''s brother.'

Read More

How to use cursor in SQL Server

Please Use  this in your procedures and functions

   DECLARE  C21 CURSOR FOR  <Query to find Value on basis of ...>
   OPEN C21     
   FETCH FROM C21 INTO @col      
   WHILE @@FETCH_STATUS = 0 BEGIN     

-----Do your tasks here according to cursor
 
 
   FETCH FROM C21 INTO @col
   END     
   CLOSE C21     
   DEALLOCATE C21     
     

Read More

Calculate Age in SQL Server

Please Pass your date of birth in "DD/MM/YYYY" format and get age .....


Create a Procedure...


create Proc dbo.CalculateAge_ravi  
(@date1 varchar(15))
as
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = CONVERT(datetime,@date1,103)

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years as Years, @months as Months, @days as Days




Get Your Age

 exec CalculateAge_ravi  'Pass your DOB'

Read More

Get Script of Stored Procedures and views

Please Write this qurey and get

Read More

Get AlfaNumeric Values From a column of a table in sql server

Function to get Only Numeric Value

Please Use this function for that


CREATE FUNCTION dbo.Ravi_GetNumeric 
(@CompeteStr VARCHAR(256)) 
RETURNS VARCHAR(256) 
AS 
BEGIN 
DECLARE @NumericValues INT 
SET @NumericValues = PATINDEX('%[^0-9]%', @CompeteStr ) 
BEGIN 
WHILE @NumericValues > 0 
BEGIN 
SET @CompeteStr = STUFF(@CompeteStr , @intAlpha, 1, '' ) 
SET @NumericValues = PATINDEX('%[^0-9]%', @CompeteStr ) 
END 
END 
RETURN ISNULL(@CompeteStr ,0) 
END

Read More

Generate Script of a Table in Sql server (Using Query)

Step 1. Please pass Your table Name in this Stored Procedure and get script



Create Proc dbo.Ravi_Script_Table
(@tablename varchar(50))
as
begin

    DECLARE
          @object_name SYSNAME
        , @object_id INT
        , @SQL NVARCHAR(MAX)
    
    SELECT
          @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
        , @object_id = [object_id]
    FROM (SELECT [object_id] = OBJECT_ID(@tablename, 'U')) o
    
    SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
        SELECT CHAR(13) + '    , [' + c.name + '] ' + 
            CASE WHEN c.is_computed = 1
                THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
                ELSE 
                    CASE WHEN c.system_type_id != c.user_type_id 
                        THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']' 
                        ELSE '[' + UPPER(tp.name) + ']' 
                    END  + 
                    CASE 
                        WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
                            THEN '(' + CASE WHEN c.max_length = -1 
                                            THEN 'MAX' 
                                            ELSE CAST(c.max_length AS VARCHAR(5)) 
                                        END + ')'
                        WHEN tp.name IN ('nvarchar', 'nchar')
                            THEN '(' + CASE WHEN c.max_length = -1 
                                            THEN 'MAX' 
                                            ELSE CAST(c.max_length / 2 AS VARCHAR(5)) 
                                        END + ')'
                        WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
                            THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                        WHEN tp.name = 'decimal'
                            THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                        ELSE ''
                    END +
                    CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id 
                        THEN ' COLLATE ' + c.collation_name
                        ELSE ''
                    END +
                    CASE WHEN c.is_nullable = 1 
                        THEN ' NULL'
                        ELSE ' NOT NULL'
                    END +
                    CASE WHEN c.default_object_id != 0 
                        THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' + 
                             ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
                        ELSE ''
                    END + 
                    CASE WHEN cc.[object_id] IS NOT NULL 
                        THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
                        ELSE ''
                    END +
                    CASE WHEN c.is_identity = 1 
                        THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' + 
                                        CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')' 
                        ELSE '' 
                    END 
            END
        FROM sys.columns c WITH(NOLOCK)
        JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
        LEFT JOIN sys.check_constraints cc WITH(NOLOCK) 
             ON c.[object_id] = cc.parent_object_id 
            AND cc.parent_column_id = c.column_id
        WHERE c.[object_id] = @object_id
        ORDER BY c.column_id
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') + 
        ISNULL((SELECT '
        , CONSTRAINT [' + i.name + '] PRIMARY KEY ' + 
        CASE WHEN i.index_id = 1 
            THEN 'CLUSTERED' 
            ELSE 'NONCLUSTERED' 
        END +' (' + (
        SELECT STUFF(CAST((
            SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
                    CASE WHEN ic.is_descending_key = 1
                        THEN ' DESC'
                        ELSE ''
                    END
            FROM sys.index_columns ic WITH(NOLOCK)
            WHERE i.[object_id] = ic.[object_id]
                AND i.index_id = ic.index_id
            FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
        FROM sys.indexes i WITH(NOLOCK)
        WHERE i.[object_id] = @object_id
            AND i.is_primary_key = 1), '') + CHAR(13) + ');'
    
    PRINT @SQL
  
  
end

Read More

Change DataType or Size of a Column in Sql Server

Step 1 

 Alter Table <Table Name>
Alter Column <Column Name> <New Data Type with Size>

Note : If you find any error of transaction log file the please Run This Query

ALTER DATABASE <DataBase Name>
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (DataBase Name_log, 1);
GO
sp_helpdb <DataBase Name>
GO
ALTER DATABASE <DataBase Name>
SET RECOVERY FULL
GO


Please Use again Step 1

Read More

Remove Deadloack Condition in sql server

Use this query to drop all kind of Transaction Deadlock in Sql Server

ALTER DATABASE <DataBase Name>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE <DataBase Name>
SET MULTI_USER;

Read More

Kill Sleeping Threads in SQL to Remove DeadLock Condition

 Use This Process to Kill sleeping Threads in sql


CREATE proc [dbo].[KillProcess]               
as             
begin    
 declare @msrno1 varchar(10) ,@spid varchar(110)='',@count int =0, 
 @mysql varchar(100)='' 
  
      
   DECLARE  C21 CURSOR FOR select spid from sysprocesses where status = 'sleeping' and   cmd='AWAITING COMMAND' order by   spid 
   OPEN C21     
   FETCH FROM C21 INTO @msrno1      
   WHILE @@FETCH_STATUS = 0 BEGIN 
   set @count=0  
   set @mysql=''    
      select @count= count(1) from sysprocesses where spid=@msrno1 and  status = 'sleeping'   and         cmd='AWAITING COMMAND' 
      if @count>0 
      begin 
      set @mysql= 'kill '+ cast(@msrno1 as varchar(10))  
      exec( @mysql) 
      end 
        
   FETCH FROM C21 INTO @msrno1      
   END     
   CLOSE C21     
   DEALLOCATE C21     
     
    
         
          
  Select 'Done'          
          
            
end

Read More

Replace Null values in sql server

This Function is used in replacing null or blank value as 0

select coalesce(@param, 0)
this gives result 0 when @param is null or blank

Read More

Reset Identity Column in sql server

Here is the query to reset identity column value


DBCC CHECKIDENT( <TableName> , RESEED, <Values>)

<Table Name> :- Table Name in which you want to reset identity column.
<Values> :- This is value by which you want to increment column value
                   eg. this may 1   (It increment values by one )

Read More

Copy One table data to next table...

Step 1 . If destination table have any identity column then firstly off identity by this query

SET IDENTITY_INSERT DestinationTable ON

Step 2  Put this query in editor

 Insert into db.DestinationTable (clomunname1,clomunname2,clomunname3)
select clomunname1,clomunname2,clomunname3 from SourceTable 

.... Data Copied

Step 3  Please On its identity Column

SET IDENTITY_INSERT DestinationTable OFF

Read More

Table having more than one primary key

CREATE TABLE dbo.tablename (
  columnname1 INT ,
  columnname2 INT  
primary key (columnname1 , columnname2 ),
);

Read More

Sql Solutions: This is the Query to delete duplicate values from ...

Sql Solutions: This is the Query to delete duplicate values from ...: This is the Query to delete duplicate values from a table...... delete FROM  <tablename> WHERE <tablename>.%%physloc%%  NOT...

Read More

This is the Query to delete duplicate values from a table......


delete FROM  <tablename> WHERE <tablename>.%%physloc%%
 NOT IN (SELECT MIN(b.%%physloc%%)  FROM   <tablename>  as b
 GROUP BY b.columnname1,b.columnname2) and   <put here your condition for table>

Read More

Here we are with unicode char type

nchar	Maximum length of 4,000 characters
nvarchar	Maximum length of 4,000 characters
nvarchar(max)	Maximum length of 231characters
ntext	Maximum length of 1,073,741,823 characters.

We will be next with another data Type.....

Read More

Data Type A  Char type

char	Maximum length of 8,000 characters.
varchar	Maximum of 8,000 characters.
varchar(max)	Maximum length of 231characters
text	maximum length of 2,147,483,647 characters.

On Next We will with unicode Char Type

Read More