— Applicable for SQL 2005 and later versions USE [Your_DB]; SELECT [Scehma] = schema_name(o.schema_id), o.Name, o.type FROM sys.sql_modules m INNER JOIN sys.objects o ON o.object_id = m.object_id WHERE m.definition like ‘%your_text_to_search%’
Tag: sp
How to insert results of a stored procedure into a temporary table
CREATE TABLE #tmpTable ( COL1 int, COL2 int, COL3 nvarchar(max), COL4 nvarchar(max), COL5 bit ) INSERT INTO #tmpTable exec SpGetRecords ‘Params’ NOTE: The columns of #tmpTable must be the same as SpGetRecords. Otherwise, there will be a problem. If there are no parameters in the stored procedure, you don’t need to use ‘Params’.
View the Definition of a Stored Procedure in SQL
– In Object Explorer, connect to an instance of the Database Engine. – On the toolbar, click New Query. – In the query window, enter the following statement that uses the sp_helptext system stored procedure. Change the database name and stored procedure name to reference the database and stored procedure that you want. EXEC sp_helptext […]
Rename column SQL Server
EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’
Create, Alter, Drop and Execute SQL Server Stored Procedures
A stored procedure is a saved block of T-SQL code, such as a query to list the rows in a table. A block of T-SQL code can be saved in a T-SQL script file. You can also store the code from a script file in a stored procedure. There are several benefits that result from […]
SQL Query to Find and Replace text in a stored procedures
Declare @spnames CURSOR Declare @spname nvarchar(max) Declare @moddef nvarchar(max) Set @spnames = CURSOR FOR select distinct object_name(c.id) from syscomments c, sysobjects o where c.text like ‘%findtext%’ and c.id = o.id and o.type = ‘P’ OPEN @spnames FETCH NEXT FROM @spnames into @spname WHILE @@FETCH_STATUS = 0 BEGIN Set @moddef = (SELECT Replace ((REPLACE(definition,’findtext’,’replacetext’)),’ALTER’,’create’) FROM sys.sql_modules […]
Search text in all stored procedures in SQL Server
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE ‘%search_parameter%’