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
Like this:
Like Loading...
Related