November 17, 2009 at 12:58 pm
hi everyone,
how can i know count or names of all the databases in multiple servers
thanks
praveen
November 17, 2009 at 1:07 pm
SELECT [Name] FROM MASTER..SYSDATABASES
Should give the list of all Databases in that Server (Instance)
To get it in all Servers, I am not aware of this, in SQL 2005. But in SQL 2008, you can run asingle query againt all registered servers.
Following link give more details how to do.
http://msdn.microsoft.com/en-us/library/bb964743.aspx
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 17, 2009 at 1:18 pm
Bru Medishetty (11/17/2009)
SELECT [Name] FROM MASTER..SYSDATABASESShould give the list of all Databases in that Server (Instance)
To get it in all Servers, I am not aware of this, in SQL 2005. But in SQL 2008, you can run asingle query againt all registered servers.
Following link give more details how to do.
Same process - just iterate it. You can create a batch using osql or SSIS to run the same command against each of the DB Instances.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 17, 2009 at 1:35 pm
with in one server its ok, but i need it on multiple servers all at a time
thanks
praveen
November 17, 2009 at 11:44 pm
To fetch database list from multiple servers see the below code;
Create linked server connection between servers from server on which script has to be excuted ;
Create PROC dbinfo
@server_nm VARCHAR(15)
AS
BEGIN
DECLARE @return_status int
DECLARE @sql1 VARCHAR(100)
DECLARE @S_NM VARCHAR(15)
SET @s_nm =@server_nm
SET @sql1 ='select name from '+ @S_nm+ '.master.dbo.sysdatabases'
EXEC (@sql1)
END
Rd,
Deepali
November 19, 2009 at 8:51 am
Praveen,
An approach I have used:
- Create a text file (called DBInstances.txt in this example) containing the names of each DB instance you want to include (one DB instance name per line). This list may include default (unnamed) DB instances or named DB instances – for example:
DBServer1
DBServer2\DBInstance1
DBServer2\DBInstance2
- Create a SQL text file “DB Summary.sql” with the following content (if you want a list of DB names):
Select
Cast(ServerProperty('ServerName') As varchar(15)) As ServerName,
Cast(sd.name As varchar(30)) As DBName
From master.dbo.sysdatabases sd
Where (sd.name Not In ('master', 'model', 'msdb', 'tempdb'))
And (sd.name Not Like 'ReportServer%')
And (sd.name Not In ('pubs', 'Northwind'))
And (sd.name Not Like 'AdventureWorks%')
or the following content if you want DB counts – without DB names:
Select
Cast(ServerProperty('ServerName') As varchar(15)) As ServerName,
Count(sd.name) As DBCount
From master.dbo.sysdatabases sd
Where (sd.name Not In ('master', 'model', 'msdb', 'tempdb'))
And (sd.name Not Like 'ReportServer%')
And (sd.name Not In ('pubs', 'Northwind'))
And (sd.name Not Like 'AdventureWorks%')
Both of these queries will list or count only application DBs, by excluding system and sample DBs from the list or count. If you want the list or count to include system or sample DBs, modify or remove the Where clause.
- Create the following 1-line BAT script file called DisplayDBSummary.bat, to run from either a command line window or from a SQL Agent job. Note that the following text may be shown on multiple lines for readability, but should be typed as a single line in the BAT script file:
For /F 'usebackq' %%G In ("DBInstances.txt") Do
SQLCmd –S %%G –E –I "DB summary.sql" >> DBSummary.txt
(Note: If the BAT script will be run from a system with installed SQL client tools earlier than SQL 2005 (such as SQL 2000), change SQLCmd to OSQL)
- All three of the above files should be placed in the same folder to simplify running the solution.
- Run the BAT script from either a command line window or from a SQL Agent job, as follows:
DisplayDBSummary.bat
The BAT script file run will create a text file in the same folder called DBSummary.txt that contains the results of all query runs across all requested DB instances.
I have used this approach for the type of query you requested (list of DB names or DB counts across a number of DB instances) and for many other queries that need to be run across multiple DB instances. This approach is very adaptable to any number of different needs.
This solution does not require the creation or use of linked servers, but it does require logon access and adequate privileges for the user running the BAT script to each requested DB instance. Use of integrated Windows DB security is assumed in this solution. The use of SQL Server DB security instead of integrated Windows DB security is left as an exercise to any interested reader.
Scott R.
November 19, 2009 at 1:50 pm
Using an ssis package with a forloop container and a lookup table of the instances you are interested in is how we get info from all the instances we look after. Setting it up is detailed in this article series, its pretty straight forward and once set up you can just copy the solutions and cheange whatever code\processes you may need:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61621/
Andrew
November 19, 2009 at 4:44 pm
The SSIS package is the method I currently use. I use it for more than just getting DB listings. I have expanded it to be able to inventory my SQL Instances. It is very useful, but you need to know all of the servers beforehand (much the same as the other methods provided in this thread).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 22, 2009 at 12:33 pm
You can get all the servers list from "sqlcmd -L" or "isql -L"
you can execute these commands using xp_cmdshell..Take all the servers list into a table and then iterate one by one and get the list of databases from these servers..
--Divya
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply