我在数据库中发现了一些旧风格的默认设置,需要批量更新uses_ansi_nulls和uses_quoted_identifier。以下是我编写的代码,以替换旧风格的默认设置(CREATE DEFAULT sp_BindDefault)为更新的样式(ALTER TABLE)。
DECLARE @strSQL varchar(max)
, @DBName varchar(50) = DB_NAME();
DROP TABLE IF EXISTS #tmp;
SELECT IDENTITY(int, 1,1) AS ID,
UnbindCmd = 'EXEC sp_unbindefault ''' + t.Name + '.' + c.name + ''';',
DropCmd = 'DROP DEFAULT IF EXISTS [' + SCHEMA_NAME(SO.schema_id) + '].[' + OBJECT_NAME(SO.object_id) + '];',
CreateCmd = 'ALTER TABLE [' + SCHEMA_NAME(t.Schema_id) + '].[' + t.Name + ']'
+ ' ADD CONSTRAINT df_' + REPLACE(t.Name, ' ', '') + '_' + REPLACE(c.Name, ' ', '')
+ ' DEFAULT (' + RIGHT(m.definition, LEN(m.definition) - CHARINDEX(' AS ', m.definition) - 3)
+ ') FOR ' + QUOTENAME(c.Name) + ';'
INTO #tmp
FROM sys.sql_modules m
join sys.columns c on c.default_object_id = m.object_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.objects SO ON SO.object_id = C.default_object_id
WHERE (m.uses_ansi_nulls = 0
OR m.uses_quoted_identifier = 0)
ORDER BY
t.name
, c.name;
BEGIN TRANSACTION
BEGIN TRY
DECLARE UnbindCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT UnbindCmd
FROM #tmp
ORDER BY
ID;
OPEN UnbindCursor;
FETCH NEXT FROM UnbindCursor INTO @strSQL;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@strSQL);
FETCH NEXT FROM UnbindCursor INTO @strSQL;
END
CLOSE UnbindCursor;
DEALLOCATE UnbindCursor;
DECLARE DropCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT DropCmd
FROM #tmp
GROUP BY
DropCmd
ORDER BY
MIN(id)
OPEN DropCursor;
FETCH NEXT FROM DropCursor INTO @strSQL;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@strSQL);
FETCH NEXT FROM DropCursor INTO @strSQL;
END
CLOSE DropCursor;
DEALLOCATE DropCursor;
DECLARE CreateCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT CreateCmd
FROM #tmp
ORDER BY
id
OPEN CreateCursor;
FETCH NEXT FROM CreateCursor INTO @strSQL;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@strSQL);
FETCH NEXT FROM CreateCursor INTO @strSQL;
END
CLOSE CreateCursor;
DEALLOCATE CreateCursor;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(4000)
, @ErrorSeverity int
, @ErrorState int;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
RAISERROR (@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
END CATCH