Generate a view/query from a table RRS feed

  • Question

  • Hi,

    I am trying to come up with a way to generate the code to create a view from the data stored in a table but I've no idea how to begin with this.......

    The idea would be that I would have a table which would include the name of the person making the request, the date of the request, the database name, table name(s) and field names.  For example:

    Requester                  Date                 Database                Table                  Field

    JT                               05/01/16          A                              A                        A

    JT                               05/01/16          A                              A                        B

    JT                               05/01/16          A                              A                        C

    JT                               05/01/16          A                              B                        B


    From this table I then want to generate code for each request (could be multiple requests in the same table) something like below:

    USE A


    CREATE VIEW [dbo].[JT - 05/01/16]



               A.A, A.B, A.C, B.B


              A LEFT JOIN B ON A.ID = B.ID

    Does anyone have any ideas how/where I might begin with something like this?  I thought that I would find some ideas on google as I assumed that someone would have done something similar before, but I've not had any luck so far.  Thanks.

    Tuesday, January 5, 2016 8:46 AM

All replies

  • Hi,

    You can achieve it using dynamic sql


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Tuesday, January 5, 2016 8:49 AM
  • Hi

    I would NOT recommend  you doing such manipulation in T-SQL, instead of use .NET programming language .....

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 5, 2016 8:56 AM
  • What is the source of the columns specified in the join predicate?  What about different types of joins (OUTER/INNER)?  In your example, why is table A on the LEFT Side and table B on the right, instead of visa-versa?

    I think you need a data dictionary with relationship meta-data to supplement this request table.

    Dan Guzman, Data Platform MVP,

    Tuesday, January 5, 2016 9:42 AM
  • You can do this by generating script using dynamic sql. That being said its not straight forward when it involves multiple tables with composite join conditions or when there are unrelated tables which will make code ugly.

    Can you explain the need of this requirement? What is the purpose of creating the views like this and how they will be used? 

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, January 5, 2016 10:07 AM
  • You can find QueryDesigner from visual studio itself. Try to use it.
    Tuesday, January 5, 2016 10:12 AM
  • I did think that I might be able to use Dynamic SQL to do this but I've not managed to figure out how yet - most of my SQL experience is based around creating views so I am still learning how to do some things in SQL. 

    With regards to using other tools or programming languages I don't really have much experience of programming outside of using SQL so I thought it would be best/easiest to do this in SQL if it is possible.

    What we are trying to do is create a system to enable users to easily request data from specific databases.  Most of the system has already been set up as an access web database and we were originally looking at generating the code in the Access database and then loading it to SQL, but were unable to figure out a solution - therefore I came up with the idea of loading a list of the data requested into a table in a SQL database and then using a stored procedure to create the code for each request.

    In my example I listed columns from only two tables, but the request could potentially be for data from 1 table to all of the tables in the database.  For this example table A was the primary table to which all of the other tables would be connected (we’d always have values for the three columns from table A) and table B contains additional data that has been requested.  Whilst in this case an INNER JOIN would have worked I used a LEFT JOIN because as more tables are added to the query there is a chance one or more of them might not contain data and therefore if I used an INNER JOIN some data could be missed in the final results.

    Hopefully this gives a better idea of what it is we are trying to do - I thought that it was worth asking in case anyone already had done something similar, but I will continue to research as well to find a solution.  Many thanks for the ideas so far.

    Tuesday, January 5, 2016 1:42 PM
  • I think perhaps you should look to existing solutions for this, such as Business Objects Universe, MicroStrategy or SQL Server Analysis Services.

    Each of these products allow for the creation of objects on the backend, which can then be presented to the end user for final manipulation.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Tuesday, January 5, 2016 3:18 PM