May 24, 2017 at 12:14 pm
Years ago, I found a stored procedure that would allow me to enter a string value and it would display all occurrences of that value in the database. Can anybody give me a link to that stored procedure?
Thanks in advance!
May 24, 2017 at 12:45 pm
I can't imagine it would be efficient, but you could look through the INFORMATION_SCHEMA views to do this, if you have to recreate it. that in combination with SP_MSFOREACHTABLE might make that happen http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx
You could probably make it smarter by only looking at user objects and ignoring columns where the datatype can't possibly match.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 24, 2017 at 1:26 pm
if you're trying to search through stored procedures, functions, views, triggers, constraints, etc code for a string, you could use sys.sql_modules like:
DECLARE @SearchString NVARCHAR(80) = N'%ClaimPayment%'
SELECT s.name + N'.' + o.name AS object_name, o.type_desc, m.definition
FROM sys.objects o
INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE m.definition like @SearchString
ORDER BY s.name, o.type_desc, o.name;
May 24, 2017 at 2:20 pm
yeah, or sys.syscomments. Chris, any idea what the difference is between sql_modules and syscomments? I'm trying to read BOL to understand, but to me it looks like it would be in either place
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 24, 2017 at 2:40 pm
I believe sys.syscomments is one of the old-style system views based on the SQL Server 2000 metadata. They've been depreciated for years and years, but they are still there in 2016. (I haven't had a chance to play with 2017, still doing 2016 upgrades where I work)
May 25, 2017 at 2:06 am
I think you're referring to this sp:
USE master;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[Search] @Search VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON;
IF ( OBJECT_ID('tempdb..##AllDBProcText') IS NOT NULL )
DROP TABLE ##AllDBProcText;
CREATE TABLE ##AllDBProcText
(
DBName VARCHAR(50) ,
ProcName VARCHAR(150) ,
SqlText NVARCHAR(MAX)
);
DECLARE databases CURSOR
FOR
SELECT name
FROM sys.databases
WHERE database_id >= 5
AND state_desc != 'OFFLINE';
OPEN databases;
DECLARE @db NVARCHAR(128);
DECLARE @query NVARCHAR(2000);
FETCH NEXT FROM databases INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = '
INSERT INTO ##AllDBProcText
SELECT DISTINCT ''' + @db + ''',o.name AS Object_Name,m.definition
FROM ' + @db + '.sys.sql_modules m
INNER JOIN ' + @db + '.sys.objects o ON m.object_id=o.object_id
ORDER BY 2,1';
--print @query
EXEC sp_executesql @query;
FETCH NEXT FROM databases INTO @db;
END;
CLOSE databases;
DEALLOCATE databases;
SELECT * ,
LEN(SqlText) lengthtext ,
SUBSTRING(SqlText, CHARINDEX(@Search, SqlText) - 60,
LEN(@Search) + 100)
FROM ##AllDBProcText
WHERE SqlText LIKE '%' + @Search + '%'
ORDER BY lengthtext DESC;
SELECT b.name AS Job_Name ,
step_id ,
step_name ,
database_name ,
a.command AS Command ,
last_run_date ,
last_run_time ,
last_run_duration ,
date_created ,
date_modified
FROM msdb..sysjobsteps a
JOIN msdb..sysjobs b ON a.job_id = b.job_id
WHERE Command LIKE '%' + @Search + '%'
ORDER BY last_run_date DESC ,
last_run_time DESC;
END;
GO
May 25, 2017 at 4:17 am
Chris Harshman - Wednesday, May 24, 2017 2:40 PMI believe sys.syscomments is one of the old-style system views based on the SQL Server 2000 metadata. They've been depreciated for years and years, but they are still there in 2016. (I haven't had a chance to play with 2017, still doing 2016 upgrades where I work)
Yes, and crucially, syscomments splits definitions up into 4000-character (I think) chunks. So if the text you're searching for happens to be on one of the breaks, you won't find it. That's the practical reason why you should use sql_modules.
John
May 25, 2017 at 12:10 pm
check out some options online: https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58 or https://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database, I ended up having to do this very thing today and was working on rolling my own, but these seem to handle most general cases and errors you might run into
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 25, 2017 at 12:38 pm
If you are looking to search objects - and not values in columns - then I would recommend downloading and installing SQL Search from Redgate. Does exactly what you want and is free...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply