One of the applications we have from a vendor has a SQL Server 2005 database with over 50,000 tables. I was using the PowerShell provider for SQL Server to search through the tables to find any that had the word 'ROLE' in it using the following command:
get-childitem *ROLE*
But, in the results, only two tables where returned, and I was expecting plenty more. In T-SQL I ran the following query:
SELECT count(*) FROM sys.tables
WHERE [name] LIKE '%ROLE%';
This time I see that there are 133 tables in the database that have the string 'ROLE' in the name.
I recalled reading a blog somewhere about folks hitting an object limit when returning objects from Active Directory in PowerShell, so I begin to wonder if there isn't some sort of configuration inside the PowerShell provider for SQL Server that lets you set a limit for get-childitem. And, sure enough, there is a global variable called $SqlServerMaximumChildItems. However, mine is set to 0, which means I should be returning an unlimited number of objects.
I head back into PowerShell in the SQLServer:\Sql\<database>\<instance>\databases\<dbname>\tables\, and run the following statement:
(get-childitem *).count
1000? What if I change the SqlServerMaximumChildItems global variable to 20 and try the count again?
set-variable -name SqlServerMaximumChildItems -scope global -value 20
Nope, the directory count still shows 1000. What if I bump up the variable to 2000? Directory count still shows 1000.
I try writing out the list of table names to a text file, and only 1000 table names are written. Does PowerShell have a 1000 object limit? That doesn't seem right, and my internet searches can't find anything to even suggest that. In fact, I use invoke-SQLCMD to select the table names from sys.tables again and I get all 50,000 objects. What about SMO 10.0? That doesn't seem right either. Something about the way I setup PowerShell to work with the SQL Server Provider? Is there a bug in the SqlServerMaximumChildItems global variable?
Anyway, I tested it on another machine, and was able to reproduce the issue.
I'm stumped.