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?
sp_MSforeachtable @command1="select count(*) from ?"
– Gaultiero