Blog ala Vidar

Brrrrrrhhhh, that sounds nasty. It is, but sometimes there are no other ways of solving a problem. Here’s a couple of tricks that might help you on your way, and some tricks on how to use static SQL where you might have thought you needed dynamic SQL.

SET NOCOUNT ON

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @TableName AS NVARCHAR(128)

SELECT name AS TableName, CAST(NULL AS INT) AS RowsCount
	INTO ##tables
	FROM sys.sysobjects
	WHERE xtype = 'U'

WHILE EXISTS (SELECT * FROM ##tables WHERE RowsCount IS NULL)
BEGIN
	SELECT TOP 1
	  @TableName = TableName
		FROM ##tables
		WHERE RowsCount IS NULL

	BEGIN TRY
		SET @SQL = 'UPDATE ##tables '
		SET @SQL = @SQL + '	SET RowsCount = (SELECT COUNT(*) FROM  [' + @TableName + '])'
		SET @SQL = @SQL + ' WHERE TableName = ''' + @TableName + ''''
		EXEC (@SQL)
	END TRY
	BEGIN CATCH
		UPDATE ##tables
			SET RowsCount = 0
			WHERE TableName = @TableName
	END CATCH
END

SELECT *
	FROM ##tables
	ORDER BY RowsCount DESC

DROP TABLE ##tables

This is one way of looping through tables, doing something with each. In this case doing a COUNT(*), but you can also use it to update statistics or similar.

There’s another way of doing it. NB! This is using sp_MSForEachTable which is an UNDOCUMENTED stored procedure, so use it on your own risk (it might get depricated with no notice!).

DECLARE @SQL AS NVARCHAR(MAX)

CREATE TABLE ##tables2 (
	TableName NVARCHAR(128),
	RowsCount INT
)

SET @SQL = 'INSERT INTO ##tables2 (TableName, RowsCount) '
SET @SQL = @SQL + 'SELECT ''?'', COUNT(*) FROM ?'
EXEC sp_MSForEachTable @SQL

SELECT *
	FROM ##tables2
	ORDER BY RowsCount DESC

DROP TABLE ##tables2

As you see, this is much less code. You can also do the same for databases with sp_MSForEachDb.

Another cool thing you can do with dynamic SQL is to get values back from it. In other words, we can rewrite our little script which counts records in each table to execute the COUNT in dynamic, but get the results from this and updating our table in static SQL. This way, we don’t have to use global (##tables) tables.

SET NOCOUNT ON

DECLARE @RowsCount AS INT
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @TableName AS NVARCHAR(128)

SELECT name AS TableName, CAST(NULL AS INT) AS RowsCount
	INTO #tables
	FROM sys.sysobjects
	WHERE xtype = 'U'

WHILE EXISTS (SELECT * FROM #tables WHERE RowsCount IS NULL)
BEGIN
	SELECT TOP 1
	  @TableName = TableName
		FROM #tables
		WHERE RowsCount IS NULL

	BEGIN TRY
		SET @SQL = 'SET @RowsCount = (SELECT COUNT(*) FROM [' + @TableName + '])'
		EXEC sp_executesql 
			@SQL, 
			N'@RowsCount INT OUTPUT', 
			@RowsCount OUTPUT

		UPDATE #tables
			SET RowsCount = @RowsCount
			WHERE TableName = @TableName
	END TRY
	BEGIN CATCH
		UPDATE #tables
			SET RowsCount = 0
			WHERE TableName = @TableName
	END CATCH
END

SELECT *
	FROM #tables
	ORDER BY RowsCount DESC

DROP TABLE #tables