What are the challenges you face when working across database platforms? Take the survey

SQL Compare Bug

Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
edited March 3, 2005 11:59AM in SQL Compare Previous Versions
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
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
This discussion has been closed.