Find keyword used in Stored Procedure, Function and View in SQL SERVER

Here is the query to find any keyword used in definition of Stored Procedure, Function and View

Replace keyword in @Keyword Vairable

DECLARE @Keyword Varchar(50)
SET @Keyword = 'Association'
SELECT 'SP' as [Type], Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' +@Keyword+ '%'
Union
SELECT 'VW' as [Type], Name FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' +@Keyword+ '%'
Union
SELECT 'FN' as [Type], ROUTINE_NAME as Name  FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' And ROUTINE_DEFINITION Like '%' + @Keyword + '%'
Union
SELECT 'SP' as [Type], ROUTINE_NAME as Name  FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' And ROUTINE_DEFINITION Like '%' + @Keyword + '%'
Posted in Uncategorized. Leave a Comment »

Find dependency refering a table in SQL SERVER

Here is the query to know the dependency of table in sql server.
Just replace the @TableName variable.


DECLARE @TableName Varchar(50)
SET @TableName = 'Association'
SELECT  
    DISTINCT 
    ISNULL(sd.referenced_schema_name+'.','')+ OBJECT_NAME(sd.referenced_id) As TableName,
    OBJECT_NAME(sd.referencing_id) As ObjectName,
    CASE 
	WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsUserTable') = 1  THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction') = 1  THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction') = 1  THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsScalarFunction') =1  THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTrigger')= 1  THEN'Trigger'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsView')= 1  THEN'View'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsUserTable')= 1 THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsProcedure')= 1 THEN'Procedure'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsIndexed')= 1 THEN'Index'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsForeignKey')= 1 THEN'ForeignKey'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsPrimaryKey')= 1 THEN'PrimaryKey'
    END AS ObjectType
FROM    
    SYS.SQL_EXPRESSION_DEPENDENCIES SD
    INNER JOIN SYS.OBJECTS obj ON obj.object_id=sd.referenced_id
WHERE   
    obj.is_ms_shipped= 0
    AND referenced_id=object_id(@TableName) /*Where one can Replace table Name*/
    AND obj.type_desc='USER_TABLE'
ORDER BY 
    TableName,ObjectName,ObjectType

Posted in Uncategorized. Leave a Comment »

FInd text in Stored Procedure, Function, View or Trigger

Following query can be use to search any text in SQL Server –

SELECT DISTINCT
s.name+’.’+o.name AS Object_Name,o.type_desc, o.[Type]
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE m.definition Like ‘%FN_Accounts_GetLedgerOpeningBalance%’
–AND o.Type=’P’ –<uncomment if you only want to search procedures
–AND o.Type=’FN’ –<uncomment if you only want to search procedures
ORDER BY [Type]

Posted in Uncategorized. Leave a Comment »