sql to find all references of a particular column in a db?
-
I need to write some SQL to find all references of a particular column in a db. The column that I'm trying to find references to exists in a different database. I've found a few examples of finding references of a column that exist in the *same* db:
https://stackoverflow.com/questions/1883700/in-sql-server-how-can-i-find-everywhere-a-column-is-referenced
But I'm having problems figuring out how to do this for a column that exists in a *different* database. Can you provide the SQL for this? For example purposes, let's call the external column:
MyExternalDB.MyExternalSchema.MyExternalTable.MyExternalColumn - sqlguy-736318 - Monday, November 20, 2017 1:24 PM
Actually one of the answers in that thread DOES address cross database dependencies. Check the following documentation on sys.sql_expression_dependencies - it also has a sample script for cross database. But keep in mind a lot of the dependency with any of those can be missed. It's all better than the old way of using sysdepends though:
sys.sql_expression_dependenciesSue
-
I have to do this from often enough that I actually created a snippet. This code searches through all routines (stored procs, functions, triggers) for whatever your are looking for.
This can be easily modified to include the routine_type (I don't include it because I don't need it). Note the '<@objectName,,>' and '<padding,,>' those are SSMS Template parameters. You press CTRL+M to fill them out like a form. You can just replace this with static values if you'd like.-- (1) Update this psuedo parameter (variable) with name of object you're searching for
declare @objectName nvarchar(1000) = '<@objectName,,>';
declare @padding int = <padding,,>; -- used for the "preview column"begin
-- (2) prepare table to house the resultset
if object_id('tempdb..##blah') is not null drop table ##blah;
select top (0) db = db_name(), *,
obj_pos = cast(null as int), obj_def = cast(null as varchar(max))
into ##blah
from INFORMATION_SCHEMA.ROUTINES;-- (3) prepare dynamic SQL statement
declare @SQL nvarchar(500) =
N' use [?];
insert ##blah
select db = ''?'', *, patindex(''%''+'''+@objectName+'''+''%'',
object_definition(object_id(routine_schema+''.''+routine_name))),
object_definition(object_id(routine_schema+''.''+routine_name))
from INFORMATION_SCHEMA.ROUTINES
where object_definition(object_id(routine_schema+''.''+routine_name)) like ''%''+'''+
@objectName+'''+''%'';'
-- (4) execute dynamic SQL
exec sp_msForEachDB @sql;-- (5) result set and cleanup
select db, routine_schema, routine_name, preview = substring(obj_def, obj_pos-@padding, (@padding*2)+len(@objectName))
from ##blah order by routine_name;
drop table ##blah;
end;Here's the resultset on my PC when doing a search using thes parameters:
declare @objectName nvarchar(1000) = 'itemindex';
declare @padding int = 25; -- used for the "preview column"Returns
Lastly - sp_msForEachDB is undocumented. There's better alternatives out there; I was just being lazy the day I wrote this for myself.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply