{"id":66473,"date":"2006-09-14T14:58:00","date_gmt":"2006-09-14T14:58:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/09\/14\/how-can-i-get-a-list-of-all-the-databases-on-a-sql-server-computer\/"},"modified":"2006-09-14T14:58:00","modified_gmt":"2006-09-14T14:58:00","slug":"how-can-i-get-a-list-of-all-the-databases-on-a-sql-server-computer","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-get-a-list-of-all-the-databases-on-a-sql-server-computer\/","title":{"rendered":"How Can I Get a List of All the Databases on a SQL Server Computer?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I get a list of all the databases on a SQL Server computer?

— ER<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, ER. Before we answer your question, we\u2019d like to take a moment to offer our opinions on all the major \u2013 and controversial \u2013 issues of the day. We can do that because the Scripting Editor is on vacation and, well, you know what they say: when the cat\u2019s away, the mice will play.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Did we forget to call this a \u201cwell-deserved\u201d vacation? No; forgetfulness had nothing to do with us not calling it that.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

To tell you the truth, having the opportunity to show off the depth and breadth of his knowledge is important to the Scripting Guy who writes this column. That\u2019s because he has long been typecast as some sort of nerd who cares only about computers. For example, when he worked at the University of Washington he was on his way out for the day when he was asked to stop by the Dean\u2019s office and fix a computer. To do so he set down his book \u2013 a collection of short stories by P.G. Wodehouse \u2013 on a nearby table.<\/P>\n

\u201cP.G. Wodehouse! I just love<\/I> P.G. Wodehouse!\u201d declared the administrator when she saw the book lying there. \u201cWhose book is this?\u201d<\/P>\n

\u201cMine,\u201d said the future Scripting Guy.<\/P>\n

“No, really, whose book is this?\u201d she said. \u201cI\u2019d love to talk to them about it.\u201d<\/P>\n

\u201cIt\u2019s mine,\u201d insisted the Scripting Guy.<\/P>\n

\u201cNever mind,\u201d she said. \u201cI\u2019ll find out later whose book it is.\u201d<\/P>\n

As she later explained, “I had no idea you’d ever read anything other than computer books.” <\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. True fact: the Scripting Guy who writes this column has never<\/I> read a book about computers or technology, at least not all the way through. Well, provided you don\u2019t count The Brave Little Toaster<\/I>, of course.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

But now, at long last, the completely un-nerdish Scripting Guy who writes this column has a chance to show off his knowledge of current events. That\u2019s right, current events. All the important things going on in the world today. Important things he knows all about. Yes. All his knowledge and wisdom, all those things he\u2019s been dying to say but has never been allowed to.<\/P>\n

Right \u2026. <\/P>\n

So, um, that Dred Scott decision of 1857: what was up with that<\/I>? <\/P>\n

You know what? When the Scripting Editor gets back she\u2019ll no doubt go through and delete all these insightful — albeit controversial — opinions anyway. It wouldn\u2019t be very nice to have her to come back from vacation and then have to do all that work, doesn’t? Therefore, solely in the spirit of making life easier for the Scripting Editor, let\u2019s just show you a script that can list all the databases on a computer running Microsoft SQL Server:<\/P>

strComputer = “atl-ds-01” <\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)<\/p>\n
objConnection.Open _\n    “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _\n        “Trusted_Connection=Yes;Initial Catalog=Master”<\/p>\n
Set objRecordset = objConnection.Execute(“Select Name From SysDatabases”)<\/p>\n
If objRecordset.Recordcount = 0 Then\n    Wscript.Echo “No databases could be found.”\nElse\n    Do Until objRecordset.EOF\n        Wscript.Echo objRecordset.Fields(“Name”)\n        objRecordset.MoveNext\n    Loop\nEnd If\n<\/PRE>\n
So how does this script work? How the heck would we know? What do we look like, a bunch of nerds?<\/P>\n
Good point; we probably should<\/I> get rid of pocket protectors, shouldn\u2019t we? OK, well, in that case, the script starts out by assigning the name of the target computer to a variable named strComputer. We then use this line of code to create an instance of the ADODB.Connection<\/B> object, the object we need to connect to the SQL Server service on the target computer:<\/P>
Set objConnection = CreateObject(“ADODB.Connection”)\n<\/PRE>\n
As soon we have a Connection object we can then call the Open<\/B> method to actually connect to SQL Server:<\/P>
objConnection.Open _\n    “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _\n        “Trusted_Connection=Yes;Initial Catalog=Master”\n<\/PRE>\n
We won’t explain SQL Server connections and connection strings in any detail today; however, we will note that we pass the Open method several parameters:<\/P>\n\n<\/THEAD>\n\n\n\n\n\n\n
\n
Parameter<\/B><\/P><\/TD>\n
\n
Description<\/B><\/P><\/TD><\/TR>\n
\n
Provider<\/P><\/TD>\n
\n
Essentially indicates the type of database we’re connecting to. Because we’re connecting to SQL Server, we use the SQLOLEDB provider, which is short for “Microsoft OLE DB Provider for SQL Server.\u201d<\/P>\n
And no, you don\u2019t have to be a nerd to know what SQLOLEDB is short for.<\/P><\/TD><\/TR>\n
\n
Data Source<\/P><\/TD>\n
\n
Denotes the computer we’re connecting to. Here we indicate the computer by using the variable strComputer.<\/P><\/TD><\/TR>\n
\n
Trusted_Connection<\/P><\/TD>\n
\n
Indicates the user authentication mode. Setting this value to Yes<\/I> causes us to connect uses Windows Authentication mode, which happens to be the recommended way of connecting to SQL Server.<\/P><\/TD><\/TR>\n
\n
Initial Catalog<\/P><\/TD>\n
\n
Specifies the default catalog to use when making the connection. We use the Master catalog because that’s where we’ll find the list of databases.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After opening the Master catalog we can then use the Execute<\/B> command to return a recordset consisting of all the databases found on this SQL Server machine. To do that we execute the following query, which simply selects the Name<\/B> of each item (that is, each database) stored in the table SysDatabases<\/B>:<\/P>
Set objRecordset = objConnection.Execute(“Select Name From ysDatabases”)\n<\/PRE>\n
So what do we do with that recordset once we have it? Well, to begin with, we check the value of the RecordCount<\/B> which tells us how many items are in the recordset. If the RecordCount is 0, then there aren\u2019t any<\/I> databases on this computer; in turn, we echo back a statement to that effect.<\/P>\n
Of course, the odds are pretty good that the RecordCount won\u2019t<\/I> be 0, meaning the computer has at least one database. If that\u2019s the case, we then set up a Do Until loop that loops through the entire collection of records (or, as we nerds would put it, until the recordset\u2019s EOF<\/B> property \u2013 the End of File property \u2013 is True). Inside that loop we do two things: we echo back the value of the each record’s Name field, and we use the MoveNext<\/B> method to advance from one record to the next:<\/P>
Wscript.Echo objRecordset.Fields(“Name”)\nobjRecordset.MoveNext\n<\/PRE>\n
Incidentally, don\u2019t forget the MoveNext; if you do, your script will simply echo back the name of the first database in the collection, and then continue to echo that one name forever and ever. That\u2019s because, with databases, you have to explicitly tell them to move on to the next record.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Do we know that through personal experience, like, say, because we left that line out when we first sat down to write the script for today\u2019s column? Well, we \u2013 um, sorry, but Microsoft doesn’t like us to talk about personal experiences in our columns ….<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
And what do we get when we\u2019re all done? Why, we get a list of all the databases on the computer, of course:<\/P>
master\ntempdb\nmodel\nmsdb\npubs\nNorthwind\nCustomerScripts\nCommunityScripts\n<\/PRE>\n
Which is all we ever really wanted to get.<\/P>\n
Now, tell us the truth, ER: do you<\/I> think the Scripting Guy who writes this column is a nerd? (Note: No fair asking the Scripting Son for his<\/I> opinion of dear old dad.) No, really, we want to know your honest opinion.<\/P>\n
Yeah, well, who asked you anyway?<\/P>\n
But it really was<\/I> his book. Really.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I get a list of all the databases on a SQL Server computer?— ER Hey, ER. Before we answer your question, we\u2019d like to take a moment to offer our opinions on all the major \u2013 and controversial \u2013 issues of the day. We can do that because the Scripting […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[19,146,3,176,5],"class_list":["post-66473","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-scripting-guy","tag-sql-server","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I get a list of all the databases on a SQL Server computer?— ER Hey, ER. Before we answer your question, we\u2019d like to take a moment to offer our opinions on all the major \u2013 and controversial \u2013 issues of the day. We can do that because the Scripting […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66473","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66473"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66473\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66473"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66473"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}