Where does sharePoint store Web application information so we can report full site collection URLs in a SQL report?

Question

I know MS does not support SQL commands against SharePoint Databases. But we are just reading data using SQL and SSRS so no harm to be done.

I have SQL Syntax that querys every content DB WEBS table and report site collection titles, content DB name, relative URL (without the domain or web application root) and site request email. It gives us a nice report of all of our sitecollections.

It's great, but any way to report the full url for each site collection? or aleast information about the what web application a contect DB and site collection belongs to? We have 10 Web applications. I see siteId keys, but where does SharePoint store Web application information.. I've been looking for it in the Config and AdmincContent DBs but don't see it anywhere.

Please do not instruct me to use the SharePoint Object model, STSADM, Powershell or Web Services to get this information. It must be in SQL.

Answer 1

In a production environment, even read-only queries directly against SharePoint's databases are unsupported. This is because any sort of query can lock up some or all of the rows within a database and lead to degraded performance for end users and/or data corruption for your SharePoint environment. This is why Microsoft does not support direct access of SharePoint's SQL databases and requires that you access that information via the SharePoint Object Model, SharePoint Web Services, or its PowerShell cmdlets.

Flatly put, SQL is not the right tool for you to get this information out of a live Production SharePoint environment, you are at a minimum putting the environment at risk because Microsoft will not support this type of activity. I would strongly recommend that you consider another alternative method to obtain this information, PowerShell is how I would go about getting it.

Answer 2

The best supported way to do this if it needs to be in a sql database would be to create a separate custom sql database and use the SharePoint api to extract the data from SharePoint and sync it to your custom db.

Then you can use whatever reporting tool you like to generate reports.