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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s