SQL Compare Bug

Brian Donahue

Hi Paul,

The last time this had happened, it was because of a seemingly impossible situation where an IDENTITY column was detected that didn't have a precision and scale set. Can you please run the following query and see if it returns any precision or scale values that are NULL?

SELECT sysobjects.name AS TableName,
sysusers.name AS TableOwner,
c.name AS ColumnName,
c.colid AS ColumnID,
st.name AS TypeName,
CASE WHEN bt.name IN (N'nchar', N'nvarchar') THEN c.length/2 ELSE c.length END AS Length,
ColumnProperty(c.id, c.name, N'Precision') AS [Precision], 
ColumnProperty(c.id, c.name, N'Scale') AS Scale, 
CONVERT(bit, ColumnProperty(c.id, c.name, N'IsIdentity')) AS [Identity], 
bt.name AS BaseTypeName, 
CONVERT(bit, c.iscomputed) AS IsComputed, 
CONVERT(bit, ColumnProperty(c.id, c.name, N'IsIdNotForRepl')) AS NotForReplication,
CONVERT(bit,ColumnProperty(c.id, c.name, N'AllowsNull')) AS AllowNulls,
CASE WHEN (ColumnProperty(c.id, c.name, N'IsIdentity') <> 0) THEN IDENT_SEED('[' + sysusers.name + '].[' + sysobjects.name + ']') ELSE 0 END AS IdentitySeed,
CASE WHEN (ColumnProperty(c.id, c.name, N'IsIdentity') <> 0) THEN IDENT_INCR('[' + sysusers.name + '].[' + sysobjects.name + ']') ELSE 0 END AS IdentityIncrement,
CASE WHEN (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) THEN NULL ELSE USER_NAME(d.uid) + N'.' + d.name END AS DefaultName,
c.cdefault AS DefaultTextID,
USER_NAME(r.uid) + N'.' + r.name AS RuleName,
CASE WHEN (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst')<>0) THEN OBJECT_NAME(c.cdefault) ELSE NULL END AS DRIDefaultName,
c.cdefault AS defaultid,
CONVERT(bit, COLUMNPROPERTY(c.id, c.name, N'IsFullTextIndexed')) AS FullTextIndexed,
cc.text AS ComputedText,
CONVERT(bit, ColumnProperty(sysobjects.id, c.name, N'IsRowGuidCol')) AS IsRowGuidCol,
CONVERT(bit, ColumnProperty(sysobjects.id, c.name, N'IsFulltextIndexed')) AS FullTextIndexed
 
,c.collation AS Collation,
c.language AS FullTextLanguage
 
FROM 
dbo.syscolumns c  WITH (NOLOCK) INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype 
INNER JOIN dbo.systypes bt WITH (NOLOCK) ON bt.xusertype = c.xtype 
INNER JOIN dbo.sysobjects  WITH (NOLOCK) ON sysobjects.id=c.id 
LEFT JOIN dbo.sysusers WITH (NOLOCK)  ON sysusers.uid=sysobjects.uid 
LEFT JOIN dbo.sysobjects d  WITH (NOLOCK) ON d.id=c.cdefault
LEFT JOIN dbo.sysobjects r  WITH (NOLOCK) on r.id = c.domain
LEFT JOIN dbo.syscomments cc  WITH (NOLOCK) ON cc.id=sysobjects.id AND cc.number=c.colid
WHERE ObjectProperty(c.id,'IsTable')=1 AND ObjectProperty(c.id,'IsSystemTable')=0 
AND CONVERT(bit, ColumnProperty(c.id, c.name, N'IsIdentity')) =1 
ORDER BY sysobjects.name, sysusers.name, c.colid