January 28, 2011 at 12:50 pm
Hi All,
With the following code $dts = $db.Tables returns the array of table names, but $dtn = $db.Tables[$TableName] returns $NULL
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ComputerName
$db = new-object Microsoft.SqlServer.Management.Smo.Database
$db = $s.Databases[$DatabaseName]
$dts = $db.Tables
# but this is null....why?
$dtn = $db.Tables[$TableName]
foreach ($dt in $dts){
if ($dtn.Name -eq $dt.Name){ ...etc
However, this code, $fgs = $db.FileGroups returns the array of File Group names and $fgn = $db.FileGroups[$FileGroupName] just returns the File Group Name as I want it to. (It also works if using data files, log files, databases)
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ComputerName
$db = new-object Microsoft.SqlServer.Management.Smo.Database
$db = $s.Databases[$DatabaseName]
$fgs = $db.FileGroups
$fgn = $db.FileGroups[$FileGroupName]
foreach ($fg in $fgs){
if ($fgn.FileGroupName -eq $fg.FileGroupName){ ...etc
I need the top code to perform like the bottom code; i.e. return the table name, not null.
What am I missing please?
Thank you.
gary
January 29, 2011 at 10:39 am
I came up with the answer.
Turns out the original code in my first post, does work, but only for the dbo schema. It does not "automatically" see any properties of tables belonging to another schema. Powershell has to be "forced" into using the schema in order to see the properties, and hence table name, of the table object. That can be done by pipelining the where-object to the table object. Now, $dtn does not return NULL, but does return the table name I want it to. The code below has the change as well as how I am using it.
etc...
# Fill the dataset
$da.Fill($ds) | out-null
# Are there any records?
if ($ds.Tables[0].Rows.Count -ne 0){
foreach ($Row in $ds.Tables[0].Rows){
[string]$ComputerName = $Row.FullComputerName
,[string]$DatabaseName = $Row.DBName
,[string]$TableName = $Row.TableName
,[string]$SchemaName = $Row.TableSchema
,[int32]$DatabaseTableID = $Row.DatabaseTableID
,[bool]$DataSpaceUsedWatch = $Row.DataSpaceUsedWatch
,[bool]$IndexSpaceUsedWatch = $Row.IndexSpaceUsedWatch
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ComputerName
$db = new-object Microsoft.SqlServer.Management.Smo.Database
$db = $s.Databases[$DatabaseName]
$dts = $db.Tables
$dtn = $db.Tables | Where-object {$_.schema -eq $SchemaName -and $_.name -eq $TableName}
foreach ($dt in $dts){
if ($dtn.Name -eq $dt.Name){
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText = "[SQLServerInventory].[SQLInv].[usp_DatabaseTableSpace_INS]"
# add parameters to pass values to the Stored Procedure and set the connection
$cmd.Parameters.AddWithValue("@NewDatabaseTableID", $DatabaseTableID) | out-null
if ($DataSpaceUsedWatch -ne $True)
{$cmd.Parameters.AddWithValue("@DataSpaceUsed", $NULL) | out-null}
else
{$cmd.Parameters.AddWithValue("@DataSpaceUsed", [Int64]$dt.Size) | out-null}
if ($IndexSpaceUsedWatch -ne $True)
{$cmd.Parameters.AddWithValue("@IndexSpaceUsed", $NULL) | out-null}
else
{$cmd.Parameters.AddWithValue("@IndexSpaceUsed", [Int64]$dt.UsedSpace) | out-null}
$cmd.Connection = $conn
# Execute Stored Procedure
$cmd.ExecuteNonQuery() | out-null
}
}
}
}
-----
Thanks.
gdr
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply