MS SQL Server, how do remediation table.column changes across all application
Clash Royale CLAN TAG#URR8PPP
MS SQL Server, how do remediation table.column changes across all application
I work on script to address scenario when our db vendor deprecated TableA.ColB, so I need to estimate effect of this and get list of all sp, udf, etc where this column is used to go over and fix it.
My first though is recursive search for this column for all db object something like in sample below to search in sp, udf. But I just curious if I can make it more intelligent so I would be able to take care of comments, aliases and anything else what I don't know yet to lower false positive results.
* just recalled that SSIS also could be involved.
SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'MyString' + '%'
AND TYPE = 'P'
SELECT *
There are a number of tools that will try to tell you where various identifiers are used, but it's entirely possible that they won't find everything, especially if the column name isn't used consistently or something is dynamically generating SQL to be executed or there are external systems or reports that expect it to exist. Before deleting it, I'd recommend renaming it to something like "originalname_depricated" and waiting a year to see if anything breaks.
– Terry Carmen
Aug 7 at 19:44
1 Answer
1
You could reference sys.sql_modules for stored procedures and other objects, except tables.
Tx all !! Sys.Sql_Modules I think it's same like in my sample, and I'll stringfind SSIS, RDL as XML files
– Mike S
Aug 7 at 20:35
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Import the whole database into a SSDT database project. Right-click the column, choose "find all references", and be amazed. It even sees through
SELECT *. (It cannot find references in dynamic SQL, outside the database and inside SSIS packages, though. It's only good, not magical.)– Jeroen Mostert
Aug 7 at 19:23