Let me explain in simplified terms. We have an ETL process:

1) download data from the iSeries table Cust into SQL table Cust.
2) Tran_Cust is a view created FROM Cust
3) Copy from view Tran_Cust to table ods_Cust
4) v_ods_Cust is a view created from ods_Cust

I want to look for inconsistencies in the column definitions as a column moves from iSeries Cust, SQL Cust, view Tran_Cust, ods_Cust, view v_ods_Cust. The columns generally keep the same names except for v_ods_cust. Here a column like CustName will be created with a more meaningful column name - "CustName as 'Customer Name'.

We have hundreds of tables and we have column definition inconsistences where the host changed their column defintion and we made no changes on the SQL side. Or someone made a SQL column change in one table, but not both tables and the views were not dropped and recreated.

I have the list of tables. Think that I can put together some tsql to loop through the list of tables and create a new table based on the iSeries definition, then compare columns defintion across all the tables and views - except for the v_ods. THis is where columns are given new names - such as "CustName as 'Customer Name'". Need the actual view definition to find that CustName is 'Customer Name' and then compare the definition of ods_Cust.CustName to v_ods_Cust.[Customer Name]

How can I find the view definiton of say v_ods_Cust, seperate out the source column names (CustName) and destination column names (Customer Name) so that I can compare the source column names to another table and fine the differences?

Hi Brenda,

I'd first point you to the many schema compare tools out there (many of which can be obtained on a free trial)

http://blogs.msdn.com/b/ssdt/archive/2014/07/15/msbuild-support-for-schema-compare-is-available.aspx

http://blog.sqlauthority.com/2014/06/16/sql-server-sql-server-schema-compare-tool/

http://www.red-gate.com/products/sql-development/sql-compare/

https://www.devart.com/dbforge/sql/schemacompare/

If you'd rather have script to compare schemas you could look at the information_schema.columns view to see everything you ever wanted to know about a table.

select * from INFORMATION_SCHEMA.COLUMNS

This could easily be used in a script to either catalog what you have or to be compared with something else.

My main point is that I want to be able to split apart the column source and names, say I have the following definition for a view. I want to end up with a temptable with two rows. I realize that it will not always be this easy. But if I parse the view definition from SELECT to FROM on commas, and the separate on the AS I should come close.

table, source, columnname
testview, textinfo, [text info]
testview, itemname, [itemname]

create view testview as
    select textinfo as [text info],
		  itemname as [item name]
		  from ifs_config