Search code examples
sql-serverpowershellt-sqlssmssmo

SQL Server table with different case in PowerShell SMO vs SSMS

I'm quite confused and not sure where to start looking. I have a tablename that appears in different case sys.object and in the SSMS GUI, as opposed retrieving it via SMO

e.g. in SSMS I see "TABLENAME" but in Smo I see "TableName." This is causing issues with an IBM application and I'm struggling to figure out why this is.

$table = $SmoServer.Databases.Item("MyDb").Tables.Item("TABLENAME","dbo")
Write-Output $table

Output given is "TableName."

Running a sql query though:

select [name] from sys.objects where name = 'TABLENAME'

Output given is "TABLENAME."

Not that it should matter but collation is case insensitive.

Does anyone have any thoughts?

Thanks.

Solution

  • This seems to be a quirk of using the Item method to retrieve the table from the SMO Tables object. The first time you retrieve the table, it seems to return the table with the casing provided in the method argument. If I run the Item method again with different casing, it still outputs the casing the first call.

    e.g.:

    1st run:

    $SmoServer.Databases.Item("MyDb").Tables.Item("TableName","dbo")

    Will output "TableName" as was provided in the above parameter, even if the table is actually "TABLENAME."

    2nd Run:

    $SmoServer.Databases.Item("MyDb").Tables.Item("TableNAME","dbo")

    Will still output "TableName" even though I changed the casing. It will only output the new casing of "TableNAME" if I recreate the smoServer object.

    Interesting behaviour. I think I'll use Where-Object instead.