Find Text in MSSQL Stored Procedures and Functions with a Custom Function

Dejan Dozet — Thu, 31 Oct 2024 01:42:59 +0000

MSSQL Server Function: Find_Text_In_SP

Purpose

The Find_Text_In_SP function is a table-valued function that searches for a specific text within the stored procedures, functions, and other objects in a SQL Server database. It identifies objects where the provided text pattern exists, making it useful for developers who need to locate text references in their SQL objects.

Parameters

@String1ToSearch: nvarchar(100) - The text pattern to search for within the SQL objects.

Return

The function returns a table with two columns:

Name: The name of the object (stored procedure, function, etc.) containing the search text.
Type: The type of SQL object, such as:
- P for Stored Procedure
- FN for Function
- IF for Inline Table-Valued Function
- TF for Table-Valued Function
- TR for Trigger
- D for Default constraint

Filtering Logic

This function excludes:

System-generated objects that start with MSmerge, dt_, sp_cft, sel_, sp_sel, sp_upd, and sp_ins.
Specific procedure GrantExectoAllProcedures_sp.
Text that matches @String1ToSearch with a -done! suffix.

Example Usage

To search for occurrences of the term "CustomerData" within stored procedures and functions, execute:

SELECT * FROM [dbo].[Find_Text_In_SP]('CustomerData');

Final Thoughts

This function is incredibly useful for quickly locating references within SQL objects, saving time and streamlining database management.

For more details about it and much more similar posts, check this page on my blog where you can find Find_Text_In_SP full implementation: Searching for Functions on the Server.

TADOConnection: Proper Use of LoginPrompt

Dejan Dozet — Wed, 30 Oct 2024 11:43:16 +0000

When using TADOConnection in Delphi, you may want to control when the login dialog appears. Setting LoginPrompt to True triggers the prompt each time a connection is attempted, which is useful if credentials aren’t stored in the connection string.

Example Usage

Here's an example illustrating conditional use of LoginPrompt:

procedure TfrmMain.FormShow(Sender: TObject);
var
  i: integer;
begin
  con1.Provider := 'SQLOLEDB.1';
  con1.Properties['Application Name'].Value := Application.Title;
  with TIniFile.Create(ExtractFileDir(ParamStr(0)) + '\setup.ini') do
  begin
    con1.Properties['Initial Catalog'].Value := ReadString('database', 'Initial Catalog', '');
    con1.Properties['Data Source'].Value := ReadString('database', 'Data Source', '');
    if ReadBool('database', 'Integrated Security', false ) then
    begin
      con1.Properties['Integrated Security'].Value := 'SSPI';
      con1.Properties['Persist Security Info'].Value := 'False';
      con1.LoginPrompt := False;
    end
    else
    begin
      con1.Properties['Persist Security Info'].Value := 'True';
      con1.LoginPrompt := true;
    end;
  end;
end;

procedure TfrmMain.con1Login(Sender: TObject; Username, Password: string);
begin
  con1.Properties['User ID'].Value := Username;
  con1.Properties['Password'].Value := Password;
end;

In this case, the prompt appears only when credentials are missing, providing flexibility and enhancing security.

Tips

Conditionally prompt: Use LoginPrompt based on your security needs.
Avoid hardcoded credentials: Protect sensitive data when LoginPrompt is enabled.
Consistent user experience: Keep the prompt behavior uniform across your app.

Final Thoughts

Using LoginPrompt wisely can simplify the user experience while ensuring secure access.

For more details about it and much more similar posts, check this page on my blog: TADOConnection: Correct Way to Use LoginPrompt.

DEV Community: Dejan Dozet

Find Text in MSSQL Stored Procedures and Functions with a Custom Function

MSSQL Server Function: Find_Text_In_SP

Purpose

Parameters

Return

Filtering Logic

Example Usage

Final Thoughts

TADOConnection: Proper Use of LoginPrompt

Example Usage

Tips

Final Thoughts