0

I need to develop a C# function which, given a named SQL Server database object e.g. [MySchema].[MyStoredProcedure], will identify which file in a Visual Studio database project contains the definition of that object. C:\Source\Database\Database1\MySchema\SomeArbitraryCategory\StoredProcs\MyStoreProc.sql

I could grep all the files, I know, but the scale of our solutions precludes this.

Reorganising the file structure is not an option.

How might this best be achieved?

Hugh Jones
  • 2,706
  • 19
  • 30
  • Sounds like an XY problem. What are you going to use it for, exactly? – Roger Wolf May 20 '22 at 13:27
  • Once I have the filename I will be able to supply git history relating to the Database Object – Hugh Jones May 20 '22 at 13:36
  • @RogerWolf - I have to laugh - I tried to ask this question with background detail and got stomped all over for confusing the issues – Hugh Jones May 20 '22 at 13:37
  • https://stackoverflow.com/questions/72317398/how-to-expose-visual-studio-functionality-via-an-api?noredirect=1#comment127760443_72317398 – Hugh Jones May 20 '22 at 13:38
  • 1
    You could use the [T-SQL script dom](https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom?view=sql-dacfx-150) to identify file(s) with the desired `CREATE PROCEDURE` statement. This can be be done in PowerShell with a `CreateProcedureStatement` visitor class. I don't have time to write an answer now but the basic technique can be gleaned from [this article](https://www.dbdelta.com/microsoft-sql-server-script-dom/). – Dan Guzman May 20 '22 at 14:12
  • Every db project has a predictable location and name for a given object. Whether that's "Tables/do.MyTable.sql" it just "dbo.MyTable.sql". Is that not the case for you? – Ben Thul May 20 '22 at 14:21
  • @DanGuzman - We have talked about this before. In the end I used ANTLR. I am hoping that there is some project analysis library that will get me to the file name without parsing. – Hugh Jones May 20 '22 at 14:21
  • @BenThul - unfortunately not. There is around a dozen separate database projects in the solution and no real consistency as to what folders and sub-folders exist. Plus there is the problem of typographical errors which leads to some objects not being in an appropriately name file. – Hugh Jones May 20 '22 at 14:25
  • Interesting. Once you're in a VS database project, you kind of have to go out of your way to have the file name not match the object name in some way. I.e. if you want to create a new table, it's right click → New Table and it asks you what to call the file. Presumably you have a name in mind five seconds before actually wanting a new table. Either way, if this were me, I'd still proceed on the assumption that most files are sanely named and fall back to full grep when they don't. And when they don't, fix up the file name! – Ben Thul May 21 '22 at 15:07
  • @BenThul - nearly true but, for example, some coders like to put the definition of a trigger in with the table definition, some might have it in a separate file. You may be right on your second point, though; I might assume the file naming holds up first and then drop back to grepping/parsing. Problem is once you accept the need for the grep/parse logic it probably makes sense to populate a dictionary once and then use that for everything. – Hugh Jones May 23 '22 at 07:01
  • 1
    Possibly. And there is no universally right answer - only what's right for you. A couple of things inform that choice though. 1. how often does the naming convention fail you? "one in ten" is very different than "one in ten thousand". 2. what is the carrying cost of maintaining that dictionary? You mention populating it once but you'll have to keep it up to date as/when changes are made. – Ben Thul May 23 '22 at 14:11
  • @DanGuzman - I am just picking through the script-dom source code to see how it works. I have found it uses ANTLR. Perhaps you already knew that. – Hugh Jones May 24 '22 at 07:28
  • @HughJones, yes I did. I suggested the T-SQL script dom because that managed implementation is already aware of the current and historical T-SQL dialects and is fairly fast. You could of course re-invent the wheel if you enjoy doing so as a programming exercise. – Dan Guzman May 24 '22 at 09:36
  • @DanGuzman - I don't get paid for programming exercises ;P. The reason I may not use the Script Dom is that we already have investment in ANTLR using community-published grammars. Although the TSql grammar I am using is not fully complete it is adequate. That having been said, I am wavering; script generation is coming up on my radar ... – Hugh Jones May 24 '22 at 10:20

0 Answers0