I ahve modified the querry as the execution satement was missing in it.
set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity')
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0
Exec sp_detach_db @dbname --- This Line was missing in the querry included that and it worked wonderfully
print 'print ''Detach of ' + upper(@dbname) + ' database successfully completed'''
PRINT ' '
FETCH NEXT FROM rs_cursor INTO @dbname
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully detached'
any querries mail me @ email@example.com. I will try to provide my help ASAP.