This Query can search any string value (table name, table data etc) from all the tables/views of any SQL Server database, when you will place any string at "your text here" it will search your given string from all the tables/views exist in that 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 given string in the procedures/functions/triggers as well is there any query exist in SQL Server 2005. As this query does not search in these areas.
search given string in the procedures/functions/triggers
This is actually far easier.
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 modify 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'd just run them separately one after the other as separate statements.
Check out the view sys.sql_modules.
Or, in pre-2005 versions of SQL Server, syscomments
This is also a helpful query to find something like proc etc from msDB means this will find table/function/proc ref in SQL Server Agent's 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 query above in accepted answer
SELECT OBJECT_NAME(object_id), definition
FROM sys.sql_modules
WHERE definition LIKE '%'+@SearchStr+'%'
only work to find the given text in procedures/functions/triggers from selected DB.
Below query works to check the job status of SQL Server Agent Jobs:
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
Query to find the constraints details of a table in DB.
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 check the SQL Job Agent running job status:
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 this can be helpful for someone searching some text in DB.
Here is the query to find definition of SP/view/trigger etc in sql server.
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'Object Name Here');
