Perguntas e respostas para programadores profissionais e entusiastas
CREATE TABLE #Sites ( DbId INT NOT NULL, DbName VARCHAR(MAX) NOT NULL, TotalPosts INT NOT NULL, SoftDeleted INT NOT NULL, HardDeleted INT NOT NULL ); DECLARE @query VARCHAR(MAX) = ''; DECLARE dbcursor CURSOR FOR SELECT dbid, name FROM sysdatabases WHERE name NOT IN ( 'master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange' ); DECLARE @DbName VARCHAR(MAX); DECLARE @DbId INT; OPEN dbcursor; FETCH NEXT FROM dbcursor INTO @DbId, @DbName; WHILE @@FETCH_STATUS = 0 BEGIN IF @query <> '' SET @query = @query + ' UNION ALL '; SELECT @query = @query + ' SELECT ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId, ''' + @DbName + ''' AS DbName, ( SELECT MAX(Id) FROM [' + @DbName + '].dbo.PostsWithDeleted ) AS TotalPosts, ( SELECT COUNT(*) FROM [' + @DbName + '].dbo.PostsWithDeleted WHERE DeletionDate IS NOT NULL ) AS SoftDeleted, ( SELECT MAX(Id) - COUNT(*) FROM [' + @DbName + '].dbo.PostsWithDeleted ) AS HardDeleted '; FETCH NEXT FROM dbcursor INTO @DbId, @DbName; END INSERT INTO #Sites EXEC(@query); SELECT * FROM #Sites;