Aşağıdaki sorguyu çalıştırarak içinde
ORDER BY
geçen SP'lerin bir listesini dökebiliriz.DECLARE @searchText NVARCHAR(50), @contains BIT; SELECT @searchText = '%' + 'ORDER BY' + '%', @contains = 1; SELECT OBJECT_SCHEMA_NAME(id) + '.' + OBJECT_NAME(id) AS name FROM sys.syscomments WHERE (OBJECTPROPERTY(id, 'IsProcedure') = 1) AND (@contains = 1) AND (text LIKE @searchText) OR (OBJECTPROPERTY(id, 'IsProcedure') = 1) AND (@contains = 0) AND (NOT (text LIKE @searchText)) GROUP BY OBJECT_SCHEMA_NAME(id), OBJECT_NAME(id) UNION SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) AS name FROM sys.sql_modules WHERE (OBJECTPROPERTY(object_id, 'IsProcedure') = 1) AND (@contains = 1) AND (definition LIKE @searchText) OR (OBJECTPROPERTY(object_id, 'IsProcedure') = 1) AND (@contains = 0) AND (NOT (definition LIKE @searchText))
Eğer içinde
ORDER BY
geçmeyen SP'lerin listesini dökmek istersek @contains
parametresinin değerini 0 (sıfır) vermemiz yeterlidir:SELECT ....., @contains = 0;
Hiç yorum yok:
Yorum Gönder