Find a Keyword in all Stored Procedures or whole Database

Find all Stored Procedures that reference a Column, Table Name or any keyword.

Using this Query You can search for a column/table name and any other keyword used and similar keywords in all the procedures. Just replace the text 'Name Your Column Here' in the query and you will get the list of procedure names and the procedure content as a result set.

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 '%' + 'Name Your Column Here' + '%'

AND TYPE = 'P'

Search a Column Name available in whole Database

using this query you can search for a column name or similar column names in whole database.

Just replace the text 'Name Your Column Here' with the keyword that you want to search.

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%Name Your Column Here%'

ORDER BY schema_name, table_name

Find all Table_Name , Columns and Data Type available in the Database?

using this query you can get a complete list of Table name, column name and data type of column in whole database.

SELECT SysObjects.[Name] as TableName,

SysColumns.[Name] as ColumnName,

SysTypes.[Name] As DataType,

SysColumns.[Length] As Length

FROM

SysObjects INNER JOIN SysColumns

ON SysObjects.[Id] = SysColumns.[Id]

INNER JOIN SysTypes

ON SysTypes.[xtype] = SysColumns.[xtype]

WHERE SysObjects.[type] = 'U'

ORDER BY SysObjects.[Name]