CODEWITHSUNDEEP
restazureazure-sql-database
Anand Srinivasan
Anand Srinivasan

Reputation: 55

Listing SQL Azure databases

We are able to list down all the database servers corresponding to a specific subscription using the below request URL.

https://management.database.windows.net:8443/SunscriptionID/servers

x-ms-version=1.0.

Would like to know , whether there are any restful services available to get the list of databases inside each specific server.???

Upvotes: 0

Views: 159

Answers (2)

Sandrino Di Mattia
Sandrino Di Mattia

Reputation: 24895

You can use the SOAP management service for this. First you'll need to get an authentication token (using basic authentication with the username/password of the server):

GET https://MYSERVERNAME.database.windows.net/v1/ManagementService.svc/GetAccessToken HTTP/1.1
sqlauthorization: Basic smafizjfpafijpoazjeoijfamz=
Host: MYSERVERNAME.database.windows.net

The response will be the authentication token together with a cookie:

Set-Cookie: .SQLSERVERMANAGEMENT=fmazoijfazmojf==; 

<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">ffmjjzoipfjpozajfpzofjjmoezafjimozafjoamfjiazfamzofijzmfoijzafjoizafazamijfiiozfmozafjozamfjzmofjizmfozjfohfomzaifjzaomfjizamofzijamofjafmozafjmzafojzmijfzoifzajoimfzaijomfmoizaj==</string>

Then you can use the cookie and the authentication token to get a list of databases for a specific server:

GET https://MYSERVERNAME.database.windows.net/v1/ManagementService.svc/Server2('MYSERVERNAME')/Databases HTTP/1.1
AccessToken: ffmjjzoipfjpozajfpzofjjmoezafjimozafjoamfjiazfamzofijzmfoijzafjoizafazamijfiiozfmozafjozamfjzmofjizmfozjfohfomzaifjzaomfjizamofzijamofjafmozafjmzafojzmijfzoifzajoimfzaijomfmoizaj==
x-ms-client-session-id: e5a2b587-930f-4cb1-82bc-2be5385f6ad1-2012-11-26 20:12:14Z
x-ms-client-request-id: 1ac2599c-c4dc-4a66-822b-15f81ba8216b-2012-11-26 20:12:14Z
Accept: application/atom+xml,application/xml
Accept-Charset: UTF-8
Host: MYSERVERNAME.database.windows.net
Cookie: .SQLSERVERMANAGEMENT=fmazoijfazmojf==

And the response will be a list of databases:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="https://MYSERVERNAME.database.windows.net/v1/ManagementService.svc/Server2('MYSERVERNAME')/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">Databases</title>
  <id>https://MYSERVERNAME.database.windows.net/v1/ManagementService.svc/Server2('MYSERVERNAME')/Databases</id>
  <updated>2012-11-26T20:19:32Z</updated>
  <link rel="self" title="Databases" href="Databases" />
  <entry>
    <id>https://MYSERVERNAME.database.windows.net/v1/ManagementService.svc/Server2('MYSERVERNAME')/Databases(1)</id>
    <title type="text"></title>
    <updated>2012-11-26T20:19:32Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="Database" href="Databases(1)" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Server" type="application/atom+xml;type=entry" title="Server" href="Databases(1)/Server" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/DatabaseMetrics" type="application/atom+xml;type=entry" title="DatabaseMetrics" href="Databases(1)/DatabaseMetrics" />
    <category term="Microsoft.SqlServer.Management.Server.Domain.Database" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:Id m:type="Edm.Int32">1</d:Id>
        <d:Name>master</d:Name>
        <d:Edition>Web</d:Edition>
        <d:MaxSizeGB m:type="Edm.Int32">5</d:MaxSizeGB>
        <d:CollationName>SQL_Latin1_General_CP1_CI_AS</d:CollationName>
        <d:CreationDate m:type="Edm.DateTime">2012-11-26T14:36:41.387</d:CreationDate>
        <d:IsSystemObject m:type="Edm.Boolean">true</d:IsSystemObject>
        <d:Status m:type="Edm.Int32">1</d:Status>
        <d:IsFederationRoot m:type="Edm.Boolean">false</d:IsFederationRoot>
        <d:SizeMB m:type="Edm.Decimal">-1.00</d:SizeMB>
        <d:IsRecursiveTriggersOn m:type="Edm.Boolean">false</d:IsRecursiveTriggersOn>
        <d:IsReadOnly m:type="Edm.Boolean">false</d:IsReadOnly>
      </m:properties>
    </content>
  </entry>
</feed>

Upvotes: 0

Igorek
Igorek

Reputation: 15850

You can connect to the master database of each SQL Azure server and query the sys.databases

SELECT * FROM sys.databases

Upvotes: 1

Related Questions