SQL SERVER – Cursor to Kill All Process in Database

When you run the script please make sure that you run it in different database then the one you want all the processes to be killed.

———————————————————————-CREATE TABLE #TmpWho(spid INT, ecid INT, status VARCHAR(150), loginameVARCHAR(150),hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmdVARCHAR(150))INSERT INTO #TmpWhoEXEC sp_whoDECLARE @spid INT
DECLARE @tString VARCHAR(15)DECLARE @getspid CURSOR
SET @getspid =   CURSOR FOR
SELECT spidFROM #TmpWhoWHERE dbname = 'mydb'OPEN @getspidFETCH NEXT FROM @getspid INTO @spidWHILE @@FETCH_STATUS = 0BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))EXEC(@tString)FETCH NEXT FROM @getspid INTO @spidEND
CLOSE @getspidDEALLOCATE @getspidDROP TABLE #TmpWhoGO
———————————————————————–

SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer

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

SQL SERVER – ASCII to Decimal and Decimal to ASCII

ASCII – Returns the ASCII code value of the leftmost character of a character expression.

CHAR – Fixed-length non-Unicode character data with length of n bytes.

Examples:
----Decimal to ASCIISELECT CHAR(80)+CHAR(73)+CHAR(78)+CHAR(65)+CHAR(76) ASSQLAuthorityAuthor
GO
----ASCII to DecimalSELECT ASCII('P') AS SQLAuthorityAuthorUNION ALLSELECT ASCII('I')UNION ALLSELECT ASCII('N')UNION ALLSELECT ASCII('A')UNION ALLSELECT ASCII('L')GO







SQL SERVER – Script to Find Leap Year

A leap year has 366 days. A leap year has 29 days for February month. Suppose you want to find if year is Leap year or not, you can use many methods. But this is one of the simplest methods. In our example I have passed the year 2000 as a variable to @year but you can change it to any year which you want to check for leap year.

DECLARE @YEAR SMALLINTSET @YEAR=2000SELECT @YEAR AS YEAR,CASE
DAY(EOMONTH(DATEADD(DAY,31,DATEADD(YEAR,@YEAR-1900,0))))WHEN 29 THEN 'YES' ELSE 'NO'END AS LEAP_YEAR
GO

The result is

YEAR LEAP_YEAR
 ------ ---------
 2000 YES

The logic is from the given year create a Date which results for Feb 01 of that year. You can do this by adding 31 (DATEADD(YEAR,@YEAR-1900,0) results for Jan 01 of that year). Using EOMONTH finds the last day of the month. Using DAY function find out day value. If it is 29, it is a leap year otherwise it is non Leap year.