How to loop through all SQL tables?
Asked Answered
C

5

22

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?

Cathodoluminescence answered 21/10, 2014 at 21:41 Comment(5)
I think I found the answer here: #17068Cathodoluminescence
use this system store proc sp_MSforeachtable @command1="select count(*) from ?"Gaultiero
Oh. Well... I suppose that's probably a better option :)Cathodoluminescence
Shoot... I wish I had known about that before I spent an hour debugging my script :(Cathodoluminescence
Thanks. If you want to post as an answer I'll mark it.Cathodoluminescence
G
39

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).
Gaultiero answered 21/10, 2014 at 22:1 Comment(5)
For straight up use, leave out the "@command" and use single quotes instead of double. Some might suggest adding []'s around the ?, but I found this caused issues in some cases.Tetracaine
As a note for anyone else looking at this answer, sp_MSforeachtable is not supported by AzureSQL at the time of this writing.Maxima
So what does support AzureSQL?Schaal
Better use this command: sp_MSforeachtable @command1="select '?' as name, count(*) from ?"Actress
sp_MSforeachtable @command1="select count(*), '?' as [Table] from ?" to bring back the table name.Darees
E
25

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 
Effects answered 21/10, 2014 at 22:0 Comment(0)
P
6

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.

Poleax answered 28/10, 2019 at 10:33 Comment(0)
A
0

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)
Amphibiotic answered 11/6, 2020 at 4:56 Comment(0)
M
0

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;
Macintosh answered 2/2 at 11:16 Comment(1)
Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?Battology

© 2022 - 2024 — McMap. All rights reserved.