How to loop through all SQL tables?
We have a piece of software that does not delete entries that we no longer want. To understand how much data is wasting time on our server and to prepare for a big cleanup operation, I am trying to loop through all the tables and pull out the records marked for deletion. This is what I am 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
That's my fault:
Msg 116, Level 16, State 1, Line 19 Only one expression can be specified in the select list if the subquery is not entered with EXISTS.
I understand that my error is probably related to selecting two columns in a row
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 select the next line.
PS AND @count <2
is for testing script only.
How can I view all tables?
source to share
Perhaps 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
source to share