Advanced SQL Scripts
Other: Mass-Altering a Universal Database
The Universal Schema is Continuously Evolving The Universal Database schema continually undergoes slight modifications in order to continually upgrade the migration experience. In most cases, these changes are minor and will not interrupt an ...
Other: Total Record Counts
The following SQL script will count all records in all tables in a database. DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ; INSERT INTO @TableRowCounts ([TableName], [RowCount]) EXEC sp_MSforeachtable 'SELECT ''?'' ...
Other: Obtaining Record Counts for all Tables
The following script will count all records in all tables. DECLARE @TableRowCounts TABLE ( [TableName] VARCHAR(128), [Batch_Number] INT, [Incomplete] INT, [Complete] INT, [Total] INT ) ; INSERT INTO @TableRowCounts ( [TableName], [Batch_Number], ...
Other: Trimming Field Lengths
Sometimes you may want to manually trim records so that they have certain lengths. The scripts below provide examples on how to best do this. --This is the max length you want DECLARE @MaxLength INT = 50 --This is the characters you want for an ...
Other: Global SQL Search
The following query will create a stored procedure named SearchAllTables which will let you search all tables in the database for a value. It can be used the following ways: --Search for a column that exactly contains 'Test': exec SearchAllTables ...
Other: Creating Unique Display Numbers
The following script will create unique Final_ReferenceCode for all Contacts. It can be easily modified to apply to other record types. Please note that the Final_ReferenceCode that are generated are based on the data in the database. If you are ...
Audit Scripts: Row Counts and Progress Monitoring
This article shows you a script that can be used to monitor the progress of both a Backup and a Restore. It can also be used to just review row counts on all the Universal Database tables. The first code block is used to populate a SQL Server Temp ...
Audit Scripts: Activity Billed/Unbilled row counts
After you have backed up financial information like Time Entries, Expense Entries, and Fee Entries it is a good idea to audit the results prior to restoring data to the destination system. For example, some Universal Destinations support restoring ...
Audit Scripts: General Audit
After completing a backup there are a number of things that are good to review prior to restoring into the destination system. The script below reviews several of the data points that should be looked at prior to restoring. This script is not ...
Tasks and Calendar Entries: Removing Duplicate User Assignments
Sometimes the same user is included in a calendar event, or assigned to a task, multiple times. This can be a result of User Mapping or data error in the legacy system. Below is an example of how this can happen. A calendar event includes user John ...
Detecting Duplicates by Reference Code
In some scenarios, you need to ensure that a reference code is completely unique among a record type. To do this, use the following SQL script: --Look for duplicate records in the __M_Matters table SELECT * FROM --Change this to the table we want to ...
SQL Spreadsheets: Cherry Picking Contacts
The following SQL command will generate a spreadsheet that can be used to cherry-pick specific contacts. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...
Document Register: Moving digital documents into a Document Register Matter
/* This script is a template that will help you move library documents (document register / safe custody records) into a general 'Document Register' matter. You should consider this a TEMPLATE that you may want to tweak and adjust for each client's ...
Clear all Result_Ids
/* WARNING!!! THIS IS A DANGEROUS SCRIPT!!! MAKE SURE YOU ARE USING THE RIGHT DATABASE!!! This script will clear out all Result_Ids in a database. */ USE [DATABASENAME]; EXEC sp_MSforeachtable 'UPDATE ? SET Batch_Number = 0, Result_Id = '''' '