SQL-dependency analysis-Identify impacted modules

I am sharing this script which will help during dependency analysis!
One of the table needs to be updated in my database, I had to identity all the Views/ Functions/ Procedures along with the usage stats which will be impacted with the table update…

Here you go 🙂

-- This script is to find all the Procedures/ Functions/ Views with specific Text, their creation time, modified time and last executed time

declare @mysearchstring nvarchar(40)
set @mysearchstring='%p%'

SELECT @@servername as ServerName, db_name() as Databasename, so.name, so.type_desc, create_date,	modify_date, last_execution_time, execution_count
--,OBJECT_DEFINITION(sm.OBJECT_ID), *
FROM sys.sql_modules sm 
join sys.objects so on so.Object_id = sm.Object_id 
left JOIN sys.dm_exec_procedure_stats AS ps ON sm.[object_id] = ps.[object_id] and ps.database_id = DB_ID()
WHERE definition LIKE @mysearchstring
-- NOTE: Stats will be from Cache, this is only for estimate 

[2014 05 02]
Note: the DMV dm_exec_procedure_stats is not available in SQL Server 2005, you need to remove few fields if you are using it in SQL Server 2005

use DBName

declare @mysearchstring nvarchar(40)
set @mysearchstring='%searchstring%'
 
SELECT @@servername as ServerName, db_name() as Databasename, so.name, so.type_desc, create_date,   modify_date 
FROM sys.sql_modules sm 
join sys.objects so on so.Object_id = sm.Object_id 
WHERE definition LIKE @mysearchstring

-Vijred

Similar Blogs: How to Search a Text/String in SQL Stored Procedure

Similar Posts:
https://vijredblog.wordpress.com/2014/01/31/how-to-search-a-textstring-in-sql-stored-procedure/
https://vijredblog.wordpress.com/2014/04/01/sql-query-execution-history-from-cache-plans/

Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , , , , , . Bookmark the permalink.

3 Responses to SQL-dependency analysis-Identify impacted modules

  1. Pingback: How to Search a Text/String in SQL Stored Procedure | Blog for reference – Vijred

  2. Pingback: SQL query execution history from cache plans | Blog for reference – Vijred

  3. guest says:

    Sp_help [TableName]
    Sp_depends [TableName]

    select top 10 * from msdb.dbo.sysjobsteps where command like ‘%%’

    SELECT top 10 Name, OBJECT_DEFINITION(OBJECT_ID)
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%%’

    SELECT top 10 OBJECT_NAME(object_id), *
    FROM sys.sql_modules
    WHERE definition LIKE ‘%%’

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