What’s up?

Check out my recent article in SQL Server magazine June edition to display the tables in hierarchical manner based on the relations.http://www.sqlmag.com/Article/ArticleID/101931/sql_server_101931.html.This article is specifically to produce the list of all the tables across the schemas hierarchically, so what is so special about this.

Below are the top 5 advantages:.
Database migration
SSIS Development
Validate your schema
Data Refresh
Data Masking
Validate the design based on data, not on schema

Database Migration

Migrating data from legacy database to SQL Server, this script will give you 2 sets where the set A contains the list of the week entities (tables) where you can migrate the data at a single step.

Set B contains the list of the tables in hierarchy where you should be executing in a transaction. It's better to execute one transaction at a time to handle the errors or put in a transaction.

It's possible to generate the generic insert statements through populating the output of this procedure into a table and run a select statement against this table to generate generic insert statements which will gives the whole insert statements to select the data from the staging and insert in target database.

 

SSIS Development

Run this procedure and design your package based on the sequence from the output.

Validate your schema

Through this you can validate your database design as it produce the table list which are in conflicts due to self referencing (one-many & many-one)

Data Refresh

You can generate the generic data refresh script through this procedure instead of creating an SSIS package which is very time consuming and prone to errors when there is a change in schema at the development.

Check out this article. http://www.calsql.com/2009/09/synchronize-data-between-databases-in.html

Data Masking

Masking data can be done through scrubbing the sensitive fields (SSN, credit card number) , but scrubbing the primary field requires populating the relational foreign key values at the same time.

 Check out this article about how to scrub the data in development environment to hide the sensitive information using one of the statements inside this procedure

http://www.calsql.com/2009/09/ms-sql-server-data-masking.html

Validate the design based on data, not on schema

Validate the schema based on the data is highly required especially when you migrate the data from legacy system to new system where the legacy system doesn't have the capability to maintain the data integrity through the constraints.

When you create a new schema on new system with constraints based on the assumptions that the legacy system data is in sync with the constraints which you have created on new system but when you populate the data it might give the errors when the migrated data is not according to the relations (PK-FK) on new system.

This procedure will give you the list of the tables as errors which are having bad data or bad relationships in new schema.

What else

Watch this space for more advantages about this procedure and do let me know you inputs to enhance this script further.