Find Stored Procedures containing a text

Somewhere or Sometimes, we need to Access/Update/Delete all the Stored Procedures which contain some specific text in the name of DataObjects (like SP or Function) or in the definition of DataObjects. In such cases, first we need to get the list of all DataObjects containing the text. Now to get the list of such DataObjects which contain the text in their names, we will write the following query :

 

--#####################################################################################

SELECT      ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION 
FROM        INFORMATION_SCHEMA.ROUTINES 
WHERE       ROUTINE_NAME LIKE '%NAME%'
            AND ROUTINE_TYPE ='PROCEDURE'
ORDER BY	CREATED

--#####################################################################################
    
 

Here in where clause "ROUTINE_TYPE ='PROCEDURE'" is given to search for Stored Procedures only. Now if you like to search a text 'NAME' in the definition of the DataObjects, then the query will be a bit changed as :

  

--#####################################################################################

SELECT      ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION 
FROM        INFORMATION_SCHEMA.ROUTINES 
WHERE       ROUTINE_DEFINITION LIKE '%ghost%'
            AND ROUTINE_TYPE ='PROCEDURE'
ORDER BY	CREATED'

--#####################################################################################
    
 

Some other ways to find the same output is as below :

  

--#####################################################################################

SELECT	OBJECT_NAME(OBJECT_ID) AS ObjectName, Definition
FROM	SYS.SQL_MODULES
WHERE	OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1
		AND DEFINITION LIKE '%ghost%'
ORDER BY OBJECT_ID

SELECT	OBJECT_NAME(ID) AS ObjectName, Text
FROM	SYSCOMMENTS 
WHERE	[TEXT] LIKE '%ghost%' 
		AND OBJECTPROPERTY(ID, 'IsProcedure') = 1 
ORDER BY ID

--#####################################################################################
    
 

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Numeric Characters Only in SQL Server 2005, 2008, 2012

In SQL Server 2005, 2008, 2012 if you wish to find all the rows with a particular field having Numeric values only then give a simple check as below :

SELECT * FROM CustomerDetails (NOLOCK)
WHERE MobileNo NOT LIKE '%[^0-9]%'

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Remove Tab, Newline Character From Data In SQL Server

In SQL Server 2005, 2008, 2012, If you are facing some inconsistency with data while selecting and other operations and find that this is due to tab or newline characters, then just replace them with blank.

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

CHAR(9) - Tab
CHAR(10) – LineFeed
Char(13) - CarriageReturn

CR ("carrige return") is ASCII code 13, and means "go back to the beginning of the line". It tells the Teletype machine to bring the print head to the left.

LF ("Line Feed") is ASCII code 10, and tells the printer to move the paper up 1 line.


Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Email Validation in SQL In SQL Server 2005, 2008, 2012

In SQL Server 2005, 2008, 2012 for email validation I found that either you can write a plane query as below :

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'

Or can write a function as below : "

CREATE FUNCTION dbo.ValidateEmail(@EMAIL VARCHAR(100))

RETURNS BIT AS
BEGIN     
  DECLARE @bitRetVal AS BIT
  IF (@EMAIL <> '' AND @EMAIL NOT LIKE '_%@__%.__%')
     SET @bitRetVal = 0  -- Invalid
  ELSE 
    SET @bitRetVal = 1   -- Valid
  RETURN @bitRetVal
END

Reference: Govind Badkur(http://sqlserver20.blogspot.com)