How to loop through all SQL tables?

Question

We have a piece of software that does not delete entries we no longer want. In order to get a feel for how much data is wasting away in our server and prepare for a big cleanup operation, I am trying to loop through all of the tables and pull the records that are marked for deletion. This is what I'm working with:

DECLARE @total INT
DECLARE @count INT
DECLARE @name NVARCHAR(25)
DECLARE @rn INT

SET @total = (SELECT COUNT(Name) FROM sys.tables)
SET @count = 1
SET @rn = (SELECT ROW_NUMBER() OVER(ORDER BY Name) FROM sys.tables)   

WHILE @count <= @total AND @count < 2
    BEGIN
        SET @name = (   SELECT Name, ROW_NUMBER() OVER(ORDER BY Name)
                        FROM sys.tables 
                        WHERE @rn = @count
                     )

        EXEC('SELECT * FROM WS_Live.dbo.' + @name + ' WHERE GCRecord IS NOT NULL')
        SET @count += 1         
    END

This is my error:

Msg 116, Level 16, State 1, Line 19 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I realize that my error probably has to do with selecting two columns in the line

        SET @name = (   SELECT Name, ROW_NUMBER() OVER(ORDER BY Name)
                        FROM sys.tables 
                        WHERE @rn = @count
                     )

but, I'm not sure how else to ensure that I am selecting the next row.

P.S. AND @count <2 is just for script testing.

How can I loop through all of the tables?

Answer 1

Use this system stored procedure

sp_MSforeachtable @command1="select count(*) from ?"

sample code

Note:

• This sp_MSforeachtable is an undocumented stored procedure.
• Does not support on Azure SQL (per comment below).

Answer 2

Maybe this is what you are looking for

DECLARE @NAME VARCHAR(100)
DECLARE @SQL NVARCHAR(300)

DECLARE CUR CURSOR FOR
  SELECT NAME
  FROM   SYS.TABLES
  WHERE  TYPE = 'U'
         AND SCHEMA_ID = 1

OPEN CUR

FETCH NEXT FROM CUR INTO @NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'SELECT * FROM WS_LIVE.DBO.'+@NAME+' WHERE GCRECORD IS NOT NULL'

      PRINT @SQL
      EXEC Sp_executesql
        @SQL

      FETCH NEXT FROM CUR INTO @NAME
  END

CLOSE CUR

DEALLOCATE CUR