July 22, 2008 at 9:17 am
hi All,
I want to get a result code that two table structure is same or not how can i do ? Please let me know .
Thanks
arun
July 22, 2008 at 9:20 am
RedGate SQL Compare or ApexSQL Diff will do that for you pretty efficiently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2008 at 12:24 pm
This is not as complete as those tools, but it should do about 80% of it and it is free:
ALTER proc spUtil_CompareTableDefs(
@Schema1 SYSNAME
, @Table1 SYSNAME
, @Schema2 SYSNAME
, @Table2 SYSNAME
)
AS
/*
Compares the columns defintiions of two tables in the same database
and returns the columns that are defined differently
22-Jul-2008RBarryYoung, Proactive Performance solutions, Inc.
*/
Select @Schema1 as TABLE_SCHEMA
, @Table1 as TABLE_NAME
, *
From (
Select 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_SCHEMA = @Schema1
And TABLE_NAME = @Table1
EXCEPT
Select 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_SCHEMA = @Schema2
And TABLE_NAME = @Table2
) Table1_Diffs
UNION
Select @Schema2 as TABLE_SCHEMA
, @Table2 as TABLE_NAME
, *
From (
Select 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_SCHEMA = @Schema2
And TABLE_NAME = @Table2
EXCEPT
Select 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_SCHEMA = @Schema1
And TABLE_NAME = @Table1
) Table2_Diffs
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply