在 Powershell 中将 SMO 集合复制到数组中
我编写了一个 Powershell 脚本,它将比较两个数据库并给出其中一个数据库中要删除的对象列表。我将这些项目(作为具有名称和架构的自定义对象)放入数组中。
在脚本的下一步中,我将迭代数据库中的对象,看看它们是否与数组中的对象匹配。如果找到匹配项,我就会继续从数据库中删除该对象。但我遇到的问题是,如果我尝试删除对象,那么我正在迭代的集合会发生更改,并且我会收到一条错误消息,表明集合已更改,并且 IEnumerable 在发生这种情况时将无法工作。
我尝试制作集合的副本,但似乎无法使用 CopyTo 方法将其填充到数组中。有什么建议吗?
我当前的代码如下。当我运行这个数组时 $sprocs 是空的。
function DropSQLObjects
{
param([object]$database, [object]$objectsToDrop)
$sprocs = @()
$database.StoredProcedures.CopyTo($sprocs, 0)
# If I do a $sprocs | out-host I see that the array is still empty
foreach ($objectToDrop in $objectsToDrop)
{
foreach ($sproc in $sprocs)
{
if ($sproc.Name -eq $objectToDrop.Name -and $sproc.Schema -eq $objectToDrop.Schema)
{
$sproc.Drop()
LogToSQL $database "Dropped Stored Procedure: $($objectToDrop.Schema).$($objectToDrop.Name)"
}
}
}
}
I've written a Powershell script which will compare two databases and come up with a list of objects in one of the databases to remove. I put those items (as a customized object with a name and schema) into an array.
In the next step of my script I iterate through the objects in the database and see if they match an object in my array. If I find a match then I go ahead and drop the object from my database. The problem that I ran into though, was that if I try to drop the object then the collection through which I'm iterating gets changed and I get an error message that the collection changed and IEnumerable won't work when that happens.
I tried to make a copy of collection, but I can't seem to stuff it into an array using the CopyTo method. Any suggestions?
My current code is below. When I run this the array $sprocs is empty.
function DropSQLObjects
{
param([object]$database, [object]$objectsToDrop)
$sprocs = @()
$database.StoredProcedures.CopyTo($sprocs, 0)
# If I do a $sprocs | out-host I see that the array is still empty
foreach ($objectToDrop in $objectsToDrop)
{
foreach ($sproc in $sprocs)
{
if ($sproc.Name -eq $objectToDrop.Name -and $sproc.Schema -eq $objectToDrop.Schema)
{
$sproc.Drop()
LogToSQL $database "Dropped Stored Procedure: $($objectToDrop.Schema).$($objectToDrop.Name)"
}
}
}
}
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我将其添加为答案,以防其他人将来需要此内容。事实证明,我确实让事情变得比他们需要的更困难。由于我使用的是 Powershell,因此“where”函数比迭代存储过程更好。
这是解决我的问题的代码:
实际上,我也有反对 UserDefinedFunctions 的代码,但代码主要是从 StoredProcedures 部分剪切和粘贴的。
I'm adding this as an answer in case anyone else has need of this in the future. It turns out that I was really making things harder than they needed to be. Since I was using Powershell, the "where" function was better than iterating through the stored procedures.
Here's the code which solved my issue:
In actuality, I also have code to go against UserDefinedFunctions, but the code is mostly a cut-and-paste from the StoredProcedures portion.