detect different datatypes
- a simple and handy script that finds what differences might exists between the fields in your database that have the same name.I was wondering why my execution plans where different for tables with the same field names and the same indexes,statistics etc... because the syetem was converting my fields in the WHERE statement.
- it can be run in 6 ways:1 * 2 * 3
with database name, with no database name, with field name or not, with nothing as well
DROP PROCEDURE DETECT_DIFFERENT_DATATYPES
/*****************/
CREATE PROCEDURE DETECT_DIFFERENT_DATATYPES
@table_name sysname=NULL,
@column_name sysname=NULL,
@table_name_1 sysname=NULL
AS
if(@table_name IS not null) AND (@column_name IS null) AND (@table_name_1 IS null)
SELECT a.name AS table_1,inner_table.table_name AS table_2 ,
b.name AS column_table_1,inner_table.column_name AS column_table_2,
c.name AS datatype_table_1,inner_table.datatype AS datatype_table_2,
c.length AS length_table_1,inner_table.length AS length_table_2
FROM sysobjects a INNER JOIN syscolumns b
ON a.id=b.id
inner join systypes c
ON b.xtype=c.xtype
INNER JOIN
(SELECT a.name AS table_name,b.name AS column_name,c.name AS datatype,c.length AS length,a.xtype
FROM sysobjects a INNER JOIN syscolumns b
ON a.id=b.id
inner join systypes c
ON b.xtype=c.xtype
) inner_table
ON b.name =inner_table.column_name
AND a.name<>inner_table.table_name
AND c.name <>inner_table.datatype
WHERE a.xtype='U'
AND inner_table.xtype='U'
AND a.name=@table_name
ORDER BY table_1 ,table_2 ASC
ELSE
if(@table_name IS not null) AND (@column_name IS not null) AND (@table_name_1 IS null)
SELECT a.name AS table_1,inner_table.table_name AS table_2 ,
b.name AS column_table_1,inner_table.column_name AS column_table_2,
c.name AS datatype_table_1,inner_table.datatype AS datatype_table_2,
c.length AS length_table_1,inner_table.length AS length_table_2
FROM sysobjects a INNER JOIN syscolumns b
ON a.id=b.id
inner join systypes c
ON b.xtype=c.xtype
INNER JOIN
(SELECT a.name AS table_name,b.name AS column_name,c.name AS datatype,c.length AS length,a.xtype
FROM sysobjects a INNER JOIN syscolumns b
ON a.id=b.id
inner join systypes c
ON b.xtype=c.xtype
) inner_table
ON b.name =inner_table.column_name
AND a.name<>inner_table.table_name
AND c.name <>inner_table.datatype
WHERE a.xtype='U'
AND inner_table.xtype='U'
AND a.name=@table_name
AND b.name =@column_name
ORDER BY table_1 ,table_2 ASC
ELSE
if(@table_name IS null) AND (@column_name IS not null) AND (@table_name_1 IS null)
SELECT a.name AS table_1,inner_table.table_name AS table_2 ,
b.name AS column_table_1,inner_table.column_name AS column_table_2,
c.name AS datatype_table_1,inner_table.datatype AS datatype_table_2,
c.length AS length_table_1,inner_table.length AS length_table_2
FROM sysobjects a INNER JOIN syscolumns b
ON a.id=b.id
inner join systypes c
ON b.xtype=c.xtype
INNER JOIN
(SELECT a.name AS table_name,b.name AS column_name,c.name AS datatype,c.length AS length,a.xtype
FROM sysobjects a INNER JOIN syscolumns b
ON a.id=b.id
inner join systypes c
ON b.xtype=c.xtype
) inner_table
ON b.name =inner_table.column_name
AND a.name<>inner_table.table_name
AND c.name <>inner_table.datatype
WHERE a.xtype='U'
AND inner_table.xtype='U'
AND b.name=@column_name
ORDER BY table_1 ,table_2 ASC
ELSE
if(@table_name IS not null) AND (@table_name_1 IS not null)
SELECT a.name AS table_1,inner_table.table_name AS table_2 ,
b.name AS column_table_1,inner_table.column_name AS column_table_2,
c.name AS datatype_table_1,inner_table.datatype AS datatype_table_2,
c.length AS length_table_1,inner_table.length AS length_table_2
FROM sysobjects a INNER JOIN syscolumns b
ON a.id=b.id
inner join systypes c
ON b.xtype=c.xtype
INNER JOIN
(SELECT a.name AS table_name,b.name AS column_name,c.name AS datatype,c.length AS length,a.xtype
FROM sysobjects a INNER JOIN syscolumns b
ON a.id=b.id
inner join systypes c
ON b.xtype=c.xtype
) inner_table
ON b.name =inner_table.column_name
AND a.name<>inner_table.table_name
AND c.name <>inner_table.datatype
WHERE a.xtype='U'
AND inner_table.xtype='U'
AND a.name=@table_name
AND inner_table.table_name= @table_name_1
ORDER BY table_1 ,table_2 ASC
GO
