How to search across all SQL Instances

Question

How to search across all SQL Instances

YSLGuru

SSC-Insane

Points: 21661
More actions
August 24, 2010 at 10:59 am

#226119

We have a mix of SQL PLatforms from a few 2000 instances (required by the sofwtare that uses these older DB's) to a few SQL 2008 instances with SQL 2005 being the most prominent. I imagine this kind of mixed SQL environemnt isn't that unusual.
Knowing that I have available any and all parts of SQL Server 2000 thru 2008 R2 that are available, but that I am restricted to a solutuion that can work with/across all versions (as oppossed to something that for example only works on 2008 R2 instances) does anyone know if there is a way using the SQL Server provided tools to search for a DB (preferably via pattern match in case you aren't definate about the name) across all SQL Server instances on the domain/network?
Thanks
Kindest Regards,
Just say No to Facebook!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You could look into Central Management Server. That is an option that is available with SQL 2008. You can execute a query against multiple servers at the same time.

http://msdn.microsoft.com/en-us/library/bb895144.aspx

From within that query you could do a search based on your requirements to search for a certain database name pattern.

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

i've done something similar two differnet ways; i ended up making an application because i had more control;

you can get a list of servers several ways, form osql to using SMO, but that can leave out servers that exist and do'nt broadcast, or even servers that exist but the service is not running;

so for a more complete list,

i used SQLRecon( as well as SQLPing) to create a text file listing every server on my network.

I used that list to try and connect with my domain credentials and a vb.NET app to create my connection string, and query master.dbo.sysdatabases/ master.sys.databases for the list of db's to fiddle with. i logged failure to connects as a followup item.

the next step (for me) was to look for a specific table in sysobjects/sys.tables of each database, as if the table "DBVersion" existed, then the db is related to a specific application we wrote, so we could do more stuff based on finding that table.

i did the same thing in TSQL as well, where i built and destroyed a linked server myLinkedServer, which got it's credentials changed each time i did sp_DropServer and added it back with new connection info from that list i mentioned above.

I had trouble logging failure to connect info, so that lead me towards.

i can fish out the code i used if you need a vb.net app, the TSQL stuff i think i'd have to recreate again.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

CirquedeSQLeil (8/24/2010)
You could look into Central Management Server. That is an option that is available with SQL 2008. You can execute a query against multiple servers at the same time.
http://msdn.microsoft.com/en-us/library/bb895144.aspx
From within that query you could do a search based on your requirements to search for a certain database name pattern.

Thanks for the tip Jason. You wouldn't happen to know of any common search across the SQL Versions between 2000, 2005 2008 & 2008 R2 would you? I have set up this central server and itw orks but I can't find a common query that runs across all the versions.

Thanks

Kindest Regards,

Just say No to Facebook!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Are you searching for dbname or was that just an example?

When I need a query to run across multiple versions (for instance in SSIS), I setup a query to determine SQL version.

SERVERPROPERTY('productversion')

Should prove useful. I typically look at the major version only for determining.

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

YSLGuru SSC-Insane Points: 21661 More actions · Answer 5

CirquedeSQLeil (8/24/2010)
Are you searching for dbname or was that just an example?
When I need a query to run across multiple versions (for instance in SSIS), I setup a query to determine SQL version.
SERVERPROPERTY('productversion')
Should prove useful. I typically look at the major version only for determining.

I was sarching by Table name. It might help to explain the reason I asked this to begin with.

We have an accounting software app that uses a SQL DB. I just starte here a little over a year ago and the company has been using this appl;ication for over a decade. It has gone thru several conversion and updates and so there are a couple dozen databases (all but 3 are old & not used normally) in all; some are backed up to bak files and some are just detached while other sare either offline or online and just seldom used.

I've spent most of my time getting the performance up and keeping it that way and had not had much time to go out and find all these older DB's and oprganize the lot. Now the accountants need one of these and finding it was tricky. If there was a global way in SQL to say "Show me all SQL Db's on all instances where the DB has a table named X" I could have quickly found the DB's this app uses.

In general I wish Microsoft would have come up with a simpler "MY SQL NETWORK" feature that, so long as your domain acct had access to do so, woudl go out and tell you every SQL Server instance on the domain. Oh well.

Thanks for the help.

Kindest Regards,

Just say No to Facebook!

WayneS SSC Guru Points: 95461 More actions · Answer 6

YSLGuru (8/24/2010)
CirquedeSQLeil (8/24/2010)
You could look into Central Management Server. That is an option that is available with SQL 2008. You can execute a query against multiple servers at the same time.
http://msdn.microsoft.com/en-us/library/bb895144.aspx
From within that query you could do a search based on your requirements to search for a certain database name pattern.
Thanks for the tip Jason. You wouldn't happen to know of any common search across the SQL Versions between 2000, 2005 2008 & 2008 R2 would you? I have set up this central server and itw orks but I can't find a common query that runs across all the versions.
Thanks

select name from master.dbo.sysdatabases should work across 2000-2008R2. However, read this from BOL:

Important:
This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jorge Segarra SSCrazy Points: 2571 More actions · Answer 7

While I don't have the code for it, I'm pretty sure you could perform this task using PowerShell. Lots of good resources out there such as Chad Miller, Max Trinidad, Laerte Junior, Aaron Nelson, etc. In fact here's the listing of all PowerShell MVP's: https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=PowerShell

=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management