Feb 9, 2012

Sql query to validate or check syntax of sql statement in sql server


Sql query  or stored procedure to validate or check only syntax of sql statement without executing the query in sql server

CREATE PROC ValidateSqlSyntax(
@CustomQuery AS NVARCHAR(MAX),
@StatusMsg AS VARCHAR(MAX) OUTPUT
)
AS
BEGIN


BEGIN TRY

IF @CustomQuery IS NOT NULL BEGIN 

--SET @CustomQuery = REPLACE(@CustomQuery,'''','''''')
SET @CustomQuery =  'SET NOEXEC ON; ' + @CustomQuery + ' ; SET NOEXEC OFF;'

END

EXECUTE SP_EXECUTESQL @CustomQuery

SET @StatusMsg = 'Success'

END TRY
BEGIN CATCH
SET @StatusMsg = ERROR_MESSAGE() 
END CATCH
END


To execute:


DECLARE @StatusMsg AS VARCHAR(MAX)
EXECUTE ValidateSqlSyntax 'UPDATE tblTest SET Id =5;SELECT 1', @StatusMsg OUTPUT
SELECT @StatusMsg


Note: Stored procedure  ValidateSqlSyntax will check only syntax of sql queries. I will not check the objects name,columns name etc. It will validate the column name only if is already present in the database. 

Sql query to get all stored procedures name which contains given text or string in sql server

Sql query or script to get or list or search all the user defined stored procedures name which contains or keeps given text OR strings in sql server



DECLARE @SearchText AS VARCHAR(100)
SET @SearchText = 'ntLogID'


SELECT name  
FROM Sys.procedures 
WHERE OBJECT_DEFINITION([object_id]) 
LIKE '%' + @SearchText + '%'


or



SELECT name  
FROM Sys.procedures 
WHERE CHARINDEX(@SearchText, OBJECT_DEFINITION([object_id])) > 0