Solved

comparing 2 similar table's data types for any differences

Posted on 2010-09-10
4
231 Views
Last Modified: 2012-06-21
is the following method efficient/correct in making sure to see if any data type changes have occured, since a table was replicated (basically same schema with diff name) in the same database.

example:
tblTrackerSanctionJobs, and then another table tblTrackerSanctionJobs_history was created with same schema. There are 100+ columns - just want to check if all the datatypes align and are the same, and there are not extra and less columns (basically any changes to schema)..

the below uses the 2005 command 'except'

thanks.
(SELECT     TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, 

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, 

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, 

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

FROM         INFORMATION_SCHEMA.COLUMNS

WHERE     (TABLE_NAME = 'tblTrackerSanctionJobs')) 



EXCEPT



(SELECT     TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, 

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, 

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, 

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

FROM         INFORMATION_SCHEMA.COLUMNS

WHERE     (TABLE_NAME = 'tblTrackerSanctionJobs_history'))

Open in new window

0
Comment
Question by:anushahanna
  • 2
  • 2
4 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
This is probably better.
EXCEPT only gives one side of the story, i.e. data from the first (changed/does not exist in 2)


select SOURCE_TABLE=MAX(SOURCE_TABLE), TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,
                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,
                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
from
(
select SOURCE_TABLE='tblTrackerSanctionJobs',TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,
                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,
                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
from information_schema.columns
where table_name='tblTrackerSanctionJobs'
union all
select 'tblTrackerSanctionJobs_history',TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,
                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,
                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
from information_schema.columns
where table_name='tblTrackerSanctionJobs_history'
) X
group by TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,
                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,
                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
having count(*)=1
order by 4,1
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
very nice logic, Richard-

now, i was testing that code : when I change data type or add new columns in one of the tables, it brings them back.

But if I delete a column from one of the table, it brings lot more records.

to show my point: please see the code below




CREATE TABLE [dbo].[Test1](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[Value] [numeric](4, 1) NOT NULL,

	[Standard] [bit] NOT NULL,

	[Active] [bit] NOT NULL,

	[CreatedBy] [varchar](100) NOT NULL,

	[CreatedOn] [datetime] NOT NULL,

	[CreatedAt] [varchar](100) NOT NULL

)





CREATE TABLE [dbo].[Test2](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[Value] [numeric](4, 1) NOT NULL,

	[Standard] [bit] NOT NULL,

	[Active] [bit] NOT NULL,

	[CreatedBy] [varchar](100) NOT NULL,

	[CreatedOn] [datetime] NOT NULL,

	[CreatedAt] [varchar](100) NOT NULL

)



ALTER TABLE [dbo].[Test2] DROP COLUMN [Active]



select distinct SOURCE_TABLE=MAX(SOURCE_TABLE), TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

from

(

select SOURCE_TABLE='Test1',TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

from information_schema.columns

where table_name='Test1'

union all

select 'Test2',TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

from information_schema.columns

where table_name='Test2'

) X

group by TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

having count(*)=1

order by 4,1



DROP TABLE [dbo].[Test1]



DROP TABLE [dbo].[Test2]

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
Comment Utility
I wasn't sure what you wanted from the columns (so many).
ORDINAL POSITION needs to be removed.
CREATE TABLE [dbo].[Test1](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[Value] [numeric](4, 1) NOT NULL,

	[Standard] [bit] NOT NULL,

	[Active] [bit] NOT NULL,

	[CreatedBy] [varchar](100) NOT NULL,

	[CreatedOn] [datetime] NOT NULL,

	[CreatedAt] [varchar](100) NOT NULL

)





CREATE TABLE [dbo].[Test2](

	[ID] [int] IDENTITY(1,1) NOT NULL,

	[Value] [numeric](4, 1) NOT NULL,

	[Standard] [bit] NOT NULL,

	[Active] [bit] NOT NULL,

	[CreatedBy] [varchar](100) NOT NULL,

	[CreatedOn] [datetime] NOT NULL,

	[CreatedAt] [varchar](100) NOT NULL

)



ALTER TABLE [dbo].[Test2] DROP COLUMN [Active]



select distinct SOURCE_TABLE=MAX(SOURCE_TABLE), TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

from

(

select SOURCE_TABLE='Test1',TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

from information_schema.columns

where table_name='Test1'

union all

select 'Test2',TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

from information_schema.columns

where table_name='Test2'

) X

group by TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,

                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

                      DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,

                      COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME

having count(*)=1

order by 4,1



DROP TABLE [dbo].[Test1]



DROP TABLE [dbo].[Test2]

Open in new window

0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
Thanks very much.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Move SQL 2005 Express to Server 2012R2 19 68
query help 18 50
Complex SQL 10 32
SQL JOIN 6 27
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now