I'm doing a TSQL that brings me the
rowcount of certain tables for several databases. And it actually makes me the
rowcount , but I can not get the unified result. Now bring this:
When you should bring me everything unified.
I thought it would be appropriate to accumulate the results and then the variable throw me the total after going through all the databases, but I can not find a way to apply this logic to the code.
This is the code, I hope you have explained me well and that you can help me.
USE master; GO DECLARE @HFM_DB varchar(100) DECLARE @ROWCOUNT varchar (800) DECLARE @BASE varchar (800) DECLARE Cursor_DB CURSOR FOR SELECT name FROM sys.databases WHERE name like '%HFM%' OPEN Cursor_DB FETCH NEXT FROM Cursor_DB INTO @HFM_DB WHILE (@@FETCH_STATUS= 0) BEGIN SET @BASE=N'USE' --SELECT @BASE SELECT @ROWCOUNT= @BASE + ' ' [email protected]_DB + N' SELECT ''' [email protected]_DB + N''' as DB_name, t.NAME as [table_name], i.rows FROM ' + @HFM_DB+ '.sys.tables t INNER JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2) WHERE t.name LIKE ''%_DATA_AUDIT'' OR t.name LIKE ''%_TASK_AUDIT'' OR t.name=''HFM_ERRORLOG''' FETCH NEXT FROM Cursor_DB INTO @HFM_DB; -- SELECT @ROWCOUNT EXEC sp_sqlexec @ROWCOUNT END; CLOSE Cursor_DB; DEALLOCATE Cursor_DB;