Querying Visual Studio project files using T-SQL and Powershell

jamiet — Mon, 17 Jan 2011 21:11:00 GMT

Earlier today I had a need to get some information out of a Visual Studio project file and in this blog post I’m going to share a couple of ways of going about that because I’m pretty sure I won’t be the only person that ever wants to do this. The specific problem I was trying to solve was finding out how many objects in my database project (i.e. in my .dbproj file) had any warnings suppressed but the techniques discussed below will work pretty well for any Visual Studio project file because every such file is simply an XML document, hence it can be queried by anything that can query XML documents.

Ever heard the phrase “when all you’ve got is hammer everything looks like a nail”? Well that’s me with querying stuff – if I can write SQL then I’m writing SQL. Here’s a little noddy database project I put together for demo purposes:

Two views and a stored procedure, nothing fancy. I suppressed warnings for [View1] & [Procedure1] and hence the pertinent part my project file looks like this:

  <ItemGroup>
    <Build Include="Schema Objects\Schemas\dbo\Views\View1.view.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151,3276</SuppressWarnings>
    </Build>
    <Build Include="Schema Objects\Schemas\dbo\Views\View2.view.sql">
      <SubType>Code</SubType>
    </Build>
    <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procedure1.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
    </Build>
  </ItemGroup>
  <ItemGroup>

Note the <SuppressWarnings> elements – those are the bits of information that I am after.

With a lot of help from folks on the SQL Server XML forum I came up with the following query that nailed what I was after. It reads the contents of the .dbproj file into a variable of type XML and then shreds it using T-SQL’s XML data type methods:

DECLARE @xml XML; 
SELECT @xml = CAST(pkgblob.BulkColumn AS XML) 
FROM   OPENROWSET(BULK 'C:\temp\QueryingProjectFileDemo\QueryingProjectFileDemo.dbproj' -- <-Change this path! 
                   ,single_blob) AS pkgblob 
                   
;WITH XMLNAMESPACES( 'http://schemas.microsoft.com/developer/msbuild/2003' AS ns) 
SELECT  REVERSE(SUBSTRING(REVERSE(ObjectPath),0,CHARINDEX('\',REVERSE(ObjectPath)))) AS [ObjectName] 
       ,[SuppressedWarnings] 
FROM   ( 
       SELECT  build.query('.') AS [_node] 
       ,       build.value('ns:SuppressWarnings[1]','nvarchar(100)') AS [SuppressedWarnings] 
       ,       build.value('@Include','nvarchar(1000)') AS [ObjectPath] 
       FROM    @xml.nodes('//ns:Build[ns:SuppressWarnings]') AS R(build) 
       )q

And here’s the output:

And that’s it – an easy way of discovering which warnings have been suppressed and for which objects in your database projects. I won’t bother going over the code as it is fairly self-explanatory – peruse it at your leisure.

Once I had the SQL above I figured I’d share it around a little in case it was ever useful to anyone else; hence I’m writing this blog post and I also posted it on the Visual Studio Database Development Tools forum at FYI: Discover which objects have had warnings suppressed. Luckily Kevin Goode saw the thread and he posted a different solution to the same problem, one that uses Powershell. The advantage of Kevin’s Powershell approach is that it is easy to analyse many .dbproj files at the same time. Below is Kevin’s code which I have tweaked ever so slightly so that it produces the same results as my SQL script (I just want any object that had had a warning suppressed whereas Kevin was querying specifically for warning 4151):

cd 'C:\Temp\QueryingProjectFileDemo\'
cls
$projects = ls -r -i *.dbproj
Foreach($project in $projects)
{
  $xml = new-object System.Xml.XmlDocument
  $xml.set_PreserveWhiteSpace( $true )
  $xml.Load($project)

  #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings=4151]/@Include"}
  #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[contains(e:SuppressWarnings,'4151')]/@Include"}
  $xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings]/@Include"}
  $ns = @{ e = "http://schemas.microsoft.com/developer/msbuild/2003" }

  $xml | Select-Xml -XPath $xpath.Start  -Namespace $ns |Select -Expand Node | Select -expand Value
}

and here’s the output:

Nice reusable Powershell and SQL scripts – not bad for an evening’s work. Thank you to Kevin for allowing me to share his code.

Don’t forget that these techniques can easily be adapted to query any Visual Studio project file, they’re only XML documents after all! Doubtless many people out there already have code for doing this but nonetheless here is another offering to the great script library in the sky. Have fun!

@Jamiet

This blog post was mentioned on This Week on Channel 9. Fast forward to 10m49s.

Some thoughts on Visual Studio database references and how they should be used for SQL Server BI

jamiet — Tue, 14 Sep 2010 12:53:00 GMT

Over the past few weeks I have been making heavy use of the Database tools in Visual Studio 2010 (formerly known as datadude, DBPro, VSTS for Database Professionals or one of a plethora of other names that it has gone by over the past few years) and one of the features that has most impressed me has been database references.

Database references allow you to have stored procedures in your database project that refer to objects (tables, views, stored procedures etc…) that exist in other database projects and hence when you build your database project it is able to resolve those references. Gert Drapers has a useful introduction to them at his blog post Database References; in his words database references allow you to:

represent and resolve 3 and/or 4-part name usage inside a database project. Database references are conceptually the same as assembly references inside a C# or VB.NET projects; they allow you to reference objects from your database project that live inside another namespace (database).

It occurred to me that similar functionality would be incredibly useful for SQL Server Integration Services(SSIS), Analysis Services (SSAS) & Reporting Services (SSRS) projects. After all reports, packages and data source views are rife with references to database objects – why shouldn’t we be able to have design-time dependency checking in our BI projects the same way that database and .Net developers do?

Here are some examples of ways in which this would be useful:

If a SSRS report pulls data from a view, I firstly want to know (at design-time) that that view exists. Secondly, if the view definition changes I want to know that my report will still work.
Similarly for a SSAS cube, if a view referenced in the DSV changes or disappears I want my SSAS project build to fail or succeed accordingly
Similarly for a SSIS Execute SQL Task, if it executes a stored procedure I want to know that the named parameters that I am using actually exist in the stored procedure.

To this end I have submitted three identical suggestions to Connect, one each for SSIS, SSAS & SSRS:

[SSAS] Declare database object dependencies

[SSRS] Declare database object dependencies

[SSIS] Declare database object dependencies

I have said before in my blog post The SQL developer gap that we database and BI developers deserve as much love from Microsoft as our .Net counterparts do and I’m of the opinion that providing design-time dependency checking across the full gamut of BI projects would be a huge step in the right direction. The holy grail is an object dependency graph from database tables all the way up through database views, database functions, stored procedures, DAL, ORM layer & business logic layer all the way through to presentation – I don’t know how long it will take but we will get there one day I am sure, hopefully sooner rather than later!

@JamieT

UPDATE: This feature (i.e. the ability to define dependencies on database objects at build time) is coming to the Entity Framework as confirmed in this Connect submission: http://bit.ly/igmEiS. Let's hope the same comes to SSIS/SSAS/SSRS soon.

Search results matching tags 'Visual Studio' and 'Datadude'

Querying Visual Studio project files using T-SQL and Powershell

Some thoughts on Visual Studio database references and how they should be used for SQL Server BI