How to get list of all stored procedures from SQL database

If you want to get list of all stored procedures from the database you are working on, just fire this query in one of the query window and all procedure names will be returned.

SELECT DISTINCT object_name(id) from sys.syscomments

You can further customize the query and filter stored procedures based on its name like the following query will return storec procedures that has 'add' in its name:

SELECT DISTINCT object_name(id) from sys.syscomments WHERE TEXT like '%add%'

How to truncate log file in SQL Server

I was once dealing with a simple sql server project which had a database of around 25 MB but its log file was 100+ MB and I had to download it from the production server to development machine for testing.

This is one of the situations when we should truncate the log file. Here's a code snippet which I have used with SQL Server 2005 and which works.

Say you have a database called ABCD and its files are ABCD.mdf and ABCD_log.ldf

Now log into sql server 2005, open a new query window in ABCD database, paste and run following commands

DBCC SHRINKFILE('ABCD_log', 1)
BACKUP LOG ABCD WITH TRUNCATE_ONLY
DBCC SHRINKFILE('ABCD_log', 1)
GO

Make sure to put in the name according to your database.
Also note the number '1' means log will be truncated to 1 MB.