Oct 29

Forgot MSSQL password

Sometime we forgot sa password of MSSQL.
sometime we ignore password while installation.

We can change ‘sa’ password, but we need to open ‘Enterprise manager or SQL management studio’ and authenticate with Windows authentication.
One should login on windows in that user, which was used to install MsSql
or definitely ‘Administrator’ user can do anything :)

if you are authenticate in MSSQL, open new query, ( master database must be used, or we are running query on master database)

alter login sa with password='abc123#'

execute above code,
now you can login with sa user with password:abc123#

Cheers

Sep 13

Drop all store procedures in MSSQL

Delelting all user defined procedures in MSSQL

--Use own database first
-- Use MyDatabase
DECLARE @str VARCHAR(100)
DECLARE deleteProcedure_cursor Cursor FOR 
SELECT 'Drop Procedure ' + SCHEMA_NAME(schema_id)+'.['+name +']' AS Drop_procedure_SQL 
FROM sys.procedures
OPEN deleteProcedure_cursor
fetch NEXT FROM deleteProcedure_cursor INTO @str
while @@FETCH_STATUS=0
BEGIN
	print  @str
	EXEC (@str)
fetch NEXT FROM deleteProcedure_cursor INTO @str
END
close deleteProcedure_cursor
 
DEALLOCATE deleteProcedure_cursor
Sep 13

MSSql server function to split string with given character

The function will split a string with respect to the character provided
For example we have a string ‘ram,shyam,hari,gopal’ and we need to split it with comma
Then we need to call dob.split(‘ram,shyam,hari,gopal’,',’)

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 09/13/2010 15:49:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))       
  RETURNS @temptable TABLE (items VARCHAR(8000))       
AS       
    BEGIN       
     DECLARE @idx INT       
    DECLARE @slice VARCHAR(8000)       
 
    SELECT @idx = 1       
         IF len(@String)<1 OR @String IS NULL  RETURN       
 
      while @idx!= 0       
       BEGIN       
         SET @idx = charindex(@Delimiter,@String)       
         IF @idx!=0       
             SET @slice = LEFT(@String,@idx - 1)       
        ELSE       
             SET @slice = @String       
 
         IF(len(@slice)>0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       
 
         SET @String = RIGHT(@String,len(@String) - @idx)       
          IF len(@String) = 0 break       
     END   
   RETURN       
 END