This is from an old version of this site. SQL snippets I've found useful (specific for MSSQL).

Use at your own risk: caveat emptor.

Search all Stored Procedures for text

SELECT DISTINCT
    o.name AS ObjectName,
    o.type_desc AS TypeDescription
FROM 
    sys.sql_modules m
    INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE 
    m.definition LIKE '%SearchText%';

Show Fragmentation Size

I think I got this off SQL Authority ages ago. Not sure.

DECLARE @FragSize INT = 10
DECLARE @DBID INT = DB_ID()

SELECT
	B.[Name] AS TableName,
	C.[Name] AS IndexName,
	C.fill_factor AS IndexFillFactor,
	D.[Rows] AS RowsCount,
	A.avg_fragmentation_in_percent AS AverageFragmentation,
	A.page_count AS PageCount
FROM 
	sys.dm_db_index_physical_stats(@DBID, NULL, NULL, NULL, NULL) A
	INNER JOIN sys.objects B ON A.object_id = B.object_id
	INNER JOIN sys.indexes C ON B.object_id = C.object_id AND A.index_id = C.index_id
	INNER JOIN sys.partitions D ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE 
	C.index_id > 0
	AND A.avg_fragmentation_in_percent > @FragSize 
ORDER BY 
	D.rows DESC, A.avg_fragmentation_in_percent 

Check status of Full Text Index Population

SELECT
    cat.name,
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount],
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus') AS [MergeStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulateCompletionAge],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus') AS [PopulateStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus') AS [ImportStatus]
FROM 
    sys.fulltext_catalogs AS cat
#List all Triggers and their Tables
SELECT
    sysobjects.name AS TriggerName,
    OBJECT_NAME(parent_obj) AS TableName 
FROM sysobjects 
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid 
INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id 
WHERE 
    sysobjects.type = 'TR' 
ORDER BY 
    sysobjects.name

UPDATE STATISTICS without sp_updatestats

Uses a cursor. Don't use cursors.

DECLARE updatestats CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN updatestats

DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT N'UPDATING STATISTICS ' + @tablename
    SET @Statement = 'UPDATE STATISTICS '  + @tablename + '  WITH FULLSCAN'
    EXEC sp_executesql @Statement
    FETCH NEXT FROM updatestats INTO @tablename?
END