How to loop through all SQL tables?

Some times using Cursor in the SQL is Risk. Below SQL query will traverse through all the tables in a selected data base without using CURSOR.

USE TEST
Declare @TableName nvarchar(256) 
SET @TableName = ''

WHILE @TableName IS NOT NULL
BEGIN
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    )
print @TableName -- Your logic will come here
END
GO

Above sql statements will print all the tables in side the TEST database. So instead of print table statement you can give your own sql logic like what you want to do with looping each table and @TableName will contain the table name in the present loop.

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).

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