How to searh for a string in all stored procedures within a DB

We recently had to migrate from PCT commissioning to CCG. Because of this I had to search all stored procedures within our data warehouse to check where the commissioner was being set. As our data warehouse is very large with hundreds of stored procedures this was an incredible feat to do manually. The SQL script below really helped me.

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 '%' + '<The string you want to search for>' + '%'
  AND TYPE = 'P'
GO

Just replace the <The string you want to search for> with the string you want to search. See example below


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 '%' + 'PrimaryCareTrust' + '%'
  AND TYPE = 'P'
GO

How to find a column within a table

How to find a column within a table.

If like me you are working on a SQL Server data warehouse with hundreds of columns within each table, it can get really frustrating when you are trying to look for a specific column that you do not know the exact name. I wrote this little script below to help me speed up the process of writing queries. It comes in really handy when working on datasets like SUS downloads, where I do not know the exact column names.

SELECT
  *
from
  information_schema.COLUMNS
where
  column_name like '%<field name to search for>%'
  and table_name = '<table name to search>'

Just replace the <field name to search for> for the columns name that you would like to search for and replace the <table name to search>.  see example below:


SELECT
  *
from
  information_schema.COLUMNS
where
  column_name like '%Clinic%'
  and table_name = 'tbl_sus_opa_download'

Hope this helps you out; and please let me know on the comments how you get on. Thanks.