Drop All Tables in SQL Server Database and store procedure

Following script delete all table from databse except with Foreighn Key table

 select name into #tables from sys.objects where type = 'U'
while (select count(1) from #tables) > 0
begin
declare @sql varchar(max)
declare @tbl varchar(255)
select top 1 @tbl = name from #tables
set @sql = 'drop proc ' + @tbl
exec(@sql)
delete from #tables where name = @tbl
end
drop proc #tables;

To Drop All Store Procedure :



 select name into #tables from sys.objects where type = 'p'
while (select count(1) from #tables) > 0
begin
declare @sql varchar(max)
declare @tbl varchar(255)
select top 1 @tbl = name from #tables
set @sql = 'drop proc ' + @tbl
exec(@sql)
delete from #tables where name = @tbl
end
drop proc #tables;

Tables Without Clustered Indexes

--Doesn't work on 2000 databases or databases in 2000 compatability mode.  Need to change the db_id() syntax if so.

select 
 [Database Name] = db_name()
, [Table Name] = s.name + '.' + o.name
, p.row_count
, SizeMb= (p.reserved_page_count*8.)/1024.
from 
 sys.objects o
inner join  sys.schemas s on o.schema_id = s.schema_id
inner join  sys.dm_db_partition_stats p  on p.object_id = o.object_id 
inner join  sys.indexes si on si.object_id = o.object_ID
WHERE si.type_desc = 'Heap'
and  is_ms_shipped = 0
order by SizeMb desc