|
Alter the structure or column definitions of a table by adding columns, changing various column attributes, or deleting columns.
Before altering a table, determine whether there are views dependent on a table by using the sa_dependent_views system procedure.
If you are altering the schema of a table with dependent views, there may be additional steps to take depending upon the type of view:
When you alter the schema of a table, the definition for the table in the database is updated. If there are dependent regular views, the database server automatically recompiles them after you perform the table alteration. If the database server cannot recompile a dependent regular view after making a schema change to a table, it is likely because the change you made invalidated the view definition. In this case, you must correct the view definition.
If there are dependent materialized views, you must disable them before making the table alteration, and then re-enable them after making the table alteration. If you cannot re-enable a dependent materialized view after making a schema change to a table, it is likely because the change you made invalidated the materialized view definition. In this case, you must drop the materialized view and then create it again with a valid definition, or make suitable alterations to the underlying table before trying to re-enable the materialized view.
Change the owner of a table using the ALTER TABLE statement or SQL Central. When changing the table owner, specify whether to preserve existing foreign keys within the table, as well as those referring to the table. Dropping all foreign keys isolates the table, but provides increased security if needed. You can also specify whether to preserve existing explicitly granted privileges. For security purposes, drop all explicitly granted privileges that allow a user access to the table. Implicitly granted privileges given to the owner of the table are given to the new owner and dropped from the old owner.