Skip to main content

Collation Conflicts

If you’ve ever worked with databases from different sources, or with different collation settings, you’ve probably encountered an error like this: Msg 468, Level 16, State 9, Line 1 Cannot resolve collation conflict between ‘Latin1_General_CI_AS’ and ‘SQL_Latin1_General_CP1_CI_AS’ in equal to operation. The error is cause by having different collation settings for the columns in the […]

Moving Tempdb

So somebody setup sql server and you now want to move tempdb to another drive? Easy. Fire up Query Analyser and run the commands: ALTER DATABASE tempdb MODIFY FILE (name = ‘tempdev’, filename = ‘new-file-path‘) ALTER DATABASE tempdb MODIFY FILE (name = ‘templog’, filename = ‘new-file-path‘) Replace the new-file-path with the full path and filename […]

Snippet: Show full column names and data types for all user tables in a database

SELECT QUOTENAME( sysobjects.name ) + ‘.’ + QUOTENAME( USER_NAME( sysobjects.uid ) ) + ‘.’ + QUOTENAME( syscolumns.name ) AS [Name] , TYPE_NAME( syscolumns.xusertype ) AS [Type] , syscolumns.prec AS [Precision] , syscolumns.scale AS [Scale] FROM syscolumnsINNER JOIN sysobjects ON syscolumns.id = sysobjects.idLEFT OUTER JOIN systypes ON systypes.xtype = syscolumns.xtypeWHERE sysobjects.xtype = ‘U’