March 20, 2015 at 1:10 am
Hi All,
I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.
Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .
The view is called TEMP_EXPORT_1
I can either use the code below to return a list of columns that I want removed:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'
Or I can use the code below to return the list of columns that I want to keep:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'
Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...
Therefore I tried the following but I'm not sure of the syntax:
ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1
Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.
March 20, 2015 at 6:06 am
well i'm betting that all the column names are aliases, right? the base tables don't have columns that have that pattern. in that case you can't alter the view without reading the code, and commenting out the values (views can certainly be dropped and recreated, by the way)
if you want to do this semi dynamically without looking at the columns, i think the solution might be a view over a view, and all the performance horrors that could potentially entail.
something like this would build your view to put over the top of the existing view, and would not have your offending columns in it.
if an applciaiton consume sthe current view, you could rename the original view to Base_TEMP_EXPORT_1 or something and replace the original view with one that now points to base, using this as a code example.
SELECT
'CREATE VIEW vwTEMP_EXPORT_1 AS SELECT '
+ s.Colzs
+ ' FROM TEMP_EXPORT_1'
FROM(SELECT
Colzs = STUFF((SELECT ',' + quotename(name)
FROM sys.columns
where object_name(object_id) = 'TEMP_EXPORT_1'
AND name NOT LIKE '%1%_2'
ORDER BY column_id
FOR XML PATH('')
),1,1,'')
) s
Lowell
March 20, 2015 at 6:40 am
Thanks for the quick reply!
Yep that code is exactly what I wanted and works perfectly... I was trying to do a view over view approach but I didn't realise you couldn't overwrite the original view in the same step - Code works well and performance is fine for what we need.
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply