Accessing Project files for use in other files

[Absinthe]

I posted this before in the macro forum, and though I got lots of views I got zero responses. Hopefully someone in this forum may have some advise...


When working with a database I use a PROJECT with some very specific structure. For instance:

I will simplify this for brevity but conceptually the layout is right

my project (prj) goes in a folder:

c:\sandboxes\projects\Install_<ProjectName>_<VersionNumber>\

If my project was for a database called Test_Data and it was the first one it would be
c:\sandboxes\projects\Install-Test_Data-1.0.1\Test_Data.prj
I assume this is my "Project Directory"

The source files in this case would reside in:
c:\sandboxes\packages\<ProjectName>\
With no version information. This is what I have defined as the "Working Directory" in the project properties

Which in this case would look like:
c:\sandboxes\packages\Test_Data\
And have directories like:

c:\sandboxes\packages\Test_Data\Tables\
This will contain my table definitions in files named like:
    dbo.Customer.sql
    dbo.Invoice.sql
and so forth with the first part of the name being the "Owner" and the second part being the actual table name.

c:\sandboxes\packages\Test_Data\Views\
c:\sandboxes\packages\Test_Data\Triggers\
c:\sandboxes\packages\Test_Data\Stored Procedures\

That is all background information to the next set of questions.

Since, within the confines of my project definition, I have access to all the table definitions (DDL), I would like to be able to use the data they contain to do some intelligent coding. For example, if I were doing a SELECT statement, it is very common to specifically name each column but in large tables that becomes a lot of typing to a visit to some other tool to accomplish it.

So I want to be able to prompt for a list of table names. These would be almost equivalent to the list of files in the tables subdirectory to my working directory (not my project directory)

After that, I would like to use the text in those table definitions to generate a list of attributes, or arguments, or where clause, or even allow me to intellisence what follows the table name and a period.

Can anyone give me a push in the right direction to get me started with this?

[MindprisM]

What you want I think is a custom parser that will update tags.db, though I am not sure that would provide a satisfactory solution due to the heavy coding required to handle things like SELECT * FROM top_view -- where top_view has to dig down through many potential select * and union select * in order to achieve a column list.

An alternate method might be to create a macro that does an analysis on your files using greps to extract the members, and then from that data structure create a popup menu that would inject identifiers:

Menu:

Tables
  Tablename1
    Tablename1 <paste tablename1>
    Columname <paste columnname>
    Columname <paste columnname>
  Tablename2
    Tablename2 <paste tablename2>
    Columname <paste columnname>
    Columname <paste columnname>
Views
Triggers
Functions


It wouldnt be context sensative, but it wouldnt take forever to code either. Use nested hash tables for the data structure.

Other than that, Red Gate SQL Prompt works in SQL Server Management Studio (2005 or 2008, including SSMS Express), Visual Studio (2005 or 2008), or Query Analyzer.