Toad World® Forums

Toad-SS - Hank Freeman Lazy Weekend TechTalk - TIP-001 (Dynamic SQL example of how to validate existence of a a Table and Column in a Secondary Database. [1 Attachment]


#1

[ Attachment(s) from Hank Freeman included below]

To My friends, fellow DBA(s) – Outlaws Members of the NRA.

Recently I have been working on a project where I am cleansing the PCI data in
multiple database on various servers and instances. I decided to run a cleanse
stored procedure from an Administrative database where I could log cleansed
metric data. I also had to stay out of the release or target databases, meaning
I could not make tables, procedures, functions , or views in the target
databases.

I will not go into the specifics but one of the tedious problems was determining
if the target database.schema.table and column even existed from the
Administration database because in some version of these database the tables
were not present so any update script I had as a standard solution would fail
because the table was missing. Then there was the problem of some of the
standard tables not having the target columns to update, which would produced an
error. So then I had to morph the script to determine if the target column for
the update was present, where the schema was not necessarily ‘dbo’.
This made my code look like I had come from another planet, which many you have
I am sure have suspected all along.

So in order to make a slippers that would fit all these Cinderella databases I
had to come up with code to interrogate the presence of the target tables and
columns and program around the nuances of these potentially missing objects.
Attached are the various scripts I developed that worked for me in interrogating
the presence of a table from the Master database where the target is
AdventureWorks.HumanReources.Employee and the table is ManagerID in the last
script example.

Now we all know there are many ways to get things done in SQL Server and I am
surely open to criticism here and welcome it but before you do, go look at the
attached PDF and play with it, for if you learn something from my efforts then I
have accomplished my goals here. If I learn a better way from someone else on
these boards, that would be terrific as well.

Enjoy !!!

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

image001.gif

image004.jpeg