Finding your way around an unfamiliar database

It can sometimes be difficult to find where various values are being stored in a database you are unfamiliar with, this is a T-SQL script I wrote some time ago when I was trying to find my way around a TechOne database and it came in handy back then and it recently came in handy once again when I was querying a Connectwise database.

What this script does is it cursors through all the tables and all the columns on those tables and counts the occurrences of the value you are searching for, and then gives you a result set listing which columns on which tables contain that value.

 

Warning: Think carefully before running this against your databases, this could cause performance issues.

The following query is very handy for finding which stored procedures, views & functions are referencing various values:

P = Stored procedure
V = View
TF = Table function
FN = Scalar function
AF = Aggregate function (CLR)
TR = SQL DML Trigger

You can find a full list of xtype values here: https://msdn.microsoft.com/en-AU/library/ms177596.aspx

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s