SQL Server 2008 - How to find top 10 tables and order them

Question

Hi I have the following script:

DECLARE @sql VARCHAR(2000);
DECLARE @tableName SYSNAME;
DECLARE @columnName SYSNAME;
DECLARE @count INT;
DECLARE @NotCursor TABLE(ID INT IDENTITY(1, 1),
                    TableName SYSNAME,
                    ColumnName SYSNAME)
DECLARE @StartLoop INT
DECLARE @EndLoop INT
DECLARE @SQLFinalQuery VARCHAR(MAX)

INSERT INTO @NotCursor
SELECT TABLE_NAME,
      COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(DATA_TYPE = 'date'
OR DATA_TYPE = 'datetime')
AND table_name NOT LIKE '%[_]%'
ORDER BY TABLE_NAME

SELECT @StartLoop = MIN(ID),
      @EndLoop = MAX(ID)
FROM @NotCursor

SET @SQLFinalQuery = ';WITH cte_Resultset AS'+CHAR(13)+CHAR(10)
+'('

WHILE @StartLoop <= @EndLoop
    BEGIN
       SELECT @tableName = TableName,
            @columnName = ColumnName
       FROM @NotCursor
       WHERE  ID = @StartLoop

       SET @sql = 'SELECT ''' + @tableName + ''' as [TableName], ' + '''' + @columnName + ''' AS [ColumnName], ' + 'DATEPART(yy, ' + QUOTENAME(@columnName) + ') AS [Year], COUNT(1) AS [NumberofRows]'+CHAR(13)+CHAR(10)
       +'FROM ' + QUOTENAME(@tableName) +CHAR(13)+CHAR(10) 
       +'GROUP BY DATEPART(yy, ' + QUOTENAME(@columnName) + ')';
       SET @SQLFinalQuery = @SQLFinalQuery+CHAR(13)+CHAR(10)+@sql;

       SET @SQLFinalQuery =   CASE 
                             WHEN @StartLoop = @EndLoop THEN @SQLFinalQuery+CHAR(13)+CHAR(10)+')'
                             ELSE @SQLFinalQuery+CHAR(13)+CHAR(10)+'UNION ALL' 
                          END

       SET @StartLoop = @StartLoop + 1

    END
SET @SQLFinalQuery = @SQLFinalQuery +'SELECT TOP 10 SUM(NumberofRows) AS NumberOfRows,TableName,ColumnName,Year'+CHAR(13)+CHAR(10)
+'FROM cte_Resultset'+CHAR(13)+CHAR(10)
+'WHERE Year IS NOT NULL'+CHAR(13)+CHAR(10) 
+'GROUP BY TableName, ColumnName, Year'+CHAR(13)+CHAR(10)
+'ORDER BY SUM(NumberofRows) DESC'+CHAR(13)+CHAR(10)

EXEC (@SQLFinalQuery)

The output of this script provides me with the NumberofRows, TableName, ColumnName, and Year.

However, I also want to additionally filter the results. Currently, the script searches through every table without "_" in it. But, I also want it to also only look at the tables which are related to a calendar (the table must be joint to a calendar). The table where the table name and calendar name can be found is called TimeDependencies. Is there any way to join my current code and this table, so that the result of the script will filter out tables which dont have an associated calendar with them?

Thanks.

Sample Data:

T002    dtCodeObjective
T002    dtDCNandPersistency
T002    dtServiceFee
T004    dtMilitaryCommission

Answer 1

@user3712641,This answer is related to the question from SQL Server 2008 - Optimizing Select statement [duplicate]

By the time I could answer, the original thread was closed as a duplicate. However as I had already put some time into optimize this query, I am going ahead and adding to this post instead.

What I did is remove looped inserts and eliminated cursors. Tests on local machine showed some performance increment. Please try and let me know if it works for you.

DECLARE @sql VARCHAR(2000);
DECLARE @tableName SYSNAME;
DECLARE @columnName SYSNAME;
DECLARE @count INT;
DECLARE @NotCursor TABLE(ID INT IDENTITY(1, 1),
                    TableName SYSNAME,
                    ColumnName SYSNAME)
DECLARE @StartLoop INT
DECLARE @EndLoop INT
DECLARE @SQLFinalQuery VARCHAR(MAX)

INSERT INTO @NotCursor
SELECT TABLE_NAME,
      COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(DATA_TYPE = 'date'
OR DATA_TYPE = 'datetime')
AND table_name NOT LIKE '%[_]%'
ORDER BY TABLE_NAME

SELECT @StartLoop = MIN(ID),
      @EndLoop = MAX(ID)
FROM @NotCursor

SET @SQLFinalQuery = ';WITH cte_Resultset AS'+CHAR(13)+CHAR(10)
+'('

WHILE @StartLoop <= @EndLoop
    BEGIN
       SELECT @tableName = TableName,
            @columnName = ColumnName
       FROM @NotCursor
       WHERE  ID = @StartLoop

       SET @sql = 'SELECT ''' + @tableName + ''' as [TableName], ' + '''' + @columnName + ''' AS [ColumnName], ' + 'DATEPART(yy, ' + QUOTENAME(@columnName) + ') AS [Year], COUNT(1) AS [NumberofRows]'+CHAR(13)+CHAR(10)
       +'FROM ' + QUOTENAME(@tableName) +CHAR(13)+CHAR(10) 
       +'GROUP BY DATEPART(yy, ' + QUOTENAME(@columnName) + ')';
       SET @SQLFinalQuery = @SQLFinalQuery+CHAR(13)+CHAR(10)+@sql;

       SET @SQLFinalQuery =   CASE 
                             WHEN @StartLoop = @EndLoop THEN @SQLFinalQuery+CHAR(13)+CHAR(10)+')'
                             ELSE @SQLFinalQuery+CHAR(13)+CHAR(10)+'UNION ALL' 
                          END

       SET @StartLoop = @StartLoop + 1

    END
SET @SQLFinalQuery = @SQLFinalQuery +'SELECT TOP 10 SUM(NumberofRows) AS NumberOfRows,TableName,Year'+CHAR(13)+CHAR(10)
+'FROM cte_Resultset'+CHAR(13)+CHAR(10)
+'GROUP BY TableName, Year'+CHAR(13)+CHAR(10)
+'ORDER BY SUM(NumberofRows) DESC'+CHAR(13)+CHAR(10)

EXEC (@SQLFinalQuery)

IF you need to include Columns, Please replace the last 6 line with this

SET @SQLFinalQuery = @SQLFinalQuery +'SELECT TOP 10 SUM(NumberofRows) AS NumberOfRows,TableName,ColumnName,Year'+CHAR(13)+CHAR(10)
+'FROM cte_Resultset'+CHAR(13)+CHAR(10)
+'GROUP BY TableName, ColumnName, Year'+CHAR(13)+CHAR(10)
+'ORDER BY SUM(NumberofRows) DESC'+CHAR(13)+CHAR(10)

EXEC (@SQLFinalQuery)

Answer 2

Based on the updated comments that the OP wants to get the counts for all tables, not just the 10 largest counts this can be done with no loops.

declare @SQL nvarchar(max)

select @SQL = 
STUFF((
select top 20 'SELECT ''' + TABLE_NAME + ''' as [TableName], ' + 
             '''' + COLUMN_NAME + ''' AS [ColumnName], ' +
             'DATEPART(yy, ' + QUOTENAME(COLUMN_NAME) + 
             ') AS [Year], COUNT(1) AS [NumberofRows] FROM ' + QUOTENAME(TABLE_NAME) + 
             ' GROUP BY DATEPART(yy, ' + QUOTENAME(COLUMN_NAME) + ') union all '
from INFORMATION_SCHEMA.COLUMNS
WHERE
  (DATA_TYPE = 'date' or DATA_TYPE = 'datetime')
  and table_name not like '%[_]%'
ORDER BY TABLE_NAME 
for xml path('')),1 , 0 , '')

select @SQL = stuff(@SQL, len(@SQL) - 9, 10, '') + ' order by NumberOfRows desc'

exec sp_executesql @SQL

Answer 3

Just gather the results inside a table variable and select from that table:

DECLARE @sql varchar(2000);
DECLARE @tableName sysname; 
DECLARE @columnName sysname;
DECLARE @count int;
DECLARE theCursor CURSOR FOR

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
  (DATA_TYPE = 'date' or DATA_TYPE = 'datetime')
  and table_name not like '%[_]%'
ORDER BY TABLE_NAME 

OPEN theCursor;
FETCH NEXT FROM theCursor INTO @tableName, @columnName;

DECLARE @tables TABLE (TableName nvarchar(128), ColumnName nvarchar(128), 
                Year int, NumberofRows int)
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = 'SELECT ''' + @tableName + ''' as [TabelName], ' + 
             '''' + @columnName + ''' AS [ColumnName], ' +
             'DATEPART(yy, ' + QUOTENAME(@columnName) + 
             ') AS [Year], COUNT(1) AS [NumberofRows] FROM ' + QUOTENAME(@tableName) + 
             ' GROUP BY DATEPART(yy, ' + QUOTENAME(@columnName) + ')';
  PRINT @sql;
  INSERT INTO @tables
  EXEC (@sql);

  FETCH NEXT FROM theCursor INTO @tableName, @columnName;
END

SELECT TOP 10 SUM(NumberofRows) NumberOfRows, tableName, Year FROM @tables GROUP BY TableName, Year ORDER BY SUM(NumberofRows) DESC
CLOSE theCursor
DEALLOCATE theCursor;