Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 28 November 2013

Query to find SP name and Table name and object name

Query to find SP name and Table name and object name


SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'cid' + '%'



SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CId' + '%'



SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'CId' + '%'
AND TYPE = 'P'



SELECT table_catalog,table_name,column_name,data_type,column_default
FROM information_schema.columns

WHERE column_name LIKE '%' + 'CId' + '%'