Solved

comparing 2 similar table's data types for any differences

Posted on 2010-09-10
4
239 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
ID: 33645874
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
ID: 33646015
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
ID: 33646144
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
ID: 33646741
Thanks very much.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

896 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

14 Experts available now in Live!

Get 1:1 Help Now