Query to look for text in procedures

This is a query that I use every day! Say, you've changed a datatype of a field, or renamed it, and now you need to change it everywhere where it's been called from procedures. Or you have hardcoded something (or someone before you did it), and now you need to change it or, better, get rid of it. Anyway, you will have to look for some set of symbols in all your procedures. There are, actually, quite a few samples of how to do this on the Internet, this is just one of them:


USE [YourDataBaseName]
GO
-- create temporary Result table to keep names and text of the procedures in the database :
CREATE TABLE #Result
(TextField varchar(max), ProcName varchar(100))

-- create temporary ProcName table with the names of all the procedures in the database:
CREATE TABLE #ProcList
(ID int IDENTITY, ProcName varchar(100))

-- populate the ProcName table with the procedure names:
INSERT #ProcList SELECT [name] from sys.procedures

-- get the number of procedures (to be used in the loop below):
DECLARE @NumberOfProcs int
SELECT @NumberOfProcs = COUNT(*) FROM sys.procedures

-- loop to populate the Result table:
DECLARE @i INT
SET @i = 1
DECLARE @ProcName varchar(100)
DECLARE @SQL varchar(2000)
WHILE @i <= @NumberOfProcs
BEGIN
SELECT @ProcName = ProcName FROM #ProcList WHERE ID = @i
-- and ProcName like '%proc name%'
SET @SQL = 'INSERT INTO #Result (TextField) EXEC sp_helptext ' + @ProcName
EXEC (@SQL)
UPDATE #Result SET ProcName = @ProcName WHERE ProcName IS NULL
SET @i = @i + 1
END

-- look for a string you need [your string] in the Result table
SELECT * FROM #Result WHERE TextField LIKE '%[your string]%'

-- clean up
DROP TABLE #Result
DROP TABLE #ProcList

--
Additional observations.
1. The general idea is to use the sp_text procedure.
2. Now, Pinal Dave, who runs a very useful blog on SQL server, has something much shorter for this (and guess what, it works just the same, even faster :) ):

USE [YouDatabaseName]
GO
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%[your string]%'

Error message "String or binary data would be truncated"

It is Msg 8152, and it's usually followed by "The statement has been terminated" message. It's actually very easy, still, I learned to get scared of it after, like, 5-th (10-th?) time I had it.
--
What happened. Nothing really bad: you just tried to populate a field with a string longer than the field's designed length. It's good to know: maybe you'd reconsider such an update.
--
Workaround. It you, however, want to stick a long string into a short field, just issues a statement: SET ANSI_WARNINGS OFF. You'll be able to commit updates without further interruption: extra symbols will be silently truncated.
--
Additional observation. One of the web articles explaining this error had a comment: "Profiler is handy in troubleshooting this error. " Never had a change to find out what this means... (feels like it's worth trying)

Error message "The row value(s)... alter multiple rows"

The full text of the error message is following:

"No row was not updated.
The data in row [number] was not committed.
The row value(s) updated or deleted either do not make the row unique or they alter multiple rows ([number] rows)."
--
How it happened. I received this error message (and, indeed, was not able to commit any updates) when I was trying to change a value through the 'OPEN TABLE' option of SSMS. I saw that I am able to change values in some rows, but other give me this message. How could I alter multiple rows if, while working directly in the open table, you can only update 1 value in 1 row at a time? And, of course, T-SQL statement was allowing to update any values with no error messages.
--
Explanation. It could be, indeed, that you have several rows with all the same values. One of the cases - you've recently dropped an identity field. Another case - you've just made changes to a row that turned it into a duplicate of another row. It is allowed, however, this row immediately turns into a read-only (it has a lighter grey color than other rows), and now, if you try to update its duplicate - you get this error message.
Workaround. There are cases when you want to make some hand-made changes directly in the table. If the situation of a non-uniqueness could occur, create a new identity field - then do whatever updates you need - then drop the new identity field.
--
Additional observations. The same can happened if there are constrains that tie a table with other tables, although I did not investigate this case too far.