Have an application with 100′s of stored procs and need to find one?  We use this code below that searchs all the stored proc’s syntax in a SQL database.

Create PROCEDURE [dbo].[Find_Text_In_SP]
@StringToSearch varchar(100)
AS
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name

Below is a script that I found that makes it very easy to set execute permissions to all all stored procs in a SQL Server 2005/2008 DB.

-- Set Username
DECLARE @U  sysname ; set @U = QUOTENAME('DB_USERNAME_HERE')
-- Set DB
DECLARE @DB  sysname ; set @DB = 'DB_NAME'
-- SET Schema
DECLARE @schema  sysname ; set @schema = 'SCHEMA_NAME'


DECLARE @ID           integer,
@LAST_ID     integer,
@NAME        varchar(1000),
@SQL         varchar(4000)

SET @LAST_ID = 0

WHILE @LAST_ID IS NOT NULL
BEGIN
SELECT @ID = MIN(id)
FROM dbo.sysobjects
WHERE id > @LAST_ID  AND type = 'P' AND category = 0

SET @LAST_ID = @ID

-- We have a record so go get the name
IF @ID IS NOT NULL
BEGIN
SELECT @NAME = name
FROM dbo.sysobjects
WHERE id = @ID

-- Build the DCL to do the GRANT
SET @SQL = 'GRANT EXECUTE ON ' +@DB + '.' + @schema +'.' + @NAME + ' TO ' + @U

PRINT 'setting execute permissions for: '+  @SQL
-- Run the SQL Statement you just generated
EXECUTE(@SQL)

END


END

Here is a handy little script I found that dumps out all the tables in a SQL DB, their sizes and how many records.  Handy for debugging why your DB has gotten so damn big.

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp