create view out of dynamic query from INFORMATION_SCHEMA.COLUMNS

Question

Post reply

create view out of dynamic query from INFORMATION_SCHEMA.COLUMNS

d_martins

Old Hand

Points: 383

July 23, 2020 at 8:00 am

#3774051

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)

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

d_martins Old Hand Points: 383 More actions · Answer 2

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

d_martins Old Hand Points: 383 More actions · Answer 4

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?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

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

sgmunson SSC Guru Points: 110640 More actions · Answer 6

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

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.

dkultasev SSC Rookie Points: 34 More actions · Answer 8

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.