我有下面的代码
SELECT tA.FieldName As [Field Name],
COALESCE(tO_A.[desc], tO_B.[desc], tO_C.Name, tA.OldVAlue) AS [Old Value],
COALESCE(tN_A.[desc], tN_B.[desc], tN_C.Name, tA.NewValue) AS [New Value],
U.UserName AS [User Name],
CONVERT(varchar, tA.ChangeDate) AS [Change Date]
FROM D tA
JOIN
[DRTS].[dbo].[User] U
ON tA.UserID = U.UserID
LEFT JOIN
A tO_A
on tA.FieldName = 'AID'
AND tA.oldValue = CONVERT(VARCHAR, tO_A.ID)
LEFT JOIN
A tN_A
on tA.FieldName = 'AID'
AND tA.newValue = CONVERT(VARCHAR, tN_A.ID)
LEFT JOIN
B tO_B
on tA.FieldName = 'BID'
AND tA.oldValue = CONVERT(VARCHAR, tO_B.ID)
LEFT JOIN
B tN_B
on tA.FieldName = 'BID'
AND tA.newValue = CONVERT(VARCHAR, tN_B.ID)
LEFT JOIN
C tO_C
on tA.FieldName = 'CID'
AND tA.oldValue = tO_C.Name
LEFT JOIN
C tN_C
on tA.FieldName = 'CID'
AND tA.newValue = tN_C.Name
WHERE U.Fullname = @SearchTerm
ORDER BY tA.ChangeDate
当运行代码时，我在添加表c的两个连接后，将错误粘贴在标题中。我认为这可能与我使用SQL Server 2008的事实有关，并在2005年的机器上恢复了这个db的副本。
多亏了marc_s的回答，我解决了我最初的问题——受到启发，我想更进一步，发布一种一次转换整个表的方法——tsql脚本生成alter列语句:
DECLARE @tableName VARCHAR(MAX)
SET @tableName = 'affiliate'
--EXEC sp_columns @tableName
SELECT 'Alter table ' + @tableName + ' alter column ' + col.name
+ CASE ( col.user_type_id )
WHEN 231
THEN ' nvarchar(' + CAST(col.max_length / 2 AS VARCHAR) + ') '
END + 'collate Latin1_General_CI_AS ' + CASE ( col.is_nullable )
WHEN 0 THEN ' not null'
WHEN 1 THEN ' null'
END
FROM sys.columns col
WHERE object_id = OBJECT_ID(@tableName)
得到:
修改myTable关联列(4000)，将Latin1_General_CI_AS为空
我承认对col.max_length / 2 -的需求感到困惑
我也有类似的要求;在这里为任何有类似情况的人记录我的方法……
场景
我有一个数据库从一个干净的安装与正确的排序规则。
我有另一个数据库，它有错误的排序。
我需要更新后者以使用在前者上定义的排序规则。
解决方案
使用SQL Server模式比较(来自SQL Server数据工具/ Visual Studio)来比较源(干净安装)和目标(无效排序规则的db)。
在我的例子中，我直接比较了两个db;虽然你可以通过一个项目，让你手动调整之间的部分…
Run Visual Studio
Create a new SQL Server Data Project
Click Tools, SQL Server, New Schema Comparison
Select the source database
Select the target database
Click options (⚙)
Under Object Types select only those types you're interested in (for me it was only Views and Tables)
Under General select:
Block on possible data loss
Disable & reenable DDL triggers
Ignore cryptographic provider file path
Ignore File & Log File Path
Ignore file size
Ignore filegroup placement
Ignore full text catalog file path
Ignore keyword casing
Ignore login SIDs
Ignore quoted identifiers
Ignore route lifetime
Ignore semicolon between statements
Ignore whitespace
Script refresh module
Script validation for new constraints
Verify collation compatibility
Verify deployment
Click Compare
Uncheck any objects flagged for deletion (NB: those may still have collation issues; but since they're not defined in our source/template db we don't know; either way, we don't want to lose things if we're only targeting collation changes). You can unchceck all at once by right clicking on the DELETE folder and selecting EXCLUDE.
Likewise exclude for any CREATE objects (here since they don't exist in the target they can't have the wrong collation there; whether they should exist is a question for another topic).
Click on each object under CHANGE to see the script for that object. Use the diff to ensure that we're only changing the collation (anything other differences manually detected you'll likely want to exclude / handle those objects manually).
Click Update to push changes
这仍然需要一些手工工作(例如，检查你只影响排序)-但它为你处理依赖关系。
此外，您还可以保留一个有效模式的数据库项目，以便在有多个数据库需要更新时为您的数据库使用通用模板，假设所有目标数据库最终都应该具有相同的模式。
你也可以对数据库项目中的文件使用find/replace，如果你希望大量修改那里的设置(例如，你可以使用模式比较从无效的数据库创建项目，修改项目文件，然后在模式比较中切换源/目标，将你的更改推回DB)。
