create view out of dynamic query from INFORMATION_SCHEMA.COLUMNS

  • Hi,

    Is there a way to create a view out if this query below?

    DECLARE @query nvarchar(max) 
    SELECT @query = STUFF(
    (
    SELECT ' UNION ALL SELECT ''' + TABLE_SCHEMA + ''' AS SCHEMANAME, ' + '''' + TABLE_NAME + ''' AS TABLENAME, ' + 'LEFT(''' + TABLE_NAME + ''', 3) AS SYSTEM, ' + 'MAX(DATETIME) AS last_refresh_date FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'DATETIME' and TABLE_SCHEMA = 'DUMMY_SCHEMA'
    FOR XML PATH('')
    ), 1, 11, '')
    EXEC(@query)
  • I assume you've tried and the error explained that your column names are not there. You can't create a view based on ad hoc queries because the view definition needs to know what the heck is happening to it in order to be defined. Same problem if you try to make it a table-valued function. The only way to programmatically run a query like this is through a stored proc.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks Grant - YES I did tried straightforwardly to

    CREATE VIEW view_name AS...

    but it didn't work and lately, after some research, found out that it is not possible as you explained.

    My goal is to have a view containing the values of this column 'DATETIME' from all the tables in the DB, dynamically because we keep adding and removing tables frequently. That's why I use INFORMATION_SCHEMA.COLUMNS.

    Can this be achieved via a stored proc? Can you help out on this

  • A stored proc would just use the script you have. It'll work.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I was able to create the stored proc but had to adapt the quotes and in the end I don't know if any of this is valid

    CREATE PROCEDURE createview_dummy as

    exec('CREATE VIEW dummy AS(

    DECLARE @querya nvarchar(max)
    SELECT @querya = STUFF(
    (
    SELECT '' UNION ALL SELECT '''' + TABLE_SCHEMA + '''' AS SCHEMANAME, '' + ''''' + TABLE_NAME + '''' AS TABLENAME, '' + 'MAX(DATETIME) AS last_refresh_date FROM ['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']''
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'DATETIME' and TABLE_SCHEMA IN ('DUMMY')
    FOR XML PATH('')
    ), 1, 11, '')
    EXEC(@querya))')

    When running the stored proc :

    Msg 156, Level 15, State 1, Procedure createview_dummy, Line 9 [Batch Start Line 0]
    Incorrect syntax near the keyword 'UNION'.
    Msg 102, Level 15, State 1, Procedure createview_dummy, Line 9 [Batch Start Line 0]
    Incorrect syntax near 'DATETIME'.

    You see what am I doing wrong here?

  • I think you misunderstood what Grant was telling you to do.  You can't create a VIEW inside of a PROCEDURE, just take your original query and put that in the PROCEDURE like:

    CREATE PROCEDURE GetAllLastRefreshDates AS
    DECLARE @query nvarchar(4000);
    SELECT @query = STUFF(
    (
    SELECT ' UNION ALL SELECT ''' + TABLE_SCHEMA + ''' AS SCHEMANAME, ' + '''' + TABLE_NAME + ''' AS TABLENAME, ' + 'LEFT(''' + TABLE_NAME + ''', 3) AS SYSTEM, ' + 'MAX(DATETIME) AS last_refresh_date FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'DATETIME' and TABLE_SCHEMA = 'DUMMY_SCHEMA'
    FOR XML PATH('')
    ), 1, 11, '');
    EXEC (@query);
    GO
  • And one more thing:  Your dynamic query starts with UNION ALL...  and that's going to cause a syntax error.   Not sure why you have that in there, and maybe you just need to remove that part.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • sgmunson wrote:

    And one more thing:  Your dynamic query starts with UNION ALL...  and that's going to cause a syntax error.   Not sure why you have that in there, and maybe you just need to remove that part.

    If you look, it's the FOR XML PATH trick to build a string by concatenating data from multiple rows.  His STUFF function around that will essentially remove the first UNION ALL from the resulting string.

  • Small tip: you can always do PRINT/SELECT instead of EXEC of  dynamically built query, then copy/paste it to SSMS and you'll see all syntax errors.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply