I am using Powershell with the SQL Server module. I setup a PSDrive that points to the SQL Server instance I want to access, and I can browse the drive and get to the Tables collection. The problem is that there are 120,000+ tables, and it slows the system to a crawl when I use Get-ChildItem to retrieve them.
Is there an efficient way to retrieve a single table object without enumerating the entire collection?
So, I've left most of my original answer below, but TheMadTechnician brings up a much better point that get-item will get you the actual item while get-childitem will get you the items at the location, i.e.,
this is the actual table
get-item schema.tablename
and this is everything in the table - checks, columns, indexes, etc...
get-childitem schema.tablename
You can do a get-item or get-childitem on a single table by just explicitly calling it with the corresponding schema.tablename.
get-childitem schema.tablename
You can even do it without navigating the drive. It would be similar to this (adjusted for whatever level of the drive you're on)
get-childitem databases\databasename\tables\schema.tablename
Get-Item should work as well, since it is designed to get a specific item. I haven't tested that with a SQL table, but in theory it should work.
