Solved

comparing 2 similar table's data types for any differences

Posted on 2010-09-10
4
242 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 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