Dave Mason has a few scripts he uses to pull database metrics from his SQL Server instances:
There is a total of “N” linked servers. The query selects from each one and combines the results via UNION ALL. Here’s a sample of the output on my CMS:
What we’ve seen so far could be used as a template for other queries, such as a backup report, a database files report, etc. The code could be put in a stored procedure and run as needed for automated tasks. I’ve used similar code to retrieve data for an SSRS report.Let’s examine a problem you’re likely to encounter by including @@SERVERNAME within the query
Give it a read.