How to Truncate table if the table refered by the view

Hi,
I got a error on my stored procedure " cannot tuncate a table if the table is refered by the view" how do I over come this?

Thanks,
Manjula
ken hanseAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
pssandhuConnect With a Mentor Commented:
I think you will have to drop the view before you can tuncate. Here is what I suggest -
1. Generate the CREATE VIEW script before you can drop the view. You can get the original script for all the views crated on a table from the information schema:
Select View_Definition
from INFORMATION_SCHEMA.VIEWS
where Table_Name = <TableName>

2. Then drop the view, truncate the table and run the scripts you got by running the above query.
Hope this helps.
P.
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
you have to use  the 'DELETE ' statement instead of truncate or else drop the view and recreate it after the TRUNCATE statement
0
 
Eugene ZConnect With a Mentor Commented:
" cannot tuncate a table if the table is refered by the view"?
are you sure it is not "table referenced by a FOREIGN KEY constraint"?
or
you try to delete table instead of truncate?

can you please post the part of the code where from you got error and copy\paste error too?
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Hope you use an indexed view instead of a normal view.

You cannot use TRUNCATE TABLE on tables that:

    * Are referenced by a FOREIGN KEY constraint.
    * Participate in an indexed view.
    * Are published by using transactional replication or merge replication.

Hence you have to either DELETE or drop and recreate the Indexed view in order to use your TRUNCATE statement.

Hope this helps.
0
All Courses

From novice to tech pro — start learning today.