How to find a text inside SQL Server procedures / triggers? - Printable Version +- 0Day Forums (https://zeroday.vip) +-- Forum: Coding (https://zeroday.vip/Forum-Coding) +--- Forum: Database (https://zeroday.vip/Forum-Database) +---- Forum: Microsoft SQL Server (https://zeroday.vip/Forum-Microsoft-SQL-Server) +---- Thread: How to find a text inside SQL Server procedures / triggers? (/Thread-How-to-find-a-text-inside-SQL-Server-procedures-triggers) Pages:
1
2
|
How to find a text inside SQL Server procedures / triggers? - hardboiled574 - 07-31-2023 I have a linkedserver that will change. Some procedures call the linked server like this: `[10.10.100.50].dbo.SPROCEDURE_EXAMPLE`. We have triggers also doing this kind of work. We need to find all places that uses `[10.10.100.50]` to change it. In SQL Server Management Studio Express, I didn't find a feature like "find in whole database" in Visual Studio. Can a special sys-select help me find what I need? RE: How to find a text inside SQL Server procedures / triggers? - daffs939767 - 07-31-2023 I use this one for work. leave off the []'s though in the @TEXT field, seems to want to return everything... <pre> SET NOCOUNT ON DECLARE @TEXT VARCHAR(250) DECLARE @SQL VARCHAR(250) SELECT @TEXT='10.10.100.50' CREATE TABLE #results (db VARCHAR(64), objectname VARCHAR(100),xtype VARCHAR(10), definition TEXT) SELECT @TEXT as 'Search String' DECLARE #databases CURSOR FOR SELECT NAME FROM master..sysdatabases where dbid>4 DECLARE @c_dbname varchar(64) OPEN #databases FETCH #databases INTO @c_dbname WHILE @@FETCH_STATUS <> -1 BEGIN SELECT @SQL = 'INSERT INTO #results ' SELECT @SQL = @SQL + 'SELECT ''' + @c_dbname + ''' AS db, o.name,o.xtype,m.definition ' SELECT @SQL = @SQL + ' FROM '+@c_dbname+'.sys.sql_modules m ' SELECT @SQL = @SQL + ' INNER JOIN '+@c_dbname+'..sysobjects o ON m.object_id=o.id' SELECT @SQL = @SQL + ' WHERE [definition] LIKE ''%'+@TEXT+'%''' EXEC(@SQL) FETCH #databases INTO @c_dbname END CLOSE #databases DEALLOCATE #databases SELECT * FROM #results order by db, xtype, objectname DROP TABLE #results </pre> RE: How to find a text inside SQL Server procedures / triggers? - pectora139271 - 07-31-2023 I've used these in the past: * [Searching all user stored procedures for a table name][1] * [Search and replace SQL Server data in all columns of all tables][2] In this particular case, where you need to replace a specific string across stored procedures, the first link is probably more relevant. A little off-topic, the [Quick Find add-in][3] is also useful for searching object names with SQL Server Management Studio. There's a [modified version][4] available with some improvements, and another [newer version][5] also available on Codeplex with some other useful add-ins as well. [1]:http://groups.google.com/group/comp.databases.ms-sqlserver/msg/6f53a7cfc6ae0db0 [2]:http://vyaskn.tripod.com/sql_server_search_and_replace.htm [3]:http://jcooney.net/archive/2007/11/26/55358.aspx [4]:http://www.karpach.com/ViewArticle.aspx?ArticleFileName=SQL-Server-Managment-Studio-Object-Explorer-Search-Add-In.htm [5]:http://www.codeplex.com/SSMSAddins RE: How to find a text inside SQL Server procedures / triggers? - arteriovenous302 - 07-31-2023 here is a portion of a procedure I use on my system to find text.... DECLARE @Search varchar(255) SET @Search='[10.10.100.50]' SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like '%'+@Search+'%' ORDER BY 2,1 RE: How to find a text inside SQL Server procedures / triggers? - whitfieldbnepcbueqh - 07-31-2023 This will work for you: use [ANALYTICS] ---> put your DB name here GO SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition FROM sys.sql_modules AS sm JOIN sys.objects AS o ON sm.object_id = o.object_id where sm.definition like '%SEARCH_WORD_HERE%' collate SQL_Latin1_General_CP1_CI_AS ORDER BY o.type; GO RE: How to find a text inside SQL Server procedures / triggers? - kirschner40 - 07-31-2023 -- Declare the text we want to search for DECLARE @Text nvarchar(4000); SET @Text = 'employee'; -- Get the schema name, table name, and table type for: -- Table names SELECT TABLE_SCHEMA AS 'Object Schema' ,TABLE_NAME AS 'Object Name' ,TABLE_TYPE AS 'Object Type' ,'Table Name' AS 'TEXT Location' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%'+@Text+'%' UNION --Column names SELECT TABLE_SCHEMA AS 'Object Schema' ,COLUMN_NAME AS 'Object Name' ,'COLUMN' AS 'Object Type' ,'Column Name' AS 'TEXT Location' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%'+@Text+'%' UNION -- Function or procedure bodies SELECT SPECIFIC_SCHEMA AS 'Object Schema' ,ROUTINE_NAME AS 'Object Name' ,ROUTINE_TYPE AS 'Object Type' ,ROUTINE_DEFINITION AS 'TEXT Location' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%'+@Text+'%' AND (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure'); RE: How to find a text inside SQL Server procedures / triggers? - goshorn947 - 07-31-2023 There are **much** better solutions than modifying the text of your stored procedures, functions, and views each time the linked server changes. Here are some options: 1. Update the linked server. Instead of using a linked server named with its IP address, create a new linked server with the name of the resource such as `Finance` or `DataLinkProd` or some such. Then when you need to change which server is reached, update the linked server to point to the new server (or drop it and recreate it). 2. While unfortunately you cannot create synonyms for linked servers or schemas, you CAN make synonyms for objects that are located on linked servers. For example, your procedure `[10.10.100.50].dbo.SPROCEDURE_EXAMPLE` could by aliased. Perhaps create a schema `datalinkprod`, then `CREATE SYNONYM datalinkprod.dbo_SPROCEDURE_EXAMPLE FOR [10.10.100.50].dbo.SPROCEDURE_EXAMPLE;`. Then, write a stored procedure that accepts a linked server name, which queries all the potential objects from the remote database and (re)creates synonyms for them. All your SPs and functions get rewritten just once to use the synonym names starting with `datalinkprod`, and ever after that, to change from one linked server to another you just do `EXEC dbo.SwitchLinkedServer '[10.10.100.51]';` and in a fraction of a second you're using a different linked server. There may be even more options. I highly recommend using the superior techniques of pre-processing, configuration, or indirection rather than changing human-written scripts. Automatically updating machine-created scripts is fine, this is preprocessing. Doing things manually is awful. RE: How to find a text inside SQL Server procedures / triggers? - sabirs693993 - 07-31-2023 <pre><code>select text from syscomments where text like '%your text here%'</code></pre> RE: How to find a text inside SQL Server procedures / triggers? - excaudate314236 - 07-31-2023 *[Late answer but hopefully usefull]* Using system tables doesn't always give 100% correct results because there might be a possibility that some stored procedures and/or views are encrypted in which case you'll need to use [DAC][2] connection to get the data you need. I'd recommend using a third party tool such as [ApexSQL Search][1] that can deal with encrypted objects easily. Syscomments system table will give null value for text column in case object is encrypted. [1]: [To see links please register here] [2]:[To see links please register here] RE: How to find a text inside SQL Server procedures / triggers? - pissabedcjwpzanmdf - 07-31-2023 Any searching with select statement yield you only object name, where search keyword contains. Easiest and efficient way is get script of procedure/function and then search in generated text file, I also follows this technique :) So you are exact pinpoint. |