11/19/2023 0 Comments Sqlpro search for coulmn![]() ![]() The ROUTINE_DEFINITION column contains the source statements that created the function or stored nvarchar( 50 ) SET = SELECT DISTINCT The INFORMATION_SCHEMA.ROUTINES view contains information about all stored procedures and functions in a database. Use SQL that queries the INFORMATION_SCHEMA.ROUTINES view to search for a specific parameter in all procedures. When searching for objects in other database object types – functions, triggers, columns, etc., or in multiple database object types at the same time, the SQL shown above should be modified accordingly. The following SQL examples search for the specified text – the variable – in stored procedures. However, all of these methods include querying system objects. There are several methods that can be used. Searching for a database object name or object definition is a bit easier than searching for specific text. To search for other data types, such as time and datetime, a new code must be written. Another disadvantage is that it can be used to search for text data only. The drawbacks of this solution are: use of cursors, which are generally inefficient, high complexity, a lot of time needed for execution, even on small databases. OPEN CursorColumns FETCH NEXT FROM CursorColumns INTO WHILE =īEGIN SET = 'IF EXISTS (SELECT * FROM ' + ' WHERE ' + ' LIKE ''%' + '%'') PRINT ''' + ', ' + '''' EXECUTE FETCH NEXT FROM CursorColumns INTO END CLOSE CursorColumns DEALLOCATE CursorColumns FETCH NEXT FROM CursorSearch INTO, END CLOSE CursorSearch DEALLOCATE CursorSearch WHERE object_id AND system_type_id IN(167, 175, 231, 239) - the columns that can contain textual data Type = 'U' = tables(user-defined) OPEN CursorSearch įETCH NEXT FROM CursorSearch INTO, WHILE =īEGIN DECLARE CursorColumns CURSOR FOR SELECT name WHERE type = 'U' -list of tables in the current database. Varchar(200) varchar(100) int varchar(100) varchar(1000) -modify the variable, specify the text to search for SET = 'John' ĭECLARE CursorSearch CURSOR FOR SELECT name, object_id FROM sys. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |