May 6, 2003 at 11:27 am
1). How do I drop column(S) for a table but ignored whatsoever constraint created for the table? Here is my script but they don't work. What is wrong with them?
DROP VIEW PSSL_CHANCE_OF_COMMERCIALITY
GO
ALTER TABLE PSSL_T_CHANCE_OF_COMMERCIALITY NOCHECK CONSTRAINT ALL DROP COLUMN (Property_Id, Group_Id, Facility_Id, WELL_UWI, ReserveCat_Id)
GO
EXEC sp_help PSSL_T_CHANCE_OF_COMMERCIALITY
GO
CREATE VIEW PSSL_CHANCE_OF_COMMERCIALITY AS SELECT * FROM PSSL_T_CHANCE_OF_COMMERCIALITY
GO
2). How do I display index(es) information for a sql statement? I wanted to capture the index(es) have been used for a sql statement.
Is anyone could help please?
Thanks,
AC
May 6, 2003 at 11:40 am
quote:
How do I drop column(S) for a table but ignored whatsoever constraint created for the table?
No, you can't. DROP COLUMN is not allowed if the compatibility level is 65 or earlier. A column cannot be dropped if it is:
A replicated column.
Used in an index.
Used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
Associated with a default defined with the DEFAULT keyword, or bound to a default object.
Bound to a rule.
You can check the execution plan of your statement to find out which indexes being used.
May 6, 2003 at 11:51 am
Thanks Allen for your quick response.
1). I am using SQL Server 2000. So I should able to drop the columns in this case, isn't it?
2). I use Query Analyzer's Show Execution Plan option for displaying the index information for a sql statement; however, the returned graphic information does not show which indexes have been used for a sql statement.
May 6, 2003 at 12:04 pm
quote:
I am using SQL Server 2000. So I should able to drop the columns in this case, isn't it?
No, you can't. In SQL Server 6.5, you have to recreate table if a column has to be dropped.
Turn on 'set showplan_text' and exec your query, you will see which indexes are used by SQL Server.
Or after click display estimated execution plan, position the cursor over graphical elements to reveal additional execution plan information.
May 7, 2003 at 8:54 am
I got it, thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply