how to delete the table which has reletions to other tables and chain goes on..by input table name

[ajaykumardev]

how to delete the table which has reletions to other tables and chain goes on..by input table name



i want this for only one table .....dont give for all tables....

 

[bborissov]

Delete all dependencies first, then table.

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

You need to drop the foreign key constraints first.

You can use this code to do it:

Declare @TableName VarChar(100)
Set @TableName = 'YourTableNameHere'

Declare @SQL VarChar(max)

-- Drop Foreign Key constraints
Set @SQL = 'SET NOCOUNT ON; SET XACT_ABORT ON;Begin Transaction' + Char(13)

Select @SQL = @SQL + 
                     'Alter Table [' + pk.table_schema + '].[' + PK.Table_Name + '] '         
                     + 'Drop Constraint [' + PK.Constraint_Name + ']; ' + char(13)
From   Information_Schema.Table_Constraints pk 
Where  Constraint_Type = 'FOREIGN KEY'
       And PK.Table_Name = @TableName
       And PK.Constraint_Name Is Not NULL

Set @SQL = @SQL + 'Commit Transaction'

Print @SQL
--Exec (@SQL)

The code above will print the drop commands to the messages window. Once you are satisfied with the results, you can comment the print line at the end and uncomment the exec line.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ajaykumardev]

Thanks for reply ....
but i want to delete all the related tables with the master table to enter the valid to master and transaction tables