When it comes to making changes to a database schema, one of the biggest concerns is how it will affect the applications that rely on it. At my company, we are fortunate to have all SQL code encapsulated into stored procedures, making it fairly straightforward to search for dependencies. However, we recently encountered an issue where our search tool didn’t show any procedures that referenced a particular table. After some investigation, we discovered a subtle flaw in our search process.

The problem arose because the source code for stored procedures, triggers, and other objects is stored in the syscomments table as an NVARCHAR(4000) data type. When a procedure exceeds 4000 characters, the source code spans multiple rows in the syscomments table. Occasionally, this multi-row split occurs right in the middle of the string we are searching for, causing our search tool to miss the dependency.

To overcome this issue, we needed to concatenate the rows and return the full string for each object ID. We wanted to avoid using loops and cursors wherever possible to ensure optimal performance. Here is a solution that works for SQL Server 2005:

SELECT o.Name, o.Type
FROM
(
SELECT id,
 CAST(COALESCE(MIN(CASE WHEN sc.colId = 1 THEN sc.text END), '') AS NVARCHAR(max)) +
 CAST(COALESCE(MIN(CASE WHEN sc.colId = 2 THEN sc.text END), '') AS NVARCHAR(max)) +
 -- ... Repeat the above line for colId 3 to 27 ...
 CAST(COALESCE(MIN(CASE WHEN sc.colId = 27 THEN sc.text END), '') AS NVARCHAR(max)) [text]
FROM syscomments SC
WHERE SC.colId IS NOT NULL
GROUP BY id
) C
INNER JOIN sysobjects O
ON C.id = O.id
WHERE C.text LIKE '%YourSearchString%'

Unfortunately, this code will not work for versions of SQL Server prior to 2005, as they do not support the VARCHAR(MAX) data type. Additionally, if you have very large procedures (exceeding 100,000 bytes), a code change is required.

For those running SQL Server 2000 or earlier, here is an alternative solution:

SELECT DISTINCT O.Name, O.Type
FROM
(
SELECT Id,
  CAST(COALESCE(MIN(CASE WHEN sc.colId = Num-1 THEN sc.text END), '') AS VARCHAR(8000)) +
  CAST(COALESCE(MIN(CASE WHEN sc.colId = Num THEN sc.text END), '') AS VARCHAR(8000)) AS [text]
FROM syscomments SC
INNER JOIN numbers N
ON N.Num = SC.colid
OR N.num-1 = SC.colid
WHERE N.Num < 30
GROUP BY id, Num
) C
INNER JOIN sysobjects O
ON C.id = O.Id
WHERE C.TEXT LIKE '%YourSearchString%'

This solution utilizes a numbers table to concatenate two rows at a time, converting the NVARCHAR(4000) to a VARCHAR(8000). By concatenating row 1 & 2, 2 & 3, 3 & 4, and so on, we can search the row edges. However, please note that this solution is still quite expensive in terms of performance.

Regardless of the version of SQL Server you are using, it is important to consider the performance implications of searching for dependencies. These queries can be resource-intensive, especially when dealing with large databases. It is always a good idea to optimize your search queries and consider alternative approaches if necessary.

In conclusion, there are various ways to tackle the issue of searching for dependencies in SQL Server. The solutions provided here are just a starting point, and there may be other, more efficient approaches. It is always a good idea to explore different options and choose the one that best fits your specific requirements.

I hope you found this article helpful in understanding SQL Server dependencies. If you have any questions or suggestions, I would love to hear from you.