title: SQL Server dynamic SQL to query on sys tables
body: <p>I'm using SQL Server 2012 and have written a stored procedure that calculates table statistics using the <code>sys</code> tables. I realize there are reports in SSMS that do this for you, however, I'm wanting additional information. It seems like I'm forced to use dynamic SQL to make this happen, but it seems slow and clunky. My stored procedure works, but I was curious there a more efficient way to write the query, maybe even without dynamic SQL?</p>
  
  <pre><code>DECLARE @my_cursor CURSOR, 
          @sql nvarchar(250), @table_name nvarchar(100),
          @days_loaded int, @row_count int, @row_count_per_day int, 
          @used_mb numeric(36,2), @used_mb_per_day numeric(36,2),
          @first_date_loaded date, @last_date_loaded date, 
          @expected_first_date date, @gaps_exist nvarchar(1)
  
  -- Delete temp table #tables in case the stored procedure is ran twice in the same session
  IF OBJECT_ID('tempdb.dbo.#tables', 'U') IS NOT NULL 
      DROP TABLE #tables
  
  -- Select all the tables and stats from the dallas schema into a temp table
  SELECT 
      s.name + '.' + t.name AS table_name,
      p.rows AS row_count,
      CAST(ROUND(SUM(a.used_pages) / 128.00, 2) AS NUMERIC(36, 2)) AS used_mb
  INTO
      #tables
  FROM
      sys.tables AS t
  INNER JOIN 
      sys.indexes AS i ON t.object_id = i.object_id
  INNER JOIN 
      sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
  INNER JOIN 
      sys.allocation_units AS a ON p.partition_id = a.container_id
  INNER JOIN 
      sys.schemas AS s ON t.schema_id = s.schema_id
  WHERE
      s.name = 'dallas'
  GROUP BY 
      t.name, s.name, p.rows
  
  -- Add some additional fields in the temp table to be used for population in loop below
  ALTER TABLE #tables ADD days_loaded INT NULL
  ALTER TABLE #tables ADD used_mb_per_day numeric(36,2) NULL
  ALTER TABLE #tables ADD row_count_per_day INT NULL
  ALTER TABLE #tables ADD first_date_loaded DATE NULL
  ALTER TABLE #tables ADD expected_first_date DATE NULL
  ALTER TABLE #tables ADD gaps_exist nvarchar(1) NULL
  
  -- Loop through each table in dallas schema
  BEGIN
      SET @my_cursor = CURSOR FOR SELECT table_name, row_count, used_mb FROM #tables
  
      OPEN @my_cursor
  
      FETCH NEXT FROM @my_cursor INTO @table_name, @row_count, @used_mb
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
          -- Calculate first date that was loaded to see history of how far back table goes
          SET @sql = N'SELECT TOP 1 @first_date_loaded = CONVERT(date,Min(data_load_timestamp)) FROM ' + @table_name
          EXECUTE sp_executesql @sql, N'@first_date_loaded date OUTPUT', @first_date_loaded=@first_date_loaded OUTPUT
  
          -- Calculate last date that was loaded to offset expected first date if needed (data that is loaded day of instead of day before)
          SET @sql = N'SELECT TOP 1 @last_date_loaded = CONVERT(date,Max(data_load_timestamp)) FROM ' + @table_name
          EXECUTE sp_executesql @sql, N'@last_date_loaded date OUTPUT', @last_date_loaded=@last_date_loaded OUTPUT
  
          -- Calculate days loaded by retrieving count of distinct dates in table
          SET @sql = N'SELECT @days_loaded=COUNT(*) FROM (SELECT DISTINCT CONVERT(date, data_load_timestamp) AS data_load_date FROM ' + @table_name + ') tt'
          EXECUTE sp_executesql @sql, N'@days_loaded int OUTPUT', @days_loaded=@days_loaded OUTPUT
          IF @days_loaded &lt;&gt; 0
              BEGIN
                  SET @row_count_per_day = @row_count / @days_loaded
                  SET @used_mb_per_day = @used_mb / @days_loaded
                  SET @expected_first_date = DATEADD(d,@days_loaded * -1,GETDATE())
                  IF @last_date_loaded = CONVERT(date,GETDATE()) SET @expected_first_date = DATEADD(d,1,@expected_first_date)
                  IF @first_date_loaded = @expected_first_date
                      SET @gaps_exist = 'N'
                  ELSE
                      SET @gaps_exist = 'Y'
              END
  
          -- Update temp table with additional fields that were calculated
          SET @sql = N'UPDATE #tables SET days_loaded=' + CONVERT(nvarchar,@days_loaded) + 
                     ', row_count_per_day=' + CONVERT(nvarchar, @row_count_per_day) +
                     ', used_mb_per_day=' + CONVERT(nvarchar,@used_mb_per_day) + ', first_date_loaded=''' +
                     CONVERT(nvarchar,@first_date_loaded) + ''', expected_first_date=''' +
                     CONVERT(nvarchar,@expected_first_date) + ''', gaps_exist=''' + @gaps_exist + ''' WHERE table_name=''' + @table_name + ''''
          EXECUTE sp_executesql @sql
          FETCH NEXT FROM @my_cursor INTO @table_name, @row_count, @used_mb
      END
      CLOSE @my_cursor
      DEALLOCATE @my_cursor
  END
  -- Final return showing results from temp table sorted by largest utilization first
  SELECT * FROM #tables ORDER BY used_mb DESC
  </code></pre>