dataresearchlabs / sql_scripts

Useful sql scripts and snippets for MSSQL, Oracle, PostgreSQL, Greenplum, and MySQL

SQL Scripts

Useful sql scripts for MSSQL, MySQL, Oracle, PostgreSQL, and Greenplum. These are based on years of usage and refinement. These are common scripts used by data analysts, software testers, and other database professionals.

Data Dictionary

The Data Dictionary script and tutorials enable you to easily document an existing database schema. You can dump the tables, views, column descriptions, data types/lengths/sizes/precision, key constraints, and other information. Export to Excel for pretty output and simple filtering, searching, and sharing.

Data Validation Scripts

The Data Validation Framework scripts and tutorials enable you to easily standup a battery of automated data validation tests in your environment. Use the DVF for prod checkouts, or test and stage regression tests, or dev unit tests, or automated data validation after each data load. There are nine rule sets depicting 66 sample test cases to demonstrate how to test for row counts, keys, heuristic thresholds, numeric/date/text values, regular expressions, and data or schema diffs. The basic data validation script demonstrations executes all 66 sample validation tests putting out one line of text with the test id, status, and test description -- a nice simple way to organize your tests. The advanced data validation scripts execute the same 66 sample validation tests, but pushes output to a table and adds in the execution time in seconds, as well supporting detail rows on fails with the rejection code + reason, the expected value, the actual value, and the SQL to lookup the rejected row so you can copy-paste-execute-and-troubleshoot.

SchemaDiff

The SchemaDiff script and tutorials enable you to track changes to your schema over time or between environments. You'll know exactly what changed last night with a deployment vs. the prior night's stable baseline. You'll know when folks are changing your development or test environment every morning rather than 15 days later, avoiding all the troubleshooting red herrings.

If you like these scripts, please be sure to click the "Star" button above in GitHub.

Also, be sure to visit or subscribe to our YouTube channel www.DataResearchLabs.com!

sql_scripts's Issues

For XML Path data_dict_dump.sql

In your youtube video comment section, you mentioned 2016 or older cannot use the String_agg function.

You provided this link Stack Overflow XML Path Approach.
I am, so far, uncertain as how best to implement the XML Path function in each of the two sections of code.

Error:

Msg 102, Level 15, State 1, Line 82
Incorrect syntax near '('.

  , STRING_AGG(CAST(de.value AS VARCHAR(1024)), '.  ') WITHIN GROUP (ORDER BY de.value) AS description

Error:

Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '('.

  , STRING_AGG(key_typ, ',') WITHIN GROUP (ORDER BY key_typ) AS is_key 

I can/will give it a try, but I have never used XML Path before, nor string_agg, so if anyone more familiar can do it better or faster than my attempt, that would be greatly appreciated!

Using code on a 2016 SQL Server

I'm really hoping to use this code to help with generating a data dictionary. I tried using the code but am running into issues with the STRING_AGG function because I am using the SQL Server 2016 version. I would rather not update the server and was hoping there is a way to change these two lines that use STRING_AGG:

STRING_AGG(CAST(de.value AS VARCHAR(1024)), '.  ') WITHIN GROUP (ORDER BY de.value) AS description

STRING_AGG(key_typ, ',') WITHIN GROUP (ORDER BY key_typ) AS is_key 

I am new to SQL so any help would be very appreciated.