One of the many hats I wear as a software consultant is that of database developer. Part of that entails the discovery of where data is stored, or how it integrates with existing software systems.
For example, suppose you need to find any reference to social security number in your client’s database. You’d need to find references to variations of that, like “SSN” or “Social” in tables, views, stored procedures, and user-defined functions.
So, I created these templates in the Template Browser for SQL Server Management Studio, just to accelerate this process. The queries use system views to find SQL Server objects based on LIKE statements.
Find text in table columns
SELECT ss.[name] + '.' + st.[Name], sc.[Name]
FROM sys.columns AS sc
INNER JOIN sys.tables AS st ON st.object_id = sc.object_id
INNER JOIN sys.schemas AS ss ON ss.schema_id = st.schema_id
WHERE sc.[Name] LIKE '%SSN%'
ORDER BY ss.[name], st.[Name], sc.[Name]Find text in views
SELECT ss.[Name] + '.' + sv.[Name]
FROM sys.views AS sv
INNER JOIN sys.schemas AS ss ON ss.schema_id = sv.schema_id
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%SSN%'
ORDER BY ss.[Name], sv.[Name]Find text in stored procedures
SELECT ss.[Name] + '.' + sp.[Name]
FROM sys.procedures AS sp
INNER JOIN sys.schemas AS ss ON ss.schema_id = sp.schema_id
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%SSN%'
ORDER BY ss.[Name], sp.[Name]Find text in user-defined functions
SELECT ss.[Name] + '.' + so.[Name]
FROM sys.objects AS so
INNER JOIN sys.schemas AS ss ON ss.schema_id = so.schema_id
WHERE so.type IN ('FN', 'IF', 'TF')
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%SSN%'
ORDER BY ss.[Name], so.[Name]