Rowcount issue in sql server I'm trying to get the rowcount for each table by using the query below:

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
where   [Tables].name   like '%ixf_%' 
     or [Tables].name   like '%itx_%'
     or [Tables].name   like '%wk_%'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name
ORDER BY [TotalRowCount] desc ,[TableName];


The problem cames up when I run it in a particular enviroment, i'm getting no rows, but when I tried in another server, I'm able to retrieve rows.

I have this query also to match the collate in case of any collation issue:

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default = b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp


And no rows are returned either.
Some things to check (for future readers, since we now know what happened here), off the cuff:


Case sensitivity: on a case sensitive instance, the metadata needs to be an exact match; TableName &lt;> TABLENAME &lt;> tablename.
Collation: in some cases you may have names in your objects that were created with a different collation than you're using (and in general it is just a good practice to always explicitly define parameters involving metadata as nvarchar and prefix all string literals with N, since metadata is always stored as nvarchar).
Correct database/server: on the new server, when there are no results, it could mean that you've left the database context set to master, or tempdb, or your default database, or the database of the most recent query window before this one. Can't hurt to double-check all connection string settings... several times the solution ends up being the person is connected to the right database, but on the QA or staging server.
Correct schema: when you use a name like TableName with certain functions, procedures, or DMVs, it may look in the wrong schema or even return the wrong object from a different schema. You may also not have correct permissions on the right schema. Always explicitly specify the schema (even when it's dbo) and, if that doesn't help, get someone with sysadmin to try your queries.
Permissions: for many metadata queries, you need more than SELECT rights, you also need VIEW DEFINITION. Again, have someone with sysadmin try your queries and see if they get different results. If they do, you're on the right track!