Contents 

Ubitsoft SQL Enlight Online Help

Ubitsoft SQL Enlight

Refactoring

The SQL Enlight Refactoring features can make intelligent changes to the T-SQL source code. There are several available refactoring commands:

  • Encapsulate as Stored Procedure - Convert the selected code into a new stored procedure and optionally replace it with a call to the new procedure.
  • Encapsulate as Scalar Function - Convert the selected code into a new scalar function and optionally replace it with a SELECT of the function return value.
  • Encapsulate as Inline Function - Convert the selected code into a new inline table-valued function and optionally replace it with a SELECT from the new function.
  • Encapsulate as View - Convert the selected code into a new view and optionally replace it with a SELECT from the new view.
  • Encapsulate as SP_ExecuteSQL Call - Convert the selected code into a call to the sp_executesql stored procedure.
  • Extract to Separate Script - Create a new SQL document and extract the selected code to it.
  • Extract T-SQL Object's Body to Separate Script - Create a new SQL document and extract the selected body of the selected CREATE/ALTER procedure, function or view to the new document.
  • Expand SP_ExecuteSQL Call - Expand the selected call to the sp_executesql procedure.
  • Qualify Object Names - Add schema name to all the not qualified objects found in the selected T-SQL script.

The parameters of the new objects are determined from the variables which are referenced in the selected code. SQL Enlight automatically resolves the data type and the direction of these parameters. If a variable is used, but declared nowhere in the script, its declaration in the resulting script will have sql_variant data type.

Encapsulate as Stored Procedure

To encapsulate code as a new stored procedure:

1. Select the script that you want to use for the new stored procedure.

2. On the SQL Enlight->Refactor menu, click Encapsulate as Stored Procedure.

3. The Encapsulate as Stored Procedure dialog box is displayed.



The SQL script for the creation of the stored procedure is displayed in the Preview box.

4. Type the name of the owner in the Owner edit box.

5. Type the name of the procedure in the Name edit box.

Note: The procedure name is not checked for duplicate names. If a stored procedure with this owner and name already exists, the script will fail.

6. Review the generated script in the Preview box and modify it if necessary.

7. Select Modify source script to reference the new object if you want SQL Enlight to modify the originating script and replace the encapsulated code with reference to the new procedure. The generated reference to the new stored procedure is an EXECUTE statement with the procedure parameters.

8. Click Apply button to apply the changes.

Encapsulate as Scalar Function

Note: Statements creating or altering stored procedures, functions, triggers, views, rules and defaults cannot be encapsulated.

To encapsulate code as a new scalar function:

1. Select the script that you want to use for the new scalar function.

2. On the SQL Enlight->Refactor menu, click Encapsulate as Scalar Function....

3. The Encapsulate as Scalar Function dialog box is displayed. The SQL script for the creation of the scalar function is displayed in the Preview box.

4. Type the name of the owner in the Owner edit box.

5. Type the name of the function in the Name edit box.

6. Modify the order and the type of the parameters using the Parameters grid.

Note: The function name is not checked for duplicate names. If a function with this owner and name already exists, the script will fail.

7. Review the generated script in the Preview box and modify it if necessary.

8. Select Modify source script to reference the new object if you want SQL Enlight to modify the originating script and replace the encapsulated code with reference to the new function. The generated reference to the new scalar function is an SELECT statement selecting the return of the function call.

9. Click Apply button to apply the changes.

Encapsulate as Inline Table-Valued Function

Note: Only SELECT statements can be encapsulated in inline table-valued functions.

SELECT INTO and SELECT @local_variable statements are not allowed.

To encapsulate code as a new scalar function:

1. Select the script that you want to use for the new function.

2. On the SQL Enlight->Refactor menu, click Encapsulate as Inline Function.

3. The Encapsulate as Inline Function dialog box is displayed.
The SQL script for the creation of the function is displayed in the Preview box.

4. Type the name of the owner in the Owner edit box.

5. Type the name of the function in the Name edit box.

6. Modify the order and the type of the parameters using the Parameters grid.

Note: The function name is not checked for duplicate names. If a function with this owner and name already exists, the script will fail.

7. Review the generated script in the Preview box and modify it if necessary.

8. Select Modify source script to reference the new object if you want SQL Enlight to modify the originating script and replace the encapsulated code with reference to the new function. The generated reference to the new inline table-valued function is an SELECT statement selecting from return of the function.

9. Click Apply button to apply the changes.

Encapsulate as View

Note: Only single SELECT statements which do not reference variables can be encapsulated as views. SELECT INTO and SELECT @local_variable statements are not allowed.

To encapsulate code as a new view:

1. Select the script that you want to use for the new view.

2. On the SQL Enlight->Refactor menu, click Encapsulate as View.

3. The Encapsulate as View dialog box is displayed.
The SQL script for the creation of the view is displayed in the Preview box.

4. Type the name of the owner in the Owner edit box.

5. Type the name of the function in the Name edit box.

Note: The view name is not checked for duplicate names. If a view with this schema and name already exists, the script will fail.

6. Review the generated script in the Preview box and modify it if necessary.

7. Select Modify source script to reference the new object if you want SQL Enlight to modify the originating script and replace the encapsulated code with reference to the new function. The generated reference to the new view is an SELECT statement:

SELECT *

FROM [dbo].[NewView]

8. Click Apply button to apply the changes.

Encapsulate as SP_ExecuteSQL Call

The Encapusulate as SP_ExecuteSQL Call command modifies the source script and wraps the selected code as a call to the sys.sp_executesql stored procedure.

To encapsulate the selected code:

1. Select the script that you want to encapsulate.

2. On the SQL Enlight->Refactor menu, click Encapusulate as SP_ExecuteSQL Call.

3. The Encapsulate as SP_ExecuteSQL Call dialog box is displayed.
The resulting SQL script for is displayed in the Preview box.

4. Modify the order, the type and the direction of the parameters using the Parameters grid.

5. Review the generated script in the Preview box and modify it if necessary.

6. Click Apply button to apply the changes.

Extract to Separate Script

The Extract to Separate Script command extracts the selected script into a new SQL document.

To extract the selected code:

1. Select the script that you want to extract.

2. On the SQL Enlight->Refactor menu, click Extract to Separate Script.

3. The Extract to Separate Script dialog box is displayed.
The resulting SQL script for is displayed in the Preview box.

4. Modify the order, the type and the direction of the parameters using the Parameters grid.

5. Review the generated script in the Preview box and modify it if necessary.

6. Click Apply button to apply the changes.

Extract T-SQL Object's Body to Separate Script

The Extract T-SQL Object's Body to Separate Script command extracts the body of the selected stored procedure, view or function into a separate SQL document.

To extract the selected code:

1. Select CREATE/ALTER PROCEDURE/VIEW/FUNCTION script which you want to extract.

2. On the SQL Enlight->Refactor menu, click Extract T-SQL Object's Body to Separate Script.

3. The Extract T-SQL Object's Body to Separate Script dialog box is displayed.
The resulting SQL script for is displayed in the Preview box.

4. Review the generated script in the Preview box and modify it if necessary.

5. Click Apply button to apply the changes.

Expand SP_ExecuteSQL Call

The Expand SP_ExecuteSQL command expands the T-SQL code encapsulated as a call to the sys.sp_executesql sorted procedure.

1. Select EXECUTE sp_executesql call which you want to expand.

2. On the SQL Enlight->Refactor menu, click Expand SP_ExecuteSQL Call.

3. The Expand SP_ExecuteSQL Call dialog box is displayed.
The resulting SQL script for is displayed in the Preview box.

4. Review the generated script in the Preview box and modify it if necessary.

5. Click Apply button to apply the changes.

Qualify Object Names

The Qualify Object Names command modifies T-SQL code in a way that all the database objects referenced in it are in the format:

[schema_name].[object_name]

The SQL Server name and database name are not added to the object name.

To qualify object names:

  1. Open the script in a SQL Server Management Studio query editor.
  2. Ensure you are connected to the SQL Server and database that contains the objects that are referenced in the script.
  3. If required, select the code that contains the object names that you want to qualify.

    If you do not select any code, the all object names in the script are qualified.

  4. On the SQL Enlight menu, click Refactor->Qualify Object Names.

SQL Enlight connects to the SQL Server, resolves the not qualified identifiers and modifies the script. For each resolved or unresolved object name, a message is added to the SQL Enlight Error List describing the result.

The changes made by the Qualify Object Names command can be undone with using the standard Undo features of the code editor.