LINKS
LINKS
MOST RECENT
Features in ApexSQL Edit that are not in MS SQL Server Management Studio
Improving the Readability of your Audit data
Expand * in SELECT statements to List all columns
Quickly convert SQL code to language-specific (e.g. C#, VB .Net) Client code
Quickly modify a SQL script so all object names are qualified
Quickly Convert your selected SQL code to a new stored procedure
Quickly Convert your selected SQL code to a new Inline Table-Valued Function
Quickly Convert your selected SQL code to a new Scalar Inline Function
|
Power User Blog Articles about advanced features for advanced users Features in ApexSQL Edit that are not in MS SQL Server Management Studio Here's a WP discussing features that are in ApexSQL Edit but not in MS SQL Server's Management Studio. Click here to read. AUTHOR Dejan Apostolovic Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...Improving the Readability of your Audit data This article describes how to use two of advanced features of ApexSQL Audit - watches and lookups. These features help improve the readability of your audit data by: a) Adding fields to your audit data that can be easily recognized by the users viewing your audit reports. For example, you can show the employee name of an audited row even if the employee name wasn't the actual column that was changed b) Showing a more readable supporting value to an audited field. For example, if you're auditing a store id, showing "Ajax Health Store" (the store name) is better than showing 5568 (the store id itself). What is a Watch? A Watch is a field selected to be added to the audit log when another field in a table is changed (inserted, updated, deleted) even if the Watched field, itself, isn't. Multiple Watch fields can be set up for an audited table. Several different Watch fields can be added to a single audited field. And a single Watch field can be associated with multiple audited fields in the same column. Why use Watches? Watches allow you to add readily recognizable fields to any audit change which is helpful for people looking at reports. For example, no matter the change is in Sales you can see the Store Name for example, even if the Store itself was not changed. This makes audit reports more readable. How to Create a Watch? Watches are added at the special Watch panel. The Watch panel can be accessed from the View Menu and it is in the same panel group as Field Grid, Row Key, and Lookups. To access it from there, just select the Panel Tab for "Watches". Example To explain it better we'll use one example. Let assume that we have table Sales.SpecialOffer with the following structure: ![]() Now, let assume that we want to audit changes in Discount Percents. This can be easily done by adding this column to audited columns. ![]() Every change on this column will be included to report: ![]() Now we can see that change was made on column DiscountPct and that old value was 10 and now it's 15. But we don't have additional information. We only have key - 2. To add more information, we can use watch. So, every time when change happens, we want to beside these information have another one - description to make it more readable. This must be added every time when DiscoundPct is changed. We should set Audit Field Name to DiscountPct and Watch Field Name to Description. ![]() To make this working, we must recreate triggers. Now, if DiscountPct is changed, we will see that Cup Sale, DVD Sale and Door Handle Sale discount is changed. ![]() Lookup is another very useful possibility. This makes reports even more readable. What is a Lookup? Lookups are a powerful feature of ApexSQL Audit that allows you to show the Literal, from a related table, along with the Value of a field that was audited. This way a more human readable value can be seen in the audit reports. For example "Ajax Health Store" (the store name) is much better than 5568 (the store id). Lookups are associated with the audited field so they will be triggered any time a row is inserted or deleted. They will be triggered if the specific column is changed in an update. Using lookups, you can define an ad hoc relationship between columns from different tables. As with watch columns, if no audited columns are affected by the data change, then none of the lookup values are captured either. Why use Lookups? Lookups provide Human Readable Audit data for reporting purposes. For example, there may be a numeric ID field in a table that was changed but would be meaningless to someone viewing the audit report. By adding a Lookup though, the literal, from the related code table, can be shown in addition to the ID field. How to Create a Lookup? Lookups are added at the special Lookups panel. The Lookups panel can be accessed from the View Menu and it is in the same panel group as Field Grid, Row Key, and Watches. To access it from there, just select the Panel Tab for "Lookups". Example To explain it better, let's see another simple example. Let assume that we have two tables Person.Person and Person.EmailAddress and they are connected via foreign key: ![]() Now, let say we want to audit all newly added Email Addresses. This is easy to achieve. We only need to set insert trigger to Person.EmailAddress table. ![]() If we add new email address, report will contain: ![]() Also, we want to know real user name which is bound this email. We will achieve this by using lookups: ![]() And, after recreating triggers, every new mail will be shown like this: ![]() But, there is more space for improvements. You can combine field into custom expression, so if you want to see full name with First and Last name, you can do this as follows: ![]() And report will contain full name like this: ![]() Labels: ApexSQL Audit ...Expand * in SELECT statements to List all columns The Expand Wildcards Refactor replaces all * wildcards in your SQL statements with its corresponding list of columns. Expanding wildcards allow you to make your code more resistant to breaking changes. Here is an example of how this refactor expands the wildcard on this SQL statement: Before Refactoring After refactoring SELECT titles.title_id,To use this feature in ApexSQL Edit, simply right-click the SQL code and select Refactor > Expand Wildcards from the context menu. ![]() This feature is available in both ApexSQL Edit and ApexSQL Refactor 2010. Labels: ApexSQL Edit, ApexSQL Refactor ...Quickly convert SQL code to language-specific (e.g. C#, VB .Net) Client code The Copy SQL Code As refactor will add syntax for a selected language (e.g. C#, PHP, Ruby, C++) to your SQL code and copy it to the clipboard. You can customize how syntax will be added for each language as well as add new languages. Below are some examples of how the refactor works. If you had following SQL code: CREATE TABLE [dbo].[ErrorLog] ( [ErrorLogID] [INT] IDENTITY ( 1 , 1 ) NOT NULL, [ErrorTime] [DATETIME] NOT NULL, [UserName] [SYSNAME] COLLATE sql_latin1_general_cp1_ci_as NOT NULL, [ErrorNumber] [INT] NOT NULL) GO SELECT * FROM table errorlog If you used Copy SQL Code As [name of language], your clipboard will hold properly transformed SQL code. Here are some examples on what will you get in your clipboard when using different languages. Copy SQL Code As C# string SQL = "CREATE TABLES [dbo].[ErrorLog]( \n" Copy SQL Code As VB .NET Dim SQL As String This options gives same results for previous versions of Visual Basic meaning you can use it for creating code for Visual Basic 6, too. Copy SQL Code As PHP <?php Copy SQL Code As Perl $SQL = 'CREATE TABLES [dbo].[ErrorLog]( \n' Copy SQL Code As Delphi var Copy SQL Code As Ruby SQL = 'CREATE TABLES [dbo].[ErrorLog]( \n' Copy SQL Code As C++ std::string SQL; Language Customization If you want to customizehow the SQL script is “copied” into the language of your choice, you can manually change each language’s settings. You will also be able to add and remove languages as well as modify existing ones. This can be done using the Customize Language Templates dialog, available from the context menu when you right click your code: Refactor > Copy SQL Code As > Customize Languages ... ![]() This refactor comes with several predefined languages: Java, Visual Basic, C#, C++, Ruby, PHP, Perl, Power Builder, Delphi, Dynamic SQL (T-SQL). To use this refactor, simply select your code and right-click. From the Refactor context menu, select Copy SQL Code As > Language. ![]() This refactor is available in ApexSQL Edit 2008.06 or higher and ApexSQL Refactor 2010. Labels: ApexSQL Edit, ApexSQL Refactor ...Quickly modify a SQL script so all object names are qualified The Qualify Object Names refactor: - Adds missing schema name to object names - Adds missing datasource name (tables, views, etc) to column names The schema name is added to the object name only if - the object name doesn’t contain schema name yet, - the object can have a schema (For example, Assembly can’t have a schema), and - the object exists in the database The datasource name is added to the column name in the following cases: - Datasource name is not specified yet - Datasource(table) has column with this name Below is an example: Before refactoring After refactoring SELECT DISTINCTTo use this refactor, select your code and from the context menu, select Refactor > Qualify Object Name ![]() This refactor is available in ApexSQL Edit 2008.05 and ApexSQL Refactor 2010. Labels: ApexSQL Edit, ApexSQL Refactor ...Quickly Convert your selected SQL code to a new stored procedure This refactoring allows extracting code fragment into a separate stored procedure. New procedure is created encapsulating the selected code fragment. It doesn’t matter for this refactors what kind of query you plan to encapsulate: scalar one or returning data as stored procedures can contain both types. Seamlessly to Encapsulate as Scalar Function and Encapsulate as Table-Valued Function refactors this refactor will determine required parameters for the encapsulated query and define them as new procedure parameters. Let’s review some examples of such refactoring. Before refactoring After refactoring the highlighted statement will be encapsulated into the separate stored procedure and the original code will be modified as follows: Please note that the name of the created stored procedure (dbo.name in example) can be customized. ![]() ![]() Let’s review another example for Encapsulate as Stored Procedure refactor. Before refactoring After refactoring Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...Quickly Convert your selected SQL code to a new Inline Table-Valued Function Encapsulate As Inline Table-Valued Function refactor allows extracting a query fragment as inline table-valued function. This can be useful if same data query was used in multiple locations, so this allows just referring to a new function from the code. Encapsulate As Inline Table-Valued Function refactor will recognize all variables and parameters used inside data query and transfer them into the result function parameters. Before refactoring highlight SELECT statement: After refactoring the selected statement will be encapsulated into the table valued function and the original code will be modified as follows: Please note that the name of the created table valued function (dbo.name in example) can be customized. ![]() ![]() Let’s review another example of Encapsulate Inline Table – Valued Function refactor. Before refactoring After refactoring Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...Quickly Convert your selected SQL code to a new Scalar Inline Function For helping you to make your T-SQL code more readable we developed a new refactor that enables you to extract large expressions or a set of multiple expressions into a separate scalar function that can be used then from within different areas of your code. Let’s review how Encapsulate as Scalar Inline Function refactor works on the below examples. Before refactoring select a scalar expression to encapsulate: After refactoring the selected expression will be encapsulated into the scalar function and the original code modified as follows: Please note that the name of the created scalar function (dbo.name in example) can be customized. ![]() ![]() Let’s review another example of Encapsulate as Scalar Inline Function refactor that shows how you can work with statements querying data. Before refactoring After refactoring -- Modified source: SELECT ProductNumber, Category = dbo.name(ProductLine) Note how all variables from the query are ported to the function variables. However you still need to customize the refactored code to make ProductLine field being a parameter as well. We plan to improve this refactor in the future version of ApexSQL Edit to catch up data fields automatically. Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...
|




















