April 13, 2008 at 1:54 am
Hello,
I create select statment:
select 1 as Status, * from table1
The problem is that table1 already have "Status" column, so when I pit the select in view I got error.
I can't replace * in columns names, due the user may added columns from application.
Any suggestion how I get rid from second "Status"?
Thanks.
April 13, 2008 at 8:03 am
no way to get around it.
btw if a user add a column to a table, you must recompile the view if yo want that column to be available using this view.
You may want to generate the columnlist right before you create the view..... and right before you refresh the view (if you want to view to contain all columns)
Keep in mind to uniquefy the columns.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2008 at 11:29 am
it's really really not a good idea to use select * in a view.
---------------------------------------
elsasoft.org
April 13, 2008 at 12:04 pm
Thanks for respond.
I don't have choise. I need to use view because we change db schema and the view wil represent table tah was dropped.
April 13, 2008 at 12:59 pm
Use this stored procedure to create your views (modify it for your specific needs). Whenever the tables' columns change, re-run this procedure instead of recompiling the views:
Alter Proc spCreate_Status_View(@Table as varchar(255), @View varchar(255), @Schema as varchar(255)='dbo') as
/*
proc to automatically generate a table's View.
BYoung, Proactive Perf Solutions, 2008
*/
Declare @sql varchar(4000)
--Start the command with either CREATE or ALTER:
IF Exists( Select * from INFORMATION_SCHEMA.TABLES
Where TABLE_SCHEMA = @Schema
And TABLE_NAME = @View
And TABLE_TYPE = 'VIEW')
Set @sql = 'ALTER '
ELSE
Set @sql = 'CREATE '
--add in the common prefix
Set @sql = @sql + 'View ' + @Schema + '.' + @View + ' AS
Select 1 as [Status]'
--Enumerate all of the columns, changing table's [Status] to [Table_Status]
Select @sql = @Sql + '
, [' + Column_Name + ']'
+ Case Column_Name When 'Status' Then ' as Table_Status' Else '' End
From INFORMATION_SCHEMA.COLUMNS
Where Table_Schema = @Schema
And Table_Name = @Table
Order by ORDINAL_POSITION
--add on the commun suffix
Set @sql = @sql + '
From ' + @Schema + '.' + @Table + '
'
Print 'Generating View with the following command:'
Print @sql
EXEC (@sql)--Creates or re-Creates the View
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 15, 2008 at 1:18 am
Thanks.:)
April 16, 2008 at 4:45 am
And this was question of the day 😀
http://www.sqlservercentral.com/Forums/FindPost485493.aspx
Hiding under a desk from SSIS Implemenation Work :crazy:
April 16, 2008 at 9:30 am
boazs (4/15/2008)
Thanks.:)
Glad I could help. Let us know how it works out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply