Search SQL Server for stored procedures in all databases containing text

Search SQL Server for stored procedures in all databases containing text

USE [master]
GO

CREATE PROCEDURE sp_SearchProcs  
(  
 @SearchText VARCHAR(1000)  
)  
AS  
BEGIN  
 DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR    
    SELECT [name]    
    FROM master..sysdatabases   
    WHERE [name] NOT IN ('model', 'tempdb')   
    ORDER BY [name]   
        
 DECLARE @DB sysname    
  
 OPEN curDB    
 FETCH NEXT FROM curDB INTO @DB    
 WHILE @@FETCH_STATUS = 0    
    BEGIN    
     DECLARE @SQL NVARCHAR(MAX) = 'USE [' + @DB +'] SELECT Distinct SO.Name, SC.Text, SS.Name AS [Schema], ''' + @DB + ''' AS [Database] FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID INNER JOIN sys.objects SO2 (NOLOCK) ON SO.id = SO2.object_id INNER JOIN sys.schemas SS ON SO2.schema_id = SS.schema_id WHERE SO.Type = ''P'' AND SC.Text LIKE ''%' + REPLACE(@SearchText, '''', '''''') + '%'' ORDER BY SO.Name'  
     BEGIN TRY  
       exec sp_executesql @SQL  
     END TRY  
     BEGIN CATCH  
     END CATCH  
     FETCH NEXT FROM curDB INTO @DB    
    END    
       
 CLOSE curDB    
 DEALLOCATE curDB  
END

Here's how to use it:

sp_SearchProcs 'insert%into%mytable'

Wildcard session cookies

Some browsers use .domain.com as wildcard, others use domain.com
It is common practice for a user to forget the www and most servers will return the same page for www or non-www.
If a user vists the non-www version of a website & the server returns a sessionID cookie, the browser may consider it a wildcard cookie, thus it will use that same cookie for ALL request to ANY subdomain of that site. Normally, each subdomain would have a separate set of cookies, so the server would generate a different sessionId for each.

Here's a method you can call in the Application_EndRequest method of the global.asax.cs to prevent this from happening.

        public static void RemoveWildcardSessionCookies(this HttpApplication application)
        {
            if (System.Web.HttpContext.Current.Request.IsLocal)
                return;

            string host = System.Web.HttpContext.Current.Request.Url.Host;
            int countOfPeriods = host.Count(x => x == '.');

            HttpCookie sessionCookie = application.Request.Cookies["ASP.NET_SessionId"];
            if (sessionCookie != null && countOfPeriods <= 1)
            {
                sessionCookie.Expires = DateTime.Now.AddDays(-1);
                sessionCookie.Value = "";
                if (System.Web.HttpContext.Current.Session != null)
                    System.Web.HttpContext.Current.Session.Abandon();
            }
        }