CODEWITHSUNDEEP
sqlsql-serverloopst-sql
Jonathon Anderson
Jonathon Anderson

Reputation: 1200

How to loop through all SQL tables?

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?

Upvotes: 23

Views: 89295

Answers (5)

Supplier PriceList2
Supplier PriceList2

Reputation: 1

To get all tables in the database:

CREATE TABLE #Results (TableName nvarchar(3630))

SET NOCOUNT ON

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
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )  

    insert  #Results values  (@TableName)
END

SELECT TableName
FROM #Results

drop table #Results;

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12959

You can generate the script as given below using Concatenation. Then execute the script.

DECLARE @sqlStmt NVARCHAR(MAX) = ''
SELECT @sqlStmt= CONCAT('SELECT COUNT(*) AS CountOfRows,''',name,''' AS TableName FROM ',name,';',CHAR(13), CHAR(10)) FROM sys.tables
--SELECT @sqlstmt 
EXEC(@sqlStmt)

Upvotes: 0

Hiten004
Hiten004

Reputation: 2481

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

Upvotes: 41

Rinoy Ashokan
Rinoy Ashokan

Reputation: 1619

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.

Upvotes: 6

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 25

Related Questions