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 ?"

Drop Procedure- Script

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Pro_Name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Pro_Name]

Drop Table -Script

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TABLE_NAME]') AND type in (N'U'))
DROP TABLE [TABLE_NAME]

SMTP mailing Attachment from Database

obj.gettable("select filename,fileExtn,fileContent from Files where fileId=@fileId");
string filename = obj.dt.Rows[0][0].ToString();
string ext = obj.dt.Rows[0][1].ToString();
byte[] bytFile = (byte[])obj.dt.Rows[0][2];
MemoryStream stream = new MemoryStream(bytFile);
Attachment att = new Attachment(stream, filename.ToString(), "application/msword");