Starting at a new client usually means some exploring in databases to figure out how things work. The system tables can be used to find where a text string is used in stored procedures or column names. The following queries will find where a specific string is used. Just use the database of interest, set a value for the @searchStr variable, and run the query!
declare @searchStr as varchar(50)
set @searchStr = ‘%categoryID%’
–Find string in proc
select distinct OBJECT_NAME(id)
from syscomments
where OBJECTPROPERTY(id, ‘IsProcedure’) = 1
and [text] like @searchStr
order by OBJECT_NAME(id)
–Find table and column by name
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 @searchStr
ORDER BY schema_name, table_name;
Leave a Reply