Find Things Pointing at Production

Not only is it just a bad idea for things in your lower

environments to be pointing at Production, it could be a sign that someone is using it for Production, which, depending on what your version of SQL Server

is, could easily be a licensing violation. 

Not to mention the eleventy billion other reasons why you just don’t

want this to happen. Period.

Help is on the way.  Here you go:

DECLARE @searchParam NVARCHAR(50);
SET @searchParam = N'<ServerNameHere>'; -- Do not include []. They are perceived as patindex searches
SELECT SPECIFIC_CATALOG + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME AS ObjectName,
       ROUTINE_TYPE AS ObjectType
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE N'%' + @searchParam + '%'
UNION ALL
SELECT DB_NAME() + '.' + SCHEMA_NAME(schema_id) + '.' + name,
       'SYNONYM'
FROM sys.synonyms
WHERE base_object_name LIKE N'%' + @searchParam + '%'
UNION ALL
SELECT name,
       'Linked Server'
FROM sys.servers
WHERE name = @searchParam;