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
This was removed by the editor as SPAM
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply