for anyone interested this is a part of the solution that i can share
DECLARE @t TABLE (id INT IDENTITY PRIMARY KEY,tbl_name VARCHAR(100))
DECLARE @id INT,
@tbl_name VARCHAR(100),
@sql VARCHAR(4000)
INSERT @t
SELECT name From sys.objects Where name like 'dim_%'
SELECT @tbl_name = tbl_name,@id = id FROM @t WHERE id =1
WHILE @id IS NOT NULL
BEGIN
SELECT @sql = 'select * from ' + @tbl_name + ' where ' + @tbl_name + '_key<=0'
PRINT @sql
EXEC (@sql)
SELECT @id +=1
SELECT @tbl_name = tbl_name,@id = id FROM @t WHERE id =(SELECT MIN(id) FROM @t WHERE id > @id)
IF @@ROWCOUNT = 0 BREAK
END