drop table student cascade

 
 create procedure drop_cascade @tabname varchar(256) 
 as 
 declare @level int; 
 set @level=0; 
  
 declare @temp table (oid int,lvl int); 
 insert into @temp values(object_id(@tabname),@level); 
 while @@rowcount>0 
 begin 
  set @level=@level+1; 
  insert into @temp 
 select fkeyid,@level from sysforeignkeys  
 where rkeyid in (select oid from @temp where lvl=@level-1); 
 end 
  
 declare @sql varchar(8000); 
 set @sql=''; 
 select @sql=@sql+','+quotename(object_schema_name(oid))+'.'+quotename(object_name(oid)) 
 from (select distinct oid,lvl from @temp t  
 where lvl=(select min(lvl) from @temp where oid=t.oid)) t 
 order by lvl desc; 
 set @sql='drop table '+stuff(@sql,1,1,'')+';'; 
  
 select @sql; 
 go 
  
 exec drop_cascade 'dbo.students';