List & Drop all Procedures and Tables in a Database

I) List all Stored procedures in a database:

Select * from sys.procedures where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

OR

Select * from sys.objects where type='p' and is_ms_shipped=0 and [name] not like 'sp[_]%diagram%'

Please note that I have added the 'NOT LIKE' part to get rid of stored procedures created during database installation.

II) Delete all [user created] Stored procedures in a database:

Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

This would list down all the user defined Stored procedure as 'Drop Procedure procedureName' in the result pane. Just copy-paste it into the query window and execute it once.


Drop all tables:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"