Hello there,

I was wondering if anyone knew if it were possible to make sql server 2005
cascade field name changes down into dependant views.

For example if I have a table
table1
- id
- title

and a view
view1
select id from table1

Can I somehow make it so that if I change table1.id to table1.myId the view
will automatically update?

Kinda just the same way MS-Access does it. That'd be neat.

Thank you kindly for any ideas
John Sheppard

John Sheppard (nospam@spamspam.com) writes:
> I was wondering if anyone knew if it were possible to make sql server 2005
> cascade field name changes down into dependant views.
>
> For example if I have a table
> table1
> - id
> - title
>
> and a view
> view1
> select id from table1
>
> Can I somehow make it so that if I change table1.id to table1.myId the
> view will automatically update?

No. In fact if you have a view with SELECT * in, and add/drop/rename a
column in a table, you need to do sp_refreshview for the view definition
to change. (Using SELECT * view definitions is not considered best
practice, by the way.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9A6C807F8CFC8Yazorman@127.0.0.1...
> John Sheppard (nospam@spamspam.com) writes:
>> I was wondering if anyone knew if it were possible to make sql server
>> 2005
>> cascade field name changes down into dependant views.
>>
>> For example if I have a table
>> table1
>> - id
>> - title
>>
>> and a view
>> view1
>> select id from table1
>>
>> Can I somehow make it so that if I change table1.id to table1.myId the
>> view will automatically update?
>
> No. In fact if you have a view with SELECT * in, and add/drop/rename a
> column in a table, you need to do sp_refreshview for the view definition
> to change. (Using SELECT * view definitions is not considered best
> practice, by the way.)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

hmm doh.....thanks Erland....I'd imagine this would lead to some very messy
databases over time...I'll have to make sure i get it right first go..

btw if you recall my last saga I found out I can make ssms save change
scripts to a file which is working ok i think.

John Sheppard (nospam@spamspam.com) writes:
> btw if you recall my last saga I found out I can make ssms save change
> scripts to a file which is working ok i think.

Scrutinize those change scripts very closely. That's a very buggy part
of SSMS.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx