Showing all Databases
-
Hello All
I hope that everyone is having a great day.
I would like to be able to see in a single list, a query resultset would be fine. How can I possible see all the SQL database servers and each database on each server on a single network?
Is this possible? I am assuming that it should be pretty easy to do. But I am just not certain about how to go about this.
Thanks
Andrew SQLDBA
-
Check if the given script below helps you.
osql -L >> c:\serverlist.txt
FOR /F %%I in (C:\ServerList.txt) do isql -S %%I -E -w500 -r1 -n -i database_SCRIPT.txt >> C:\database_RESULTS.log
Database_script should have the following script:
select @@servername as 'ServerName', name from master..sysdatabases
You need to have privilege in all the servers to execute the given script.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
This will also show you a list of databases for the curent server you are logged on to.
EXEC sp_databases
-
Chris,
the procedure will list only the databases in a single server. What he wants to for the entire domain that he woule like to have the dataabses that are running.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Sugesh,
Is there a way to do this in QA?
Cheers,
Jaybee.
-
You want to connect to all servers using query analyzer. It will be very hard i hope this should be an easy way as things are done through windows batch programs.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
If you establish linked servers you can loop through sysservers and then use a 4 part query to get all of the information in just one QA session.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
We have a series of DTS packages setup with a Source server and Destination server that go out and gather a lot of information from each of our servers. Database size information is one of the items we gather which also gives us a list of every database on that server.
The trick, as has been stated, is having access to every SQL instance. Because of a number of MSDE versions, etc., running on our network, we chose to maintain a table with those SQL instances we cared about. This table also holds information about each SQL instance (i.e. region, retired, business owner, etc.).
We then have nightly jobs setup that use a cursor (works for us) to loop through this table and pass in a source server and destination server to the DTS package (calling it for each server). This gathers all the information we want to know about our servers to a central repository that we can then easily report off of and keep historical information for each server.
If your intent is to do this for every server on the network, do a search on this site and you will find several examples to get a list of servers (even as noted in above post).
Hope this helps!
Chad
-
One issue with the -L switch is it is
a) dependendent on the SQL Server Listener Port not being blocked for a given SQL Server
b) dependent on the communications between the two to be able to respond in a timely manner
c) depend on SQL Server listening on the network (case in point, SQL Server Express and SQL Server 2005 Developer Edition by default only accept connections via Shared Memory).
It sounds like you need to do scanning to locate all the SQL Servers on a regular basis and export that to a text file. Then use the script Sugesh Kumar has given and as long as you can login to the SQL Server remotely, you should be all set. For those that only permit local connections, you'll have to use another way to get the information.
A good scanning tool is SQLRecon. It the successor to SQLPing and SQLPing2.
K. Brian Kelley
@kbriankelley -
Hi Sugesh
How can we foramt the output and put it in a table so that we can easily query it.
-
I wonder if you could use the ODBC function SQLBrowseConnect or the SQLDMO application method ListAvailableSQLServers() in some way?
David
If it ain't broke, don't fix it...
-
This is the same thing as using the -L command. It has the potential to miss SQL Servers, especially if any of them been set up for hiding or if the SQL Server Listener service is blocked for some reason.
K. Brian Kelley
@kbriankelley -
After slammer, a few of the larger companies I've worked with have blocked UDP1434's to prevent issues, so the -L doesn't work. I used to have the SMS guys query for an MSSQL* service on machines and get that list for me. Course it can include MSDE's so you need to dig into it.
For every server that I wanted this information for, I'd set up a small admin database on each server and have the server query itself for database size, configuration, etc. Then use a DTS/SSIS package to roll that information up every day to a central server. This allows you to gather the information even if there's a problem with the package (because the local server grabs it) and then still have central reporting.
-
Article?
K. Brian Kelley
@kbriankelley -
We already have a central repository where we store the information of each server. The set up goes like this-- We have a admin database on each server and every server replicates to a central database on one server. But once in a while we would like to manually match the count of the databases with that of the central reposoitry just to make sure that everything is fine. There are more than 200 servers so manually checking each server for the database count and then verifying with the inventory becomes a tedious task. So I was looking out for a way where by in one single shot I can query all the servers for the databases count.
Now the solution suggessted by Sugesh works for me and i get the count for more than 120 servers in one go. The only thing is that the output is in a text file and not properly formatted. so just wanted to know how can I format this and put in a table so that querying becomes a bit easy.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply