In a previous blog post I talked about being puzzled as to why I wasn't able to get, in my mind, accurate table counts for a database in SQL Server 2005 that had over 50,000 tables. I kept getting only 1,000.
With the help of Chad Miller, I've figured it out.
To reiterate, when I had navigated to the PSDrive for the tables within one of my databases and had run the following command:
(get-childitem *).count
I got 1000, even though I clearly had more than 1000 tables in my database. But, a curious thing happens when I alter the command slightly:
(get-childitem).count
I get the full 50,000+!?!? So, what's going on?
Well, it looks like one of the other global variables for the PowerShell provider, SqlServerMaximumTabCompletion, is getting into the mix. In my particular environment I have this set to 1000. Coincidence? Apparently not. It seems that the use of the wildcard, *, is limited by this global variable, for when I change SqlServerMaximumTabCompletion to be equal to 0, which is unlimited, (dir *).count now returns the correct number of tables.
Likewise, when I use wildcards to return a list of tables that contain a certain string in the name:
get-childitem *ROLE*
I now get all of the tables I'm expecting.
It seemed counter-intuitive to me at first, but it seems to make sense because of the way the wildcard can be used in conjunction with tab completion.