Finding Something Somewhere in a Database

I threw this ugly query together (at the bottom of the message) a few years ago (hence the ancient system table names) and recently stumbled across it in an old email I sent to myself. I figured I would post it here, since it’s a handy way to find a string in a database when you have no idea where to begin looking.

I wrote it to find a string within a database I was unfamiliar with. You provide a search term to the script -- @searchString -- such as ‘%acre%’ (use the standard LIKE wildcards) in the example below, and it searches all of the char, varchar, nchar, and nvarchar columns in all of the user tables for your term. For my initial needs, all of the tables were in the dbo schema, so the script doesn’t handle table owner/schema. You can tweak it to your needs on that front.

The result set gives you your initial search term, how many instances where found in which table, and the query used to generate the count for that table so you have a good starting place for further analysis.

DECLARE @searchString nvarchar(100)

SET @searchString = '%acre%'

SET NOCOUNT ON

CREATE TABLE ##search_results (result_table sysname, search_term NVARCHAR(100), result_count bigint)

CREATE TABLE ##search_queries (result_table sysname, query_text NVARCHAR(MAX))

DECLARE @tbl sysname

set @tbl = (select top 1 so.name FROM sysobjects as so inner join syscolumns as sc on so.id = sc.id inner join systypes as st on sc.xtype = st.xtype

where so.type = 'u'

and st.name in ('char', 'varchar', 'nchar', 'nvarchar')

order by so.name desc )

declare @sql NVARCHAR(max), @where nvarchar(max)

while @tbl is not null

BEGIN

set @where = ''

SELECT @where = @where + ' OR ' + quotename(sc.name) + ' LIKE ''' + @searchString + '''' FROM sysobjects as so inner join syscolumns as sc on so.id = sc.id inner join systypes as st on sc.xtype = st.xtype

where so.type = 'u'

and st.name in ('char', 'varchar', 'nchar', 'nvarchar')

and so.name = @tbl

-- get rid of the initial ' OR '

SET @where = substring(@where, 4, len(@where))

SET @sql = 'SELECT ' + quotename(@tbl, '''') + ' AS resultsTable, ' + quotename(@searchString, '''') + ' AS searchTerm, count(*) AS resultsCount FROM ' + quotename(@tbl) + ' WHERE '

INSERT ##search_queries (result_table, query_text) VALUES (@tbl, @sql + @where)

SET @sql = 'INSERT ##search_results ' + @sql + @where

EXEC sp_executesql @statement = @sql

set @tbl = (select top 1 so.name FROM sysobjects as so inner join syscolumns as sc on so.id = sc.id inner join systypes as st on sc.xtype = st.xtype

where so.type = 'u'

and st.name in ('char', 'varchar', 'nchar', 'nvarchar')

AND so.name < @tbl

order by so.name desc)

END

SELECT sr.search_term, sr.result_count, sr.result_table, sq.query_text FROM ##search_results AS sr JOIN ##search_queries AS sq ON sr.result_table = sq.result_table ORDER BY sr.result_count DESC, sr.result_table DESC

DROP TABLE ##search_results

DROP TABLE ##search_queries

Searching Cached Query Plans

A quick way to search cached query plans (for missing indexes, table scans, etc.)

--exec dbo.dba_SearchCachedPlans '%MissingIndexes%'

--2. exec dbo.dba_SearchCachedPlans '%ColumnsWithNoStatistics%'

--3. exec dbo.dba_SearchCachedPlans '%TableScan%'

--4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'

DECLARE @StringToSearchFor VARCHAR(100)

SET @StringToSearchFor = '%%'

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 20

st.text AS [SQL]

, cp.cacheobjtype

, cp.objtype

, DB_NAME(st.dbid)AS [DatabaseName]

, cp.usecounts AS [Plan usage]

, qp.query_plan

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor

ORDER BY cp.usecounts DESC

Found on http://www.sqlservercentral.com/articles/Performance/66729/

Full Recovery or Stealth Simple

This query lists all databases on an instance that are using the FULL recovery model but do not have a full or differential backup to anchor the log chain – that way you don’t have to inspect each database on the server to find the offender.

SELECT

db.name,

recovery_model_desc,

last_log_backup_lsn

FROM

sys.databases AS db

INNER JOIN sys.database_recovery_status AS dbrs

ON db.database_id = dbrs.database_id

WHERE

db.recovery_model = 1

AND dbrs.last_log_backup_lsn IS NULL

You can test it out by placing a fully backed database into SIMPLE recovery then placing it back into FULL recovery. The query will now return the modified database. Backup the database and the query will now return an empty set.

This query is based on the concepts in an article by Paul Randal, in case you want to do some more digging: http://www.sqlskills.com/BLOGS/PAUL/post/New-script-is-that-database-REALLY-in-the-FULL-recovery-mode.aspx