Paul Davallou gave me this scripts and it is really awesome.I found it really helpful when I know only column name and needs to compute its dependencies in stored proc, table etc.
I am posting this script with Paul D permissions and expect that readers may suggest , how we can improve.
Thank you Paul.
-- Purpose: Which procs, triggers, functions, tables, views, FKs contain a specific string?
-- Can remove unwanted results by commenting out DML and/or DDL searches.
-- Just find/replace the first 'PutSearchValueHere' (below) with a character value you want to search for.
-- If you want to customize for insert, update, or delete, you can use the other examples and
-- comment out the first occurance.
-- Very quick and is more accurate than SSMS dependencies.
-- You may also try to verify using SSMS "Dependencies" or VS Find-in-Files
-- or use Regular Expressions against source management.
-- This will show which procs, triggers and/or functions reference a particular search term.
-- Unfortunately, this logic DOES include matches in commented code,
-- so, if you prefer, validate using SSMS Dependencies or Redgate's SQL Data Trakker
-- or left-justify results from VS find-in-files and sort rows and delete the commented rows.
-- Is relatively quick (seconds) for small searches, but conceivably could take a minute or so (or more).
-- fyi - Search is of entire proc/trigger/func code.
-- Can only match extended stored proc names, not content.
-- Created by Paul Davallou
-- Revision History:
-- ------------------------------------------------------------------------------
-- Date Name JIRA# DescriptionDescription
-- ------------------------------------------------------------------------------
-- 01/01/2007 PDavallou Initial creation
-- 05/31/2013 PDavallou Fixed error where view definitions were not included and tab translation was missing a line of code.
---------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @SearchFor VARCHAR(80), @SearchReplace VARCHAR(80), @SearchIn VARCHAR(30);
DECLARE @myvar varbinary(128), @mycharvar varchar(100);
DECLARE @mysquiggle varbinary(8), @mytab varbinary(8);
DECLARE @view_user_obj bit;
DECLARE @view_system_obj bit;
SELECT @SearchFor = '', @mysquiggle = CONVERT(varbinary,CHAR(126)), @mytab = CONVERT(varbinary,CHAR(09));
-- Result table
DECLARE @OutputTable TABLE([SearchFor] VARCHAR(80),[DATABASE] VARCHAR(80),[ObjNm] VARCHAR(100), [ChildNm] varchar(100) NULL, [CodeType] varchar(20));
--Search DML
-- List of all "code" object types you want to look for within (P,FN,TF,IF)
-- Comment out any you do not want
DECLARE @SearchType TABLE([TYPE] VARCHAR(2));
INSERT @SearchType SELECT 'P'; --Proc
INSERT @SearchType SELECT 'V'; --Proc
INSERT @SearchType SELECT 'FN'; --Scalar function
INSERT @SearchType SELECT 'TF'; --Table function
INSERT @SearchType SELECT 'IF'; --Inline table function
INSERT @SearchType SELECT 'TR'; --Trigger
-- INSERT @SearchType SELECT 'PC'; --Proc Assembly (CLR)
-- INSERT @SearchType SELECT 'AF'; --Aggregate function (CLR)
-- INSERT @SearchType SELECT 'FS'; --Assembly (CLR) scalar function
-- INSERT @SearchType SELECT 'FT'; --Assembly (CLR) table-valued function
-- INSERT @SearchType SELECT 'TA'; --Assembly (CLR) DML trigger
-- Change to 1 if you want system table views.
SELECT @view_system_obj = 0;
-- Change to 1 if you want user objects.
SELECT @view_user_obj = 1;
DECLARE @MySearchTerms TABLE(SearchWrd VARCHAR(60));
-- Many examples below for specific situations.
-- Just uncomment any one you want or use the generic one below.
INSERT @MySearchTerms SELECT 'yourSearchTermHere'; -- could be anything. Keep it simple is best. Don't save your modified copy.
-- Look for any occurance of each SearchTerm
WHILE EXISTS (SELECT SearchWrd FROM @MySearchTerms a
WHERE SearchWrd > @SearchFor)
BEGIN
SELECT @SearchFor = MIN(SearchWrd)
FROM @MySearchTerms a
WHERE SearchWrd > @SearchFor;
-- Handle tabbed Search value (where tab is replaced by '~'
-- SQL Svr differentiates tabs from spaces in LIKE phrases.
SELECT @SearchReplace = '';
IF CharIndex('~', @SearchFor, 1) > 0
BEGIN
-- Replace squiggle 7E with tab 09
-- Done to make grouping of results more meaningful
DECLARE @MyCmd varchar(200); DECLARE @SR Table(TabbedValue varchar(40));
SELECT @SearchReplace = @SearchFor;
SELECT @myvar = CONVERT(varbinary(128),@SearchReplace);
SELECT @mycharvar = REPLACE(master.dbo.fn_sqlvarbasetostr(@myvar),'7E','09');
SELECT @MyCmd = 'SELECT CONVERT(varchar(40), ' + @mycharvar + ')';
--Put in table
INSERT @SR(TabbedValue)
EXEC(@MyCmd)
SELECT @SearchReplace = (SELECT Top 1 TabbedValue from @SR);
DELETE @SR;
END
ELSE
BEGIN
SELECT @SearchReplace = @SearchFor;
END
IF @view_user_obj = 1
BEGIN
-- Uncomment the JOIN on @MyProc if you only want to search names in the @MyProc table above.
-- Optional filter included below for excluding object names having
-- version-name-variations (e.g. Lookup1, Lookup2, or LookupA, LookupB).
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,o.name, 'See Code', o.type
FROM sys.objects o WITH(NOLOCK)
JOIN @SearchType st ON o.[TYPE] = st.[TYPE] COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.sql_modules c WITH(NOLOCK) ON c.[OBJECT_ID] = o.[object_id]
-- JOIN @MyProc mp on o.[name] = mp.[ProcNm] -- use when you have specific names to search
WHERE c.definition LIKE '%' + @SearchReplace + '%'
-- AND c.definition Not LIKE '%[^@]' + @SearchReplace + '[^a-z,^A-Z,^0-9]%'; -- useful for some table name searches
-- TABLES - If you don't want tables, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,t.[name], NULL, 'U'
FROM sys.tables t WITH(NOLOCK)
WHERE t.[name] LIKE '%' + @SearchReplace + '%';
-- VIEWs - If you don't want user views, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,v.[name], NULL, 'V'
FROM sys.views v WITH(NOLOCK)
WHERE v.[name] LIKE '%' + @SearchReplace + '%';
-- COLUMNs - If you don't want table or view columns, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,object_name(c.[OBJECT_ID]), c.[name], o.type + '_Col'
FROM sys.columns c WITH(NOLOCK)
JOIN sys.objects o WITH(NOLOCK) ON c.[OBJECT_ID] = o.[object_id]
WHERE o.type in ('U','V')
AND c.[name] LIKE '%' + @SearchReplace + '%';
-- FKs - If you don't want FKs, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,object_name(fkc.constraint_object_id),object_name(fkc.referenced_object_id), 'F'
FROM sys.foreign_key_columns fkc WITH(NOLOCK)
WHERE object_name(fkc.referenced_object_id) LIKE '%' + @SearchReplace + '%';
-- Extended Stored Procs - If you don't want Extended Stored Procs, comment this out.
-- Cannot search the code to find the search term since these are DLLs.
INSERT @OutputTable
SELECT @SearchFor ,'master' ,o.[name], NULL, 'X'
FROM master.sys.objects o WITH(NOLOCK)
WHERE o.[name] LIKE '%' + @SearchReplace + '%'
AND o.type = 'X';
END
IF @view_system_obj = 1
BEGIN
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,o.name, 'See Code', o.type
FROM sys.system_objects o WITH(NOLOCK)
JOIN @SearchType st ON o.[TYPE] = st.[TYPE] COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.system_sql_modules c WITH(NOLOCK) ON c.[OBJECT_ID] = o.[object_id]
WHERE c.definition LIKE '%' + @SearchReplace + '%'
-- VIEWs - If you don't want system views, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,v.[name], NULL, 'SV'
FROM sys.system_views v WITH(NOLOCK)
WHERE v.[name] LIKE '%' + @SearchReplace + '%';
END
-- Identify replication-related procs that we care about
UpDATE @OutputTable
SET CodeType = 'Repl Proc'
WHERE [ObjNm] LIKE 'sp_MS%';
-- Remove irrelevant system items
DELETE @OutputTable
WHERE [ObjNm] in ('sp_alterdiagram'
,'sp_creatediagram'
,'sp_drop_constraints'
,'sp_dropdiagram'
,'sp_helpdiagrams'
,'sp_helpdiagramdefinition'
,'sp_renamediagram'
,'sp_upgraddiagrams'
,'sp_helpdiagramdefinition'
,'sp_certify_removable'
,'dt_generateansiname'
,'fn_diagramobjects'
,'sysdiagrams'
);
END
-- Get unique occurance of proc name grouped by format of search term
-- I made up SU and SV for my own convenience.
SELECT a.SearchFor
, a.[DATABASE]
, a.[ObjNm]
, a.[ChildNm]
, CASE
WHEN a.[CodeType] = 'P' AND a.[ObjNm] LIKE 'sp_MS%' THEN 'ReplProc'
WHEN a.[CodeType] = 'P' THEN 'Proc'
WHEN a.[CodeType] = 'IF' THEN 'Tbl Func'
WHEN a.[CodeType] = 'TF' THEN 'Tbl Func'
WHEN a.[CodeType] = 'FN' THEN 'Scalar Func'
WHEN a.[CodeType] = 'TR' THEN 'Trigger'
WHEN a.[CodeType] = 'PC' THEN 'Proc (CLR)'
WHEN a.[CodeType] = 'AF' THEN 'Aggregate Func (CLR)'
WHEN a.[CodeType] = 'FS' THEN 'Scalar Func (CLR)'
WHEN a.[CodeType] = 'FT' THEN 'Tbl Func (CLR)'
WHEN a.[CodeType] = 'TA' THEN 'DML Trigger (CLR)'
WHEN a.[CodeType] = 'F' THEN 'FK'
WHEN a.[CodeType] = 'U' THEN 'Table'
WHEN a.[CodeType] = 'X' THEN 'Ext Stored Proc'
WHEN a.[CodeType] = 'V' THEN 'View'
WHEN a.[CodeType] = 'SV' THEN 'System View'
WHEN a.[CodeType] = 'U _Col' THEN 'TableCol'
WHEN a.[CodeType] = 'V _Col' THEN 'ViewCol'
ELSE a.[CodeType]
END as 'Type'
FROM @OutputTable a
--JOIN @MyProc b on a.[ObjNm] = b.[ObjNm]
WHERE [SearchFor] IS NOT NULL
GROUP BY [SearchFor], [DATABASE], [ObjNm], [ChildNm],[CodeType]
ORDER BY 1,2,5,3;
I am posting this script with Paul D permissions and expect that readers may suggest , how we can improve.
Thank you Paul.
-- Purpose: Which procs, triggers, functions, tables, views, FKs contain a specific string?
-- Can remove unwanted results by commenting out DML and/or DDL searches.
-- Just find/replace the first 'PutSearchValueHere' (below) with a character value you want to search for.
-- If you want to customize for insert, update, or delete, you can use the other examples and
-- comment out the first occurance.
-- Very quick and is more accurate than SSMS dependencies.
-- You may also try to verify using SSMS "Dependencies" or VS Find-in-Files
-- or use Regular Expressions against source management.
-- This will show which procs, triggers and/or functions reference a particular search term.
-- Unfortunately, this logic DOES include matches in commented code,
-- so, if you prefer, validate using SSMS Dependencies or Redgate's SQL Data Trakker
-- or left-justify results from VS find-in-files and sort rows and delete the commented rows.
-- Is relatively quick (seconds) for small searches, but conceivably could take a minute or so (or more).
-- fyi - Search is of entire proc/trigger/func code.
-- Can only match extended stored proc names, not content.
-- Created by Paul Davallou
-- Revision History:
-- ------------------------------------------------------------------------------
-- Date Name JIRA# DescriptionDescription
-- ------------------------------------------------------------------------------
-- 01/01/2007 PDavallou Initial creation
-- 05/31/2013 PDavallou Fixed error where view definitions were not included and tab translation was missing a line of code.
---------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @SearchFor VARCHAR(80), @SearchReplace VARCHAR(80), @SearchIn VARCHAR(30);
DECLARE @myvar varbinary(128), @mycharvar varchar(100);
DECLARE @mysquiggle varbinary(8), @mytab varbinary(8);
DECLARE @view_user_obj bit;
DECLARE @view_system_obj bit;
SELECT @SearchFor = '', @mysquiggle = CONVERT(varbinary,CHAR(126)), @mytab = CONVERT(varbinary,CHAR(09));
-- Result table
DECLARE @OutputTable TABLE([SearchFor] VARCHAR(80),[DATABASE] VARCHAR(80),[ObjNm] VARCHAR(100), [ChildNm] varchar(100) NULL, [CodeType] varchar(20));
--Search DML
-- List of all "code" object types you want to look for within (P,FN,TF,IF)
-- Comment out any you do not want
DECLARE @SearchType TABLE([TYPE] VARCHAR(2));
INSERT @SearchType SELECT 'P'; --Proc
INSERT @SearchType SELECT 'V'; --Proc
INSERT @SearchType SELECT 'FN'; --Scalar function
INSERT @SearchType SELECT 'TF'; --Table function
INSERT @SearchType SELECT 'IF'; --Inline table function
INSERT @SearchType SELECT 'TR'; --Trigger
-- INSERT @SearchType SELECT 'PC'; --Proc Assembly (CLR)
-- INSERT @SearchType SELECT 'AF'; --Aggregate function (CLR)
-- INSERT @SearchType SELECT 'FS'; --Assembly (CLR) scalar function
-- INSERT @SearchType SELECT 'FT'; --Assembly (CLR) table-valued function
-- INSERT @SearchType SELECT 'TA'; --Assembly (CLR) DML trigger
-- Change to 1 if you want system table views.
SELECT @view_system_obj = 0;
-- Change to 1 if you want user objects.
SELECT @view_user_obj = 1;
DECLARE @MySearchTerms TABLE(SearchWrd VARCHAR(60));
-- Many examples below for specific situations.
-- Just uncomment any one you want or use the generic one below.
INSERT @MySearchTerms SELECT 'yourSearchTermHere'; -- could be anything. Keep it simple is best. Don't save your modified copy.
-- Look for any occurance of each SearchTerm
WHILE EXISTS (SELECT SearchWrd FROM @MySearchTerms a
WHERE SearchWrd > @SearchFor)
BEGIN
SELECT @SearchFor = MIN(SearchWrd)
FROM @MySearchTerms a
WHERE SearchWrd > @SearchFor;
-- Handle tabbed Search value (where tab is replaced by '~'
-- SQL Svr differentiates tabs from spaces in LIKE phrases.
SELECT @SearchReplace = '';
IF CharIndex('~', @SearchFor, 1) > 0
BEGIN
-- Replace squiggle 7E with tab 09
-- Done to make grouping of results more meaningful
DECLARE @MyCmd varchar(200); DECLARE @SR Table(TabbedValue varchar(40));
SELECT @SearchReplace = @SearchFor;
SELECT @myvar = CONVERT(varbinary(128),@SearchReplace);
SELECT @mycharvar = REPLACE(master.dbo.fn_sqlvarbasetostr(@myvar),'7E','09');
SELECT @MyCmd = 'SELECT CONVERT(varchar(40), ' + @mycharvar + ')';
--Put in table
INSERT @SR(TabbedValue)
EXEC(@MyCmd)
SELECT @SearchReplace = (SELECT Top 1 TabbedValue from @SR);
DELETE @SR;
END
ELSE
BEGIN
SELECT @SearchReplace = @SearchFor;
END
IF @view_user_obj = 1
BEGIN
-- Uncomment the JOIN on @MyProc if you only want to search names in the @MyProc table above.
-- Optional filter included below for excluding object names having
-- version-name-variations (e.g. Lookup1, Lookup2, or LookupA, LookupB).
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,o.name, 'See Code', o.type
FROM sys.objects o WITH(NOLOCK)
JOIN @SearchType st ON o.[TYPE] = st.[TYPE] COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.sql_modules c WITH(NOLOCK) ON c.[OBJECT_ID] = o.[object_id]
-- JOIN @MyProc mp on o.[name] = mp.[ProcNm] -- use when you have specific names to search
WHERE c.definition LIKE '%' + @SearchReplace + '%'
-- AND c.definition Not LIKE '%[^@]' + @SearchReplace + '[^a-z,^A-Z,^0-9]%'; -- useful for some table name searches
-- TABLES - If you don't want tables, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,t.[name], NULL, 'U'
FROM sys.tables t WITH(NOLOCK)
WHERE t.[name] LIKE '%' + @SearchReplace + '%';
-- VIEWs - If you don't want user views, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,v.[name], NULL, 'V'
FROM sys.views v WITH(NOLOCK)
WHERE v.[name] LIKE '%' + @SearchReplace + '%';
-- COLUMNs - If you don't want table or view columns, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,object_name(c.[OBJECT_ID]), c.[name], o.type + '_Col'
FROM sys.columns c WITH(NOLOCK)
JOIN sys.objects o WITH(NOLOCK) ON c.[OBJECT_ID] = o.[object_id]
WHERE o.type in ('U','V')
AND c.[name] LIKE '%' + @SearchReplace + '%';
-- FKs - If you don't want FKs, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,object_name(fkc.constraint_object_id),object_name(fkc.referenced_object_id), 'F'
FROM sys.foreign_key_columns fkc WITH(NOLOCK)
WHERE object_name(fkc.referenced_object_id) LIKE '%' + @SearchReplace + '%';
-- Extended Stored Procs - If you don't want Extended Stored Procs, comment this out.
-- Cannot search the code to find the search term since these are DLLs.
INSERT @OutputTable
SELECT @SearchFor ,'master' ,o.[name], NULL, 'X'
FROM master.sys.objects o WITH(NOLOCK)
WHERE o.[name] LIKE '%' + @SearchReplace + '%'
AND o.type = 'X';
END
IF @view_system_obj = 1
BEGIN
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,o.name, 'See Code', o.type
FROM sys.system_objects o WITH(NOLOCK)
JOIN @SearchType st ON o.[TYPE] = st.[TYPE] COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.system_sql_modules c WITH(NOLOCK) ON c.[OBJECT_ID] = o.[object_id]
WHERE c.definition LIKE '%' + @SearchReplace + '%'
-- VIEWs - If you don't want system views, comment this out
INSERT @OutputTable
SELECT @SearchFor ,DB_NAME() ,v.[name], NULL, 'SV'
FROM sys.system_views v WITH(NOLOCK)
WHERE v.[name] LIKE '%' + @SearchReplace + '%';
END
-- Identify replication-related procs that we care about
UpDATE @OutputTable
SET CodeType = 'Repl Proc'
WHERE [ObjNm] LIKE 'sp_MS%';
-- Remove irrelevant system items
DELETE @OutputTable
WHERE [ObjNm] in ('sp_alterdiagram'
,'sp_creatediagram'
,'sp_drop_constraints'
,'sp_dropdiagram'
,'sp_helpdiagrams'
,'sp_helpdiagramdefinition'
,'sp_renamediagram'
,'sp_upgraddiagrams'
,'sp_helpdiagramdefinition'
,'sp_certify_removable'
,'dt_generateansiname'
,'fn_diagramobjects'
,'sysdiagrams'
);
END
-- Get unique occurance of proc name grouped by format of search term
-- I made up SU and SV for my own convenience.
SELECT a.SearchFor
, a.[DATABASE]
, a.[ObjNm]
, a.[ChildNm]
, CASE
WHEN a.[CodeType] = 'P' AND a.[ObjNm] LIKE 'sp_MS%' THEN 'ReplProc'
WHEN a.[CodeType] = 'P' THEN 'Proc'
WHEN a.[CodeType] = 'IF' THEN 'Tbl Func'
WHEN a.[CodeType] = 'TF' THEN 'Tbl Func'
WHEN a.[CodeType] = 'FN' THEN 'Scalar Func'
WHEN a.[CodeType] = 'TR' THEN 'Trigger'
WHEN a.[CodeType] = 'PC' THEN 'Proc (CLR)'
WHEN a.[CodeType] = 'AF' THEN 'Aggregate Func (CLR)'
WHEN a.[CodeType] = 'FS' THEN 'Scalar Func (CLR)'
WHEN a.[CodeType] = 'FT' THEN 'Tbl Func (CLR)'
WHEN a.[CodeType] = 'TA' THEN 'DML Trigger (CLR)'
WHEN a.[CodeType] = 'F' THEN 'FK'
WHEN a.[CodeType] = 'U' THEN 'Table'
WHEN a.[CodeType] = 'X' THEN 'Ext Stored Proc'
WHEN a.[CodeType] = 'V' THEN 'View'
WHEN a.[CodeType] = 'SV' THEN 'System View'
WHEN a.[CodeType] = 'U _Col' THEN 'TableCol'
WHEN a.[CodeType] = 'V _Col' THEN 'ViewCol'
ELSE a.[CodeType]
END as 'Type'
FROM @OutputTable a
--JOIN @MyProc b on a.[ObjNm] = b.[ObjNm]
WHERE [SearchFor] IS NOT NULL
GROUP BY [SearchFor], [DATABASE], [ObjNm], [ChildNm],[CodeType]
ORDER BY 1,2,5,3;
No comments:
Post a Comment