ApexSQL Clean has, among others, a useful feature that most people are not even aware of. It provides a client SQL code analysis in C#, Delphi, VB.NET, XAML, XML, ASP.NET, HTML, CSS code etc., and detects which SQL objects are actually being used and which ones aren’t in the referenced database. The SQL code analysis is performed by inspecting the code file for objects in the SQL database. This feature helps keeping SQL databases clean and organized
A SQL Server database is used to feed the inspection engine with the database object names, search for them in the code and locate references to database objects. Besides the ability to perform database analysis at once in a single or multiple custom code or project, it can even combine them. Additionally, ApexSQL Clean can be feed with a Visual Studio solution file for file and SQL code analysis. ApexSQL Clean will analyze all the files included in this solution
To use some of the aforementioned files:
In the New Project dialog window, click the Advanced tabs button and select the Files tab from the left pane. Under Look up in, it is possible to select files or projects for SQL code analysis, or to narrow the analysis down by using additional options
To add a folder with the files that should be checked for dependencies, click the Add folder button and select the folder. Once the folder is added in the Look up in in pane, additional options will be available:
- To check all folders in the selected directory click Include subfolders
- To select the files to be used for analysis, click on Select files. All files from the selected folder will be listed in the dialog window. If Include subfolders is selected, the files from all subfolders will be displayed as well
If there is a need for filtering the files by type, File Name Patterns allow customization and maintenance of the list for files to be searched during the analysis process. For example, if the client application was written in C#, then use a file name pattern .cs (or .cs$ to match the very last extension) so only the files containing .cs (or ending with .cs) are analyzed for SQL dependencies
When the option Match case is checked, the analysis will be case-sensitive. The option Match whole word only instructs the analysis to return only whole word matches. If there is a need for ignoring some comments in code, define the list of comments to be ignored in Comments to ignore. File references will be shown in the main grid and in the Child sub-grid for each object
This is the part of C# code where the SQL object is referenced:
Console.WriteLine(“nCustomers:n ————–“); var values = ListStuff(“SELECT CustomerName FROM Production.ProductCategory”); foreach (var value in values) { Console.WriteLine(value); }
As can be seen from the image above, there are two references of the Production.ProductCategory table in the Total column, which indicates that this table is referenced not only in the database but in the file as well. In the Children pane, file name where the object is referenced will appear, as well as its location in the file system
The process for adding projects is the same; the difference being that this time we need to select a project file
So remember, when there is a need for performing a quick SQL code analysis of custom code files to find references to an object in an SQL Server database, it can be accomplished, in most cases, using ApexSQL Clean
April 16, 2015
