SQL Snippets

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%';
1
2
3
4
5
6
7
8

Show Fragmentation Size

I think I grabbed this off SQL Authority years 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 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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
1
2
3
4
5
6
7
8
9

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
1
2
3
4
5
6
7
8
9
10

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Copyright © 2018 Andy Belfield. All rights reserved. Some portions may be CC-BY-NC where noted. Code examples on this web site are for educational purposes only. Don't use them in a production environment. Seriously.