SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%Foo%’ AND ROUTINE_TYPE=’PROCEDURE’ SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE ‘%Foo%’ AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1 GROUP BY OBJECT_NAME(id) SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, ‘IsProcedure’) = 1 AND definition LIKE ‘%Foo%’
You can create a table variable to hold the result set from the stored proc and then insert the output of the stored proc into the table variable, and then use the table variable exactly as you would any other table… … sql …. Declare @T Table ([column definitions here]) Insert @T Exec storedProcname params … Read more
SELECT … INTO … only works if the table specified in the INTO clause does not exist – otherwise, you have to use: INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key This assumes there’s only two columns in dbo.TABLETWO – you need to specify the columns otherwise: INSERT INTO dbo.TABLETWO (col1, … Read more
Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable. Declare @tablevar table(col1 col1Type,.. insert into @tablevar(col1,..) exec MyStoredProc ‘param1’, ‘param2’ SELECT col1, col2 FROM @tablevar
SHOW PROCEDURE STATUS; SHOW FUNCTION STATUS;
Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed). A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set. A point worth noting from comments, which summarize the … Read more
Escape the square brackets: … WHERE m.definition Like ‘%\[ABD\]%’ ESCAPE ‘\’ Then the square brackets will be treated as a string literals not as wild cards.
You can use OPENROWSET for this. Have a look. I’ve also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn’t already enabled. CREATE PROC getBusinessLineHistory AS BEGIN SELECT * FROM sys.databases END GO sp_configure ‘Show Advanced Options’, 1 GO RECONFIGURE GO sp_configure ‘Ad Hoc Distributed Queries’, 1 GO RECONFIGURE GO … Read more