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;