SQL Text Searches

How to search through Stored Procedure for a specific String

Having to find all Stored Procedures that are using a specific piece of ccode that is buggy ?

1
2
3
4
5
6
7
8

  SELECT P.[type] 
      ,P.[name] 
      ,M.[definition] 
  FROM sys.objects P
  JOIN sys.sql_modules M
  ON P.object_id = M.object_id 
  WHERE P.[type] = 'P' 
  and M.[definition] like '%SELECT MAX(DateTimeUpdated)%'

Reference : Microsoft SQL Server Doc sys.sql_modules

(‼️ Caution) How to search through all tables of a Database for a specific value

*Don’t use in a Production environment !! ** This block of code will parse the whole Database and will have an impact on the server.

Sometimes it is usefull to find where a piece of information is stored. This piece of code can help retrieving a string through all the tables of a Database, but it will check all columns, all rows and all tables.

In the sample below, we ae looking for a string “15fb658c-4520-4a66-acff-5d6e0f6eed47”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

DECLARE @SearchString nvarchar(100) 

SET  @SearchString='15fb658c-4520-4a66-acff-5d6e0f6eed47' 

BEGIN 
  CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) 
  SET NOCOUNT ON 
  
  DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), 
  @SearchString2 nvarchar(110)  SET  @TableName = ''    SET @SearchString2 = QUOTENAME('%' + @SearchString + '%','''') 

  WHILE @TableName IS NOT NULL     
  BEGIN        
    SET @ColumnName = ''       
    SET @TableName =  ( 
    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) 
      FROM INFORMATION_SCHEMA.TABLES  
      WHERE TABLE_TYPE = 'BASE TABLE' 
          AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName 
          AND OBJECTPROPERTY( OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0) 

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)       
    BEGIN 
      SET @ColumnName = ( 
        SELECT MIN(QUOTENAME(COLUMN_NAME)) 
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) 
              AND TABLE_NAME = PARSENAME(@TableName, 1) 
              AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') 
              AND QUOTENAME(COLUMN_NAME) > @ColumnName) 

      IF @ColumnName IS NOT NULL             
      BEGIN 
        INSERT INTO #Results 
        EXEC 
        ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName +  ', 3630) FROM ' + @TableName 
        + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + SearchString)              
      END        
    END      
  END 

  SELECT ColumnName, ColumnValue FROM #Results 
END

Initially posted on DBA Services

How to find the biggest tables

1
2
3
4
5
6
7
8
9

SELECT TOP 10 schema_name(TAB.schema_id) + '.' + TAB.name AS 'Table',  
      CAST(SUM(SIZE.used_pages * 8)/1024.00 AS NUMERIC(36, 2)) AS 'Used_Mb', 
      CAST(SUM(SIZE.total_pages * 8)/1024.00 AS NUMERIC(36, 2)) AS 'Allocated_Mb' 
  FROM sys.tables TAB
  JOIN sys.indexes IND ON TAB.object_id = IND.object_id 
  JOIN sys.partitions PART ON IND.object_id = PART.object_id AND IND.index_id = PART.index_id 
  JOIN sys.allocation_units SIZE ON PART.partition_id = SIZE.container_id 
  GROUP BY schema_name(TAB.schema_id) + '.' + TAB.name 
  ORDER BY sum(SIZE.used_pages) DESC;

Initially posted on Microsoft Q&A