|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Propagating results of Alter Table to its viewsEnterprise Manager or with T-SQL statements in Query Analyzer, the alterations are not automatically noticed by the views that use the table. The only thing I have been able to figure out to do is to find each potentially affected view and then: 1. Open it in design mode 2. Check or uncheck a field in the Altered table 3. Uncheck or check the same field to restore the view to its original field selection 4. Close the view and Save it. Two problems: 1. That's tedious and time-consuming 2. It is easy to miss an affected view. Can someone advise me as to a better way? (I am mostly using SQL Server 2000) -- Thanks, Doug MacLean Doug MacLean wrote:
Show quote > When I alter a table to add columns or change their properties, either in Firstly, do not use SELECT * in views. It's generally a bad idea to use> Enterprise Manager or with T-SQL statements in Query Analyzer, the > alterations are not automatically noticed by the views that use the table. > > The only thing I have been able to figure out to do is to find each > potentially affected view and then: > 1. Open it in design mode > 2. Check or uncheck a field in the Altered table > 3. Uncheck or check the same field to restore the view to its original field > selection > 4. Close the view and Save it. > > Two problems: > 1. That's tedious and time-consuming > 2. It is easy to miss an affected view. > > Can someone advise me as to a better way? (I am mostly using SQL Server 2000) > > -- > Thanks, > > Doug MacLean SELECT * anywhere in production code. It's a particularly bad idea in views because of the way views handle changes to the columns. So assuming you have named columns in all your views, use sp_refreshview to ensure the view is up to date with changes to its columns. If you want to add a column you'll have to edit the view definition of course, which is good practice and not difficult if you have adequate source control and change control procedures. Table changes should never be executed by Enterprise Manager in a production environment. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- David,
Thanks. That's very helpful. It eliminates the need for my 4-step process in Enterprise Manager. I wonder if there is a tool to list all of the views that depend on a specified table, because the other challenge is actually finding all of them when altering a table. Best Regards, -- Show quoteDoug MacLean "David Portas" wrote: > Doug MacLean wrote: > > When I alter a table to add columns or change their properties, either in > > Enterprise Manager or with T-SQL statements in Query Analyzer, the > > alterations are not automatically noticed by the views that use the table. > > > > The only thing I have been able to figure out to do is to find each > > potentially affected view and then: > > 1. Open it in design mode > > 2. Check or uncheck a field in the Altered table > > 3. Uncheck or check the same field to restore the view to its original field > > selection > > 4. Close the view and Save it. > > > > Two problems: > > 1. That's tedious and time-consuming > > 2. It is easy to miss an affected view. > > > > Can someone advise me as to a better way? (I am mostly using SQL Server 2000) > > > > -- > > Thanks, > > > > Doug MacLean > > Firstly, do not use SELECT * in views. It's generally a bad idea to use > SELECT * anywhere in production code. It's a particularly bad idea in > views because of the way views handle changes to the columns. > > So assuming you have named columns in all your views, use > sp_refreshview to ensure the view is up to date with changes to its > columns. If you want to add a column you'll have to edit the view > definition of course, which is good practice and not difficult if you > have adequate source control and change control procedures. Table > changes should never be executed by Enterprise Manager in a production > environment. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > > Greetings,
Show quote "Doug MacLean" <DougMacL***@discussions.microsoft.com> wrote in message Create the views WITH SCHEMABINDING and you won't be able to change the news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@microsoft.com... > David, > > Thanks. That's very helpful. It eliminates the need for my 4-step process > in > Enterprise Manager. > > I wonder if there is a tool to list all of the views that depend on a > specified table, because the other challenge is actually finding all of > them > when altering a table. > > Best Regards, > -- > Doug MacLean table without first dropping the view. This is a good reminder to fix the view ;-) -- Regards, Neale NOON Thanks, Neale.
That's an interesting idea to know when I change a table that has one or more dependent views. So it is a good reminder. But I KNOW there are views. And what I want is a clean way of finding and refreshing them. Adding a process of dropping (and then re-adding) them seems tedious. Thanks Best Regards, -- Show quoteDoug MacLean "Neale NOON" wrote: > Greetings, > > "Doug MacLean" <DougMacL***@discussions.microsoft.com> wrote in message > news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@microsoft.com... > > David, > > > > Thanks. That's very helpful. It eliminates the need for my 4-step process > > in > > Enterprise Manager. > > > > I wonder if there is a tool to list all of the views that depend on a > > specified table, because the other challenge is actually finding all of > > them > > when altering a table. > > > > Best Regards, > > -- > > Doug MacLean > > > Create the views WITH SCHEMABINDING and you won't be able to change the > table without first dropping the view. This is a good reminder to fix the > view ;-) > > -- > Regards, > Neale NOON > > > You may want to use this.
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE where table_name = '<table_name>' > I wonder if there is a tool to list all of the views that depend on a You might find it easier to refresh all views since you cannot rely on > specified table, because the other challenge is actually finding all of > them > when altering a table. dependency information unless the views were created WITH SCHEMABINDING. The script below will generate a script to refresh all views in the current database. You can wrap it in a cursor and execute according to your preference. BTW, even without 'SELECT *', you can run into issues with changed datatypes in the referenced tables. SELECT 'EXEC sp_refreshview ''' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) + '''' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' AND OBJECTPROPERTY(OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped') = 0 -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Doug MacLean" <DougMacL***@discussions.microsoft.com> wrote in message news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@microsoft.com... > David, > > Thanks. That's very helpful. It eliminates the need for my 4-step process > in > Enterprise Manager. > > I wonder if there is a tool to list all of the views that depend on a > specified table, because the other challenge is actually finding all of > them > when altering a table. > > Best Regards, > -- > Doug MacLean > > > "David Portas" wrote: > >> Doug MacLean wrote: >> > When I alter a table to add columns or change their properties, either >> > in >> > Enterprise Manager or with T-SQL statements in Query Analyzer, the >> > alterations are not automatically noticed by the views that use the >> > table. >> > >> > The only thing I have been able to figure out to do is to find each >> > potentially affected view and then: >> > 1. Open it in design mode >> > 2. Check or uncheck a field in the Altered table >> > 3. Uncheck or check the same field to restore the view to its original >> > field >> > selection >> > 4. Close the view and Save it. >> > >> > Two problems: >> > 1. That's tedious and time-consuming >> > 2. It is easy to miss an affected view. >> > >> > Can someone advise me as to a better way? (I am mostly using SQL Server >> > 2000) >> > >> > -- >> > Thanks, >> > >> > Doug MacLean >> >> Firstly, do not use SELECT * in views. It's generally a bad idea to use >> SELECT * anywhere in production code. It's a particularly bad idea in >> views because of the way views handle changes to the columns. >> >> So assuming you have named columns in all your views, use >> sp_refreshview to ensure the view is up to date with changes to its >> columns. If you want to add a column you'll have to edit the view >> definition of course, which is good practice and not difficult if you >> have adequate source control and change control procedures. Table >> changes should never be executed by Enterprise Manager in a production >> environment. >> >> -- >> David Portas, SQL Server MVP >> >> Whenever possible please post enough code to reproduce your problem. >> Including CREATE TABLE and INSERT statements usually helps. >> State what version of SQL Server you are using and specify the content >> of any error messages. >> >> SQL Server Books Online: >> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx >> -- >> >> Dan,
Ahhh!. That's a great idea. Thanks for both the idea and the sample code. And it's practical because I only do such updates on the production system at times when no one is using it. You're right. I've already seen the problem that -- even without adding columns -- the view needs to be refreshed. Thanks much and Best regards, -- Show quoteDoug MacLean "Dan Guzman" wrote: > > I wonder if there is a tool to list all of the views that depend on a > > specified table, because the other challenge is actually finding all of > > them > > when altering a table. > > You might find it easier to refresh all views since you cannot rely on > dependency information unless the views were created WITH SCHEMABINDING. > The script below will generate a script to refresh all views in the current > database. You can wrap it in a cursor and execute according to your > preference. BTW, even without 'SELECT *', you can run into issues with > changed datatypes in the referenced tables. > > SELECT > 'EXEC sp_refreshview ''' + > QUOTENAME(TABLE_SCHEMA) + > N'.' + > QUOTENAME(TABLE_NAME) + > '''' > FROM INFORMATION_SCHEMA.TABLES > WHERE TABLE_TYPE = 'VIEW' > AND OBJECTPROPERTY(OBJECT_ID( > QUOTENAME(TABLE_SCHEMA) + > N'.' + > QUOTENAME(TABLE_NAME) > ), 'IsMSShipped') = 0 > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Doug MacLean" <DougMacL***@discussions.microsoft.com> wrote in message > news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@microsoft.com... > > David, > > > > Thanks. That's very helpful. It eliminates the need for my 4-step process > > in > > Enterprise Manager. > > > > I wonder if there is a tool to list all of the views that depend on a > > specified table, because the other challenge is actually finding all of > > them > > when altering a table. > > > > Best Regards, > > -- > > Doug MacLean > > > > > > "David Portas" wrote: > > > >> Doug MacLean wrote: > >> > When I alter a table to add columns or change their properties, either > >> > in > >> > Enterprise Manager or with T-SQL statements in Query Analyzer, the > >> > alterations are not automatically noticed by the views that use the > >> > table. > >> > > >> > The only thing I have been able to figure out to do is to find each > >> > potentially affected view and then: > >> > 1. Open it in design mode > >> > 2. Check or uncheck a field in the Altered table > >> > 3. Uncheck or check the same field to restore the view to its original > >> > field > >> > selection > >> > 4. Close the view and Save it. > >> > > >> > Two problems: > >> > 1. That's tedious and time-consuming > >> > 2. It is easy to miss an affected view. > >> > > >> > Can someone advise me as to a better way? (I am mostly using SQL Server > >> > 2000) > >> > > >> > -- > >> > Thanks, > >> > > >> > Doug MacLean > >> > >> Firstly, do not use SELECT * in views. It's generally a bad idea to use > >> SELECT * anywhere in production code. It's a particularly bad idea in > >> views because of the way views handle changes to the columns. > >> > >> So assuming you have named columns in all your views, use > >> sp_refreshview to ensure the view is up to date with changes to its > >> columns. If you want to add a column you'll have to edit the view > >> definition of course, which is good practice and not difficult if you > >> have adequate source control and change control procedures. Table > >> changes should never be executed by Enterprise Manager in a production > >> environment. > >> > >> -- > >> David Portas, SQL Server MVP > >> > >> Whenever possible please post enough code to reproduce your problem. > >> Including CREATE TABLE and INSERT statements usually helps. > >> State what version of SQL Server you are using and specify the content > >> of any error messages. > >> > >> SQL Server Books Online: > >> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > >> -- > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics