Tuesday 21 June 2016

Simple SQL script for all tables

SELECT
    'delete from ' + table_name 
FROM 
    information_schema.tables 
WHERE
    table_name LIKE 'tbl_junk_%'

Friday 8 April 2016

Data reconciliation against SQL Servers

Requirement:
Compare two differently shaped data tables across two SQL Servers on a nightly basis.

Solution:
1. Create a view on each server that gets the data into a consistent (matching) shape.  Use CAST to ensure the datatype also match:



2. Create a RedGate SQLDataCompare project that compares the two views:



3. Run a SQLDataCompare command nightly:

4. Which produces the following results:


In this case, 2 records were identical, two records were different and one record was found in "DB2" that didn't exist in "DB1".

Here is the summary and detail output, which is configurable from within your project file.  I've opted to some identical records here, in production you probably wouldn't want to:




5. Email yourself the results 

Thursday 24 March 2016

Run all SQL files in a directory

Add this .bat file to the directory and run it:

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
pause




Adding speech marks around a list of data

# Ever had to add text characters to a list of data, like speech marks to each line before and after the data and a comma?

Here's an example of how to do this using Notepad++:

Sunday 24 January 2016