December 8, 2005 at 3:06 pm
I am currently trying to pull info from one of our servers using the information schema views and sys tables but am unable to get my required results. What I need is:
Database Name
Data and Log file locations
Database Size(total)
# of tables in each DB
# of procs in each DB and
# of views in each DB
I have individual queries that give me pieces of this at a time but I need it in one query. Does anyone have anything like this that can help? Thanks.
December 8, 2005 at 3:15 pm
December 10, 2005 at 2:21 pm
Dave, is this for SQL Server 2005 or SQL SErver 2000?
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
December 12, 2005 at 2:49 pm
I want to generate this in one query to keep a matrix of all our databases and having it in one query would make it easier to put into a job or using smtp, send the results out becuase I am not able to have sql mail on our production servers. I was initally thinking of taking the multiple queries and joing on dbid and putting it all together in a dts package but I am being requested to do it outside of DTS.
This is in SQL 2000. Thanks.
Dave
December 12, 2005 at 3:31 pm
Check out the most recent posting on my blog. I was just pulling drive letters in my code for each database, but it's similar to what you're trying to do and you may be able to use the code to get yourself started. Basically dump everything into a temporary table and return that after you've cursored through all the databases. I can't claim credit for the approach, though; it's a technique you'll see in some of MS' system stored procedures.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply