Databases
StackOverflow2013
Postsdbo
POSQL generating report of multiple tables from multiple server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL generating report of multiple tables from multiple server
    primarykey
    data
    text
    <p>I am once again faced with an issue that my boss threw at me..</p> <p>I created a small windows based application that connects to multiple MSSQL servers consecutively runs this query:</p> <pre><code>DECLARE @Orderby AS VARCHAR(50) SET @Orderby = 'reserved_MB desc' SET nocount ON SET ansi_warnings OFF SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED CREATE TABLE #S ( [name] VARCHAR(50) NULL , [rows] VARCHAR(50) NULL , [reserved] VARCHAR(50) NULL , [data] VARCHAR(50) NULL , [index_size] VARCHAR(50) NULL , [unused] VARCHAR(50) NULL ) -- Create a cursor to loop through the user tables DECLARE @name VARCHAR(50) DECLARE c_tables CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'U' OPEN c_tables FETCH NEXT FROM c_tables INTO @name WHILE @@fetch_status = 0 BEGIN INSERT INTO #S EXEC sp_spaceUsed @name FETCH NEXT FROM c_tables INTO @name END CLOSE c_tables DEALLOCATE c_tables SELECT [dSampleDate] = GETDATE(), [name] , [rows] , reserved_MB , data_MB , [index_MB] , unused_MB INTO #T FROM ( SELECT [name] , [rows] = CAST([rows] AS INT) , reserved_MB = CAST(REPLACE(reserved, 'KB', '') AS INT) / 1000 , data_MB = CAST(REPLACE(data, 'KB', '') AS INT) / 1000 , [index_MB] = CAST(REPLACE(index_size, 'KB', '') AS INT) / 1000 , unused_MB = CAST(REPLACE(unused, 'KB', '') AS INT) / 1000 FROM #S ) AS XX ORDER BY reserved_MB DESC DROP TABLE #S DROP TABLE #T </code></pre> <p>Which runs fine, it inserts it into a local database inside the reader with this command:</p> <pre><code>INSERT INTO [DBGrowth] (sName, sTableName, iRows, iReservedMB, iDataMB, iIndexMB, iUnusedMB) VALUES('" &amp; sDbName &amp; "', '" &amp; RDR2.Item("name") &amp; "', '" &amp; RDR2.Item("rows") &amp; "', '" &amp; RDR2.Item("reserved_MB") &amp; "', '" &amp; RDR2.Item("data_MB") &amp; "', '" &amp; RDR2.Item("index_MB") &amp; "', '" &amp; RDR2.Item("unused_MB") &amp; "') </code></pre> <p>I got all that to work just fine, not the following has me puzzled.. He wants that I generate daily reports on Rows increase, Data file increase and Index file increases. And as of yet I am unable to find a solution, the language I created the application in is in Visual Basic .NET and I am using MSSQL Server 2008.</p> <p>Ideas and tips for either generating results in T-SQL or in Visual Studio (Application based) would be helpful.</p> <p>If anyone can point me in the right direction that would be greatly appreciated.</p> <p>Regards, Robert.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USRobert de Jonge
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload