Wednesday, September 13, 2017

Finding Stuff in SQL Server

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]