Eine immer wiederkehrende Aufgabe ist der Import oder Export von Daten zweier Tabellen aus meist verschiedemen Datenbanken. Der SQL Code vergleicht die ähnlichen Feldnamen und deren Datentypen.
English:
A recurring task is the import or export of data from two tables from mostly different databases. The SQL code compares the similar field names and their data types (table shema).
Here is a great contribution from mssqltips.com:
https://www.mssqltips.com/sqlservertip/4824/easy-way-to-compare-sql-server-table-schemas/
[pastacode lang=“sql“ manual=“–https%3A%2F%2Fwww.mssqltips.com%2Fsqlservertip%2F4824%2Feasy-way-to-compare-sql-server-table-schemas%2F%0A–By%3A%20Matteo%20Lorini%0A–A%20mapping%20of%20table%20names%20has%20been%20added%2C%20I.Hill%0A%0ADECLARE%20%40TableFromLink%20%20VARCHAR(MAX)%20%3D%20’%5BsourceServer%5D.%5Bdatabase%5D.%5Bdbo%5D.%5BsourceTable%5D’%0ADECLARE%20%40TableToLink%20%20VARCHAR(MAX)%20%3D%20’%5BImportToServer%5D.%5Bdatabase%5D.%5Bdbo%5D.%5BImportToTable%5D’%0ADECLARE%20%40LinkMapping%20%20VARCHAR(MAX)%20%3D%20’%5BTest%5D.%5Bdbo%5D.%5BTempMappingTable%5D’%0A%0ADECLARE%20%40DynSql%20nvarchar(max)%20%3D%20“%0ADECLARE%20%40NewMappingTable%20BIT%20%3D%201%20%0A%0A%0AIF%20%40NewMappingTable%20%3D%201%0ABEGIN%0A%09%09———————————————————————————————————-%0A%09%09–Create%20a%20mapping%20table%20and%20save%20the%20field%20data%0A%09%09IF%20OBJECT_ID(%40LinkMapping%20%2C%20’U‘)%20is%20not%20null%09BEGIN%20SET%20%40DynSql%20%3D%20’DROP%20TABLE%20’%20%2B%20%40LinkMapping%3B%20EXEC(%40DynSql)%3B%20END%0A%09%09SET%20%40DynSql%20%3D%20%0A%09%09’%0A%09%09CREATE%20TABLE%20’%20%2B%20%40LinkMapping%20%2B%20′(%20%0A%09%09%09ColumnName%20VARCHAR(MAX)%20NOT%20NULL%2C%20%0A%09%09%09ColumnNameMapping%20VARCHAR(MAX)%20DEFAULT%20““%2C%0A%09%09%09Import%20BIT%20DEFAULT%201%2C%0A%09%09%09Datatype%20VARCHAR(MAX)%20NOT%20NULL%2C%20%0A%09%09%09IsNullable%20BIT%2C%0A%09%09%09IsIdentity%20BIT%2C%0A%09%09%09MaxLength%20INT%2C%0A%09%09%09collation_name%20VARCHAR(MAX)%20DEFAULT%20““%2C%0A%09%09%09ConvertStr%20VARCHAR(MAX)%20DEFAULT%20““%09%0A%09%09%09)%0A%0A%09%09INSERT%20INTO%20’%20%2B%20%40LinkMapping%20%2B%20’%20(ColumnName%2C%20ColumnNameMapping%2C%20Datatype%2C%20IsNullable%2C%20IsIdentity%2C%20collation_name%20%2CMaxLength)%0A%09%09SELECT%20%0A%09%09%09toTbl.name%20as%20toTbl_ColumnName%2C%20%09%20%0A%09%09%09toTbl.name%20as%20toTbl_ColumnName%2C%0A%09%09%09toTbl.system_type_name%20as%20toTbl_Datatype%2C%20%09%0A%09%09%09toTbl.is_nullable%20as%20toTbl_is_nullable%2C%20%09%09%0A%09%09%09toTbl.is_identity_column%20as%20toTbl_is_identity%2C%0A%09%09%09CASE%20WHEN%20toTbl.collation_name%20IS%20NOT%20NULL%20THEN%20toTbl.collation_name%20ELSE%20““%20END%2C%0A%09%09%09toTbl.max_length%0A%09%09FROM%20sys.dm_exec_describe_first_result_set%20(N“Select%20*%20From%20’%20%2B%20%40TableFromLink%20%2B%20“’%2C%20NULL%2C%200)%20AS%20toTbl%09%09%0A%09%09———————————————————————————————————-%0A%09%09———————————————————————————————————-%0A%09%09’%0A%09%09PRINT%20%40DynSql%0A%09%09EXEC(%40DynSql)%0AEND%0A%0AIF%20OBJECT_ID(‚tempdb..%23%23MappingTable’%2C%20’U‘)%20is%20not%20null%09DROP%20TABLE%20%23%23MappingTable%0ASET%20%40DynSql%20%3D%20’SELECT%20*%20INTO%20%23%23MappingTable%20FROM%20’%20%2B%20%40LinkMapping%3B%20EXEC(%40DynSql)%3B%0A%0A%0A%0ASELECT%20%0A%09%09%0A%09%0A%09Source.ColumnName%20as%20Source_ColumnName%2C%20%09%0A%09Source.ColumnNameMapping%20AS%20SourceMapping%2C%0A%09toTbl.name%20as%20toTbl_ColumnName%2C%0A%09%0A%09CASE%20WHEN%20Source.ColumnNameMapping%20!%3D%20toTbl.name%20THEN%20’X’%20ELSE%20“%20END%20AS%20nameDIff%2C%09%20%0A%09Source.IsNullable%20as%20Source_is_nullable%2C%0A%09toTbl.is_nullable%20as%20toTbl_is_nullable%2C%0A%09CASE%20WHEN%20toTbl.is_nullable%20!%3D%20Source.IsNullable%20THEN%20’X’%20ELSE%20“%20END%20AS%20nullDIff%2C%0A%09Source.Datatype%20as%20Source_Datatype%2C%0A%09toTbl.system_type_name%20as%20toTbl_Datatype%2C%20%09%20%09%0A%09CASE%20WHEN%20Source.Datatype%20%20!%3D%20toTbl.system_type_name%20THEN%20’X’%20ELSE%20“%20END%20AS%20typeDIff%2C%0A%09Source.MaxLength%20AS%20Source_length%2C%0A%09toTbl.max_length%20AS%20toTbl_length%2C%09%09%09%0A%09CASE%20WHEN%20Source.MaxLength%20%20!%3D%20toTbl.max_length%20THEN%20’X’%20ELSE%20“%20END%20AS%20lengthDIff%2C%0A%09Source.IsIdentity%20as%20Source_is_identity%2C%0A%09toTbl.is_identity_column%20as%20toTbl_is_identity%2C%20%09%0A%09CASE%20WHEN%20toTbl.is_identity_column%20!%3D%20Source.IsIdentity%20THEN%20’X’%20ELSE%20“%20END%20AS%20idenDIff%2C%09%0A%09Source.collation_name%20AS%20Source_collation_name%2C%20%0A%09CASE%20WHEN%20toTbl.collation_name%20%20IS%20NOT%20NULL%20THEN%20toTbl.collation_name%20%20ELSE%20“%20END%20AS%20toTbl_collation_name%2C%0A%09CASE%20WHEN%20toTbl.collation_name%20!%3D%20Source.collation_name%20THEN%20’X’%20ELSE%20“%20END%20AS%20collationDIff%0A%0AFROM%20sys.dm_exec_describe_first_result_set%20(N’Select%20*%20From%20’%20%2B%20%40TableToLink%2C%20NULL%2C%200)%20AS%20toTbl%20%0AFULL%20OUTER%20JOIN%20%20%23%23MappingTable%20AS%20Source%20%0AON%20UPPER(toTbl.name)%20%3D%20UPPER(Source.ColumnNameMapping)%20%0AWHERE%20%0A%09%09Source.Import%20%3D%201%20%0A%09%09–AND%0A%09%09–Source.ColumnName%20IS%20NOT%20NULL%20AND%20%0A%09%09–(%0A%09%09–toTbl.name%20IS%20NULL%20OR%0A%09%09–Source.ColumnNameMapping%20IS%20NULL%20OR%0A%09%09–toTbl.name%20!%3D%20Source.ColumnNameMapping%20OR%0A%09%09–toTbl.is_nullable%20!%3D%20Source.IsNullable%20OR%0A%09%09–toTbl.system_type_name%20!%3D%20Source.Datatype%20OR%0A%09%09–toTbl.max_length%20!%3D%20Source.MaxLength%20OR%0A%09%09–toTbl.is_identity_column%20!%3D%20Source.IsIdentity%20OR%0A%09%09–toTbl.collation_name%20!%3D%20Source.collation_name%0A%09%09–)“ message=“compare a table shema in t-sql“ highlight=““ provider=“manual“/]
