title: TSQL: Rowcounts of All Tables In A Server
body: <p>I am trying to obtain the row count of all tables in a server (NOT a particular database, but all the databases on a server, excluding the msdb, model, master, etc).  I don't need any other details to be returned other than the database name, the table name, and row count.</p>
  
  <p>My approach to this problem is to get all the databases in a server and place an id on them, which will be referred to in a while loop (beginning with id one until the maximum id).  Then, within the while loop, I obtain the tables and row counts in the matching database ID.  My problem is that the USE DatabaseName doesn't seem to allow me to make it dynamic, meaning that I can't store a database name in a variable and use it as the referred to database when performing the table with row count query.</p>
  
  <p>Is there another approach to this that I'm missing (I've looked at many other examples - often using cursors, which seem to be much longer in code and appear to use more resources - this is a relatively fast query even if I use the largest database by tables, except that it doesn't hit the next database and so on), or am I missing something obvious in the code to make this dynamic?</p>
  
  <pre><code>DECLARE @ServerTable TABLE(
      DatabaseID INT IDENTITY(1,1),
      DatabaseName VARCHAR(50)
  )
  
  DECLARE @count INT
  DECLARE @start INT = 1
  SELECT @count = COUNT(*) FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb')
  
  INSERT INTO @ServerTable (DatabaseName)
  SELECT name 
  FROM sys.databases
  WHERE name NOT IN ('master','tempdb','model','msdb')
  
  WHILE @start &lt; @count
  BEGIN
  
      DECLARE @db VARCHAR(50)
      SELECT @db = DatabaseName FROM @ServerTable WHERE DatabaseID = @start
  
      -- This is the problem, as the USE doesn't seem to allow it to be dynamic.
      USE @db
      GO
  
      SELECT @db
          ,o.name [Name]
          ,ddps.row_count [Row Count]
      FROM sys.indexes AS i
          INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
          INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id 
      WHERE i.index_id &lt; 2  AND o.is_ms_shipped = 0 
      ORDER BY o.NAME
  
      SET @start = @start + 1
  END
  </code></pre>
  
  <p>Note: I tried checking in the sys.objects and sys.indexes to see if I could filter with a database name, but I had no luck.</p>
  
  <p><strong>Update:</strong> I tried turning the <code>SELECT</code> into something dynamic with no success (note the below code only shows the change <code>SELECT</code>):</p>
  
  <pre><code>SET @sql = '
      SELECT ' + @db + ' [Database]
          ,o.name [Name]
          ,ddps.row_count [Row Count]
      FROM  ' + @db + '.sys.objects
          INNER JOIN ' + @db + ' sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
          INNER JOIN ' + @db + ' sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id 
      WHERE i.index_id &lt; 2  AND o.is_ms_shipped = 0 
      ORDER BY o.NAME'
  </code></pre>