{"id":465,"date":"2013-03-01T09:00:40","date_gmt":"2013-03-01T16:00:40","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=465"},"modified":"2014-03-03T13:40:57","modified_gmt":"2014-03-03T20:40:57","slug":"sql-server-impact-analysis-query","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2013\/03\/sql-server-impact-analysis-query\/","title":{"rendered":"SQL Server Impact Analysis Query"},"content":{"rendered":"<p>Ugh. Impact analysis is one my least favorite tasks. And despite the fact it's gotten easier over the years with the addition of being able to check object dependencies natively within SQL Server Management Studio, I still find myself resorting to wildcard searches of object definitions for various reasons. (For example: dependencies on objects accessed via linked server, hardcoded values, etc.). Back in SQL Server 2000, these types of searches were performed against the syscomments table. In SQL Server 2005 and 2008 we have the new view sys.sql_modules to query.\u00a0 This eliminates the minor risk with syscomments that in a longer definition (over 4,000 characters) a particular\u00a0 search string could be split across two rows, either preventing it from showing up in a search or requiring some fancy coding (see this <a href=\"http:\/\/www.sqlservercentral.com\/articles\/Administration\/2792\/\" target=\"_blank\">forum post on SQL Server Central<\/a>).<\/p>\n<p>Here's the query I use to check sql_modules.<\/p>\n<pre>-- http:\/\/www.sqldbpros.com\r\nSELECT\u00a0 OBJECTPROPERTYEX(sm.object_id, 'basetype') AS ObjectType\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , OBJECT_SCHEMA_NAME(sm.object_id) AS schemaName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 , OBJECT_NAME(sm.object_id) AS ObjectName\r\nFROM\u00a0\u00a0\u00a0 sys.sql_modules AS sm\r\nWHERE\u00a0\u00a0 OBJECTPROPERTY(sm.object_id, 'IsMSShipped') = 0 --exclude system objects\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND sm.definition LIKE '%employeeID%'<\/pre>\n<p>This will return results similar to the following:<\/p>\n<p><a href=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/SQL-server-Impact-Analysis.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-466\" alt=\"SQL server Impact Analysis\" src=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/SQL-server-Impact-Analysis.png\" width=\"384\" height=\"338\" srcset=\"http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/SQL-server-Impact-Analysis.png 384w, http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2013\/02\/SQL-server-Impact-Analysis-300x264.png 300w\" sizes=\"auto, (max-width: 384px) 100vw, 384px\" \/><\/a><\/p>\n<p>Just a reminder: if the string you're looking for contains an underscore aka \"_\" don't forget that SQL Server considers an underscore a wildcard character! You can \"escape\" this functionality by following the directions in our post <a title=\"Escaping From An Underscore In A SQL Server Wildcard \/ LIKE Search\" href=\"http:\/\/www.sqldbpros.com\/2013\/01\/escaping-from-an-underscore-in-a-sql-server-wildcard-like-search\/\" target=\"_blank\">Escaping From An Underscore In A SQL Server Wildcard \/ LIKE Search<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ugh. Impact analysis is one my least favorite tasks. And despite the fact it's gotten easier over the years with the addition of being able to check object dependencies natively within SQL Server Management Studio, I still find myself resorting to wildcard searches of object definitions for various reasons. (For example: dependencies on objects accessed [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43,3,51,122,57],"tags":[105,10,6,9,104,12,11,173],"class_list":["post-465","post","type-post","status-publish","format-standard","hentry","category-microsoft-sql-server","category-scripts","category-sql-server-management-studio","category-ssdt-sql-server-data-tools","category-t-sql","tag-impact-analysis","tag-ms-sql","tag-script","tag-sql-server","tag-sql-server-2000","tag-sql-server-2005","tag-sql-server-2008","tag-t-sql"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/465","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=465"}],"version-history":[{"count":5,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/465\/revisions"}],"predecessor-version":[{"id":669,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/465\/revisions\/669"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=465"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}