Does your server show up in the list when you
sqlcmd -L

And, Presto! Copying Poshtools_Profile.ps1 files to there did the trick.

Thanks Sue!

Edit: The $Home threw me off since it points to somewhere else entirely.

Doesn't appear to be working:

I've copied my profile.ps1 to

$Home\Documents\WindowsPowerShell\Poshtools_profile.ps1

and

$Home\Documents\WindowsPowerShell\profile.ps1

but in neither case is it being loaded when I start Visual Studio.

Also tried

$Home\My Documents\... to no avail.

Figured it out.

$DescCols = @()

$MultiCols = $objTable.Columns | %{$_.Name.Substring(3)} | group | ?{$_.Count -eq 2}

if ($MultiCols)

{

foreach ($Row in $MultiCols)

{

$DescCols += $objTable.Columns | ?{$_.Name -like "*$($Row.Name)"} | select name

}

}

...and note that $_.IsSystemObject -eq $False still returns the Northwind DB???

Bit of a kludge, but I've found a way. In short,

- create a trace to capture User Error Message events

- script out the trace definition

- start the trace

- run sp_refreshsqlmodule...

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...

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...

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...

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

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...

You're passing a DBname, but the class expects a database object:

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $TargetServer

$objDB = $Server.Databases[$dbname]

$NewRole = New-Object Microsoft.SqlServer.Management.Smo.DatabaseRole ($objDB, $RoleName)

$NewRole.Create()

Got it:

$object = $objSrcDB.Discover() | ?{$_.Name -eq $objName}

I was able to get it sorted using PsExec and the instructions here.

https://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

Phew.

Thanks gents.

P

No luck with the -E -A with my own local admin account.

We're using a domain\account to run the SQL Server service. That account has the same privs as my own,...