Schema Search Introduction
Schema Search is very useful for finding all stored procedures or views that reference a column
and works through an API that is cross vendor compatible.
Needing to find an object and all it's dependencies in a database is a very
common activity. For example if you want to update a table column to rename it and you want to ensure you don’t break any existing uses of that column in the database.
Schema Search Sample UI
The Schema Search Tool sample WinForms UI ( and the C# source code) are included with every developer license. This UI showcases the possibilities behind the API, and allows for quick interactive sessions when you don’t want to write code.
You can easily search database schema across the following providers: Microsoft SQL Server, VistaDB, Microsoft SQL Compact (SQLCE) or MySQL.
The sample UI is very functional, and showcases the powerful Schema Search API in less than 500 lines of code.
Schema Search API Example
All CornerstoneDB Tool API’s are designed with the same requirements. Small learning curves, allow flexible application of the API, and solve one well defined developer need. Too often tool API’s resemble Swiss Army Knives with huge learning curves and functionality that is seldom used. We are striving to provide small, complete APIs for functionality most developers need.
The following is an example of using the Schema Search API to connect to a VistaDB database and search for a keyword across views and stored procedures. We could also have searched within Foreign Keys, Tables, and Columns.
Create a new SearchTool object
A new SearchTool object can be created using a provider specific connection string and the provider name.
SearchTool searchTool = new SearchTool(@"Data Source = C:\Database.vdb4", "vistadb");
Execute a search
Using the SearchTool object, we can then execute a search with the keyword, search filter options and a search type to get the results back in a new SearchResult object.
Searches can be exact matches, wildcard match, and regex expressions. This allows for a wide range of matching scenarios.
SearchResult result = Tool.Search("CompanyID", new List<SearchTool.SearchFilters>()
{ SearchTool.SearchFilters.StoredProcs, SearchTool.SearchFilters.Views },
SearchTool.SearchType.ExactMatch);
Iterate over the results
The result object contains database schema objects that match the search. This collection could then be used to make changes to the database, write out change logs, anything that you need. We plan to use this functionality internally in some up coming tools ourselves.
Console.WriteLine(string.Format("Views matched search {0}", result.Views.Count));
foreach (View v in result.Views)
{
Console.WriteLine(string.Format("View Name: {0}", v.Name));
}
Signup for a trial
Would you like to try one of our database tools? Visit the
trial signup page for complete information.