August 4, 2016 at 9:14 am
I'm trying to see which stored procs (and functions) might be affected by my having added a column to a view definition, which might cause ambiguity errors.
Is there a way to do this without a manual inspection? sp_recompile / RecompileReferences doesn't quite do it for me, I want to update my code *before* 😉 I get an error at runtime...
Thanks!
August 4, 2016 at 9:20 am
schleep (8/4/2016)
I'm trying to see which stored procs (and functions) might be affected by my having added a column to a view definition, which might cause ambiguity errors.Is there a way to do this without a manual inspection? sp_recompile / RecompileReferences doesn't quite do it for me, I want to update my code *before* 😉 I get an error at runtime...
Thanks!
Using Powershell?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 4, 2016 at 9:25 am
You can also use Redgate SQL Search to search your entire database for references to the view.
And if you extract your database to a VS database project, you can look for consistency warnings there, before applying any changes anywhere.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 4, 2016 at 9:30 am
Yes, using Powershell. I don't have access to SQL Search or VS.
Getting the dependencies is easy. It's finding out which ones will throw an error the next time they're executed that I want listed.
I guess one way would be to script all the dependent objects out, and execute the script.
August 4, 2016 at 9:40 am
Got my list:
selectdistinct OBJECT_NAME(object_id)
fromsys.all_sql_modules
whereCHARINDEX('dbo.v_enqobj_descriptions', definition) > 0
andCHARINDEX('nNodeType_Id', definition) > 0
andCHARINDEX('.nNodeType_Id', definition) = 0
EDIT: Found sproc that does what I want:
sys.sp_refreshsqlmodule
August 4, 2016 at 9:44 am
The following will query the DDL text of all objects across all databases, returning details on any object that references a keyword you specify. This would include stored procedures, views, functions, triggers, and etc.
Just replace "InsertNameOfViewHere" with whatever keyword you're looking for.
exec sp_msforeachdb
'
use ?;
set lock_timeout 10000;
set deadlock_priority low;
set transaction isolation level read uncommitted;
select db_name() + ''.'' + schema_name(o.schema_id)
+ ''.'' + o.name as object_name, m.object_id, o.modify_date, m.definition
from sys.sql_modules m (nolock)
join sys.objects o (nolock) on o.object_id = m.object_id
where definition like ''%InsertNameOfViewHere%'';
';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 4, 2016 at 9:51 am
schleep (8/4/2016)
Yes, using Powershell. I don't have access to SQL Search or VS.Getting the dependencies is easy. It's finding out which ones will throw an error the next time they're executed that I want listed.
I guess one way would be to script all the dependent objects out, and execute the script.
I don't envy you having to do this work without having access to a bunch of tools which would make it easier. Good luck.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 4, 2016 at 9:57 am
As I mentioned, I was able to generate my list -- albeit with a few false positives.
Thanks all for taking the time to respond.
PS: Am I missing some obvious reason as to why this functionality would not exist in SQL or SMO?
August 4, 2016 at 10:44 am
If you're wanting to search for object references across multiple mssql instances, then PowerShell could help with that. However, if you're working with database objects within a single instance, then PowerShell and SMO add no value; they're essentially just an abstraction and integration layer. What you're wanting to do is a simple T-SQL statement.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 4, 2016 at 11:02 am
I agree the search is simpler in T-SQL; it's the compiling of the affected code before runtime that I want automated.
Maybe I'm using the wrong word here: I want the affected sprocs parsed.
That would be tedious at best in SQL, likely involving a cursor; in Powershell it would be a one-liner -- if it was possible at all.
August 4, 2016 at 12:11 pm
schleep (8/4/2016)
I agree the search is simpler in T-SQL; it's the compiling of the affected code before runtime that I want automated.Maybe I'm using the wrong word here: I want the affected sprocs parsed.
That would be tedious at best in SQL, likely involving a cursor; in Powershell it would be a one-liner -- if it was possible at all.
If you are talking about 'compiling' in terms of checking for consistency errors before making physical changes, that is what a 'build' of a Visual Studio database solution would give you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 4, 2016 at 12:29 pm
Here's the code for anyone interested:
clear
$strSrv = "MyServerName"
$objSrv = New-object Microsoft.SqlServer.Management.Smo.Server $strSrv
$objdb = $objSrv.Databases["MyDBName"]
$ObjectsToCheck = $objdb.StoredProcedures | ?{($_.textbody -like '*MyModifiedObject*')}
$ObjectsToCheck += $objdb.UserDefinedFunctions | ?{($_.textbody -like '*MyModifiedObject*')}
$ObjectsToCheck += $objdb.Views | ?{($_.textbody -like '*MyModifiedObject*')}
$ObjectsToCheck += $objdb.Triggers | ?{($_.textbody -like '*MyModifiedObject*')}
# Add other objects types that you may use.
$ObjectsToRecode = @()
$ObjectsToCheck | % {$objSp = $_; $spName = $_.Name;
try
{
$query = "EXEC sys.sp_refreshsqlmodule '$spName'"
$objdb.ExecuteNonQuery($Query)
}
catch
{
$ObjectsToRecode += $spName
}
}
$ObjectsToRecode
EDIT: Credit where it's due dept.:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply