How to search a string in SQL Server databases

Question

This query can search for any string value (table name, table data, etc.) from all tables / views of any SQL Server database, when you put any row in the text here . "Will search for your row from all tables / views in this database

DECLARE @SearchStr nvarchar(100) SET @SearchStr = 'your text here' DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') 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 ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM @Results 

But I like to search for a given line in procedures/functions/triggers , and also whether there is any query in SQL Server 2005. Since this query is not searched in these areas.

Answer 1

search for a given line in procedures / functions / triggers

It is much simpler.

 SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE definition LIKE '%'+@SearchStr+'%' 

One way to use it is to add it to the end of your TSQL code, i.e. change the last SELECT:

 SELECT ColumnName, ColumnValue FROM @Results UNION ALL SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE definition LIKE '%'+@SearchStr+'%' 

Personally, I just run them separately one after another as separate statements.

Answer 2

Check out the sys.sql_modules view .

Or, in versions of SQL Server before 2005, syscomments

Answer 3

This is also a useful query to search for something like proc, etc. from msDB means that it will find the table / function / proc ref in SQL Agent Agent Jobs.

 USE [msdb] GO SELECT j.job_id, s.srvname, j.name, js.step_id, js.command, j.enabled FROM dbo.sysjobs j JOIN dbo.sysjobsteps js ON js.job_id = j.job_id JOIN master.dbo.sysservers s ON s.srvid = j.originating_server_id WHERE js.command LIKE N'%YOUR TEXT HERE%' 

Where is the request above in the accepted answer

 SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE definition LIKE '%'+@SearchStr+'%' 

It works only for searching for the specified text in procedures / functions / triggers from the selected database.

The following is a query to check the status of a SQL Server Agent job job:

 SELECT sj.name , sja.* FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id SELECT TOP 10 * FROM msdb.dbo.sysjobhistory 

A query to find information about table restrictions in the database.

 SELECT FK_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE PT.table_name = 'Table Name' 

To test the working SQL Job Agent:

 use msdb go SELECT j.name JobName , STUFF(STUFF(h.run_date,5,0,'-'),8,0,'-') as Date , CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6),5, 0, ':'), 3, 0, ':') AS DATETIME) AS StartDate , STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(h.run_duration as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') AS DURATION , CASE h.run_status WHEN 0 THEN 'failed' WHEN 1 THEN 'Succeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' END AS ExecutionStatus FROM sysjobhistory h INNER JOIN sysjobs j ON j.job_id = h.job_id WHERE STEP_ID = 0 AND CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6),5, 0, ':'), 3, 0, ':') AS DATETIME)>=getdate()-90 AND (j.name Like 'DW%' ) ORDER BY j.name, STUFF(STUFF(h.run_date,5,0,'-'),8,0,'-') 

So it can be useful for those who are looking for some text in the database.