“Hey Database dude, I need you to update the PDADE33e433 Table with values for columns GrAnDaMa12333 and WTF32323 with values ‘ASdjendnd’ and ‘BarfFartwekljweljk3′. Cool? Thanks.’
I work with engineers who use databases. They design all the products that my company sells. They are really good at designing but…they use databases. For them, I am aÂ necessary evil. We all get along very well but they would rather be designing products than deal with storing information. Did I mention it’s one of me to about 300 of them?
With those odds it’s very hard to keep track of what they are doing in their databases. With supporting over 100 databases (possibly more, only on month 3) I am still finding it a hard time to try to find out how they are building these databases (that’s a whole different blog entry).
What I do know is that I get requests to change data that they don’t have access to. When this happens I have to figure out where this data is. Most of the time they tell me a server and a table. I always say “Sure, no problem, just make sure you send me an email with the details”
This is where my journey begins. To save time I have built a stored proc that I have been installing in all the master DBs of all the instances. This SP checks all columns in all databases to see if one is LIKE the keyword that I pass in. Feel free to use it. NOTE: it also gives you view names.
SET ansi_nulls ON GO SET quoted_identifier ON GO /* -- ============================================= -- Author: Â Â Adam Mikolaj-- Create date: 19 May 2011 -- Description: Â Use this SP to search for a column in every database on your server. -- I created this because sometimes the I have no idea what database a column I need is in. -- This quickly searches all your DBs -- Sample - exec uSP_WheresThatColumn 'Birthdate' -- ============================================= */ CREATE PROCEDURE Usp_wheresthatcolumn @COLUMN VARCHAR(255)AS BEGIN SET nocount ON; CREATE TABLE #whereisit( database_name VARCHAR(255), TABLE_NAME VARCHAR(255), column_name VARCHAR(255)) DECLARE @SQL VARCHAR(MAX) SET @SQL = 'insert into #whereisit exec sp_MSforeachdb '' use [?] select ''''?'''' as DATABASE_NAME , TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like ''''%'+@COLUMN+'%'''' and TABLE_NAME not like ''''#%'''' '' ' EXEC (@SQL) SELECT * FROM #whereisit DROP TABLE #whereisit END GO
To call it just type in master..uSP_WheresThatColumn ‘column name’