[研究]T-SQL, SQL Server, 把欄位名稱以 Finish 結尾的，預設值設定為 N''

[研究]T-SQL, SQL Server, 把欄位名稱以 Finish 結尾的，預設值設定為 N''

2024-01-31

環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19

N'' 是設定為空字串。

********************************************************************************

修改單一欄位預設值

ALTER TABLE YourSchema.YourTableName ADD CONSTRAINT DF_DefaultValue DEFAULT N'' FOR YourColumnName;

如果你已經有了預設約束，你也可以更新它的值：

ALTER TABLE YourSchema.YourTableName DROP CONSTRAINT DF_DefaultValue; ALTER TABLE YourSchema.YourTableName ADD CONSTRAINT DF_DefaultValue DEFAULT N'' FOR YourColumnName;

********************************************************************************

如果有很多欄位結尾都是 Finish，且欄位名稱結尾是 Memo 或 Text 也都要改 ( MyTable 請換成實際的 )

DECLARE @ColumnName NVARCHAR(255) DECLARE @TableName NVARCHAR(255) DECLARE @SQL NVARCHAR(MAX) SET @TableName='MyTable' DECLARE column_cursor CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND (COLUMN_NAME LIKE '%Finish' OR COLUMN_NAME LIKE '%Memo' OR COLUMN_NAME LIKE '%Text' ) OPEN column_cursor FETCH NEXT FROM column_cursor INTO @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT DF_' + @ColumnName + '_Default DEFAULT N'''' FOR ' + QUOTENAME(@ColumnName) EXEC sp_executesql @SQL FETCH NEXT FROM column_cursor INTO @ColumnName END CLOSE column_cursor DEALLOCATE column_cursor

********************************************************************************

Radio 結尾的「預設值」設定為 0；Check 結尾的「預設值」設定為 1 

DECLARE @ColumnName NVARCHAR(255) DECLARE @TableName NVARCHAR(255) DECLARE @SQL NVARCHAR(MAX) SET @TableName='MyTable' -- 處理以 Radio 結尾的欄位 DECLARE radio_cursor CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME LIKE '%Radio' OPEN radio_cursor FETCH NEXT FROM radio_cursor INTO @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT DF_' + @ColumnName + '_Default DEFAULT 0 FOR ' + QUOTENAME(@ColumnName) EXEC sp_executesql @SQL FETCH NEXT FROM radio_cursor INTO @ColumnName END CLOSE radio_cursor DEALLOCATE radio_cursor --處理以 Check 結尾的欄位 DECLARE check_cursor CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME LIKE '%Check' OPEN check_cursor FETCH NEXT FROM check_cursor INTO @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT DF_' + @ColumnName + '_Default DEFAULT 1 FOR ' + QUOTENAME(@ColumnName) EXEC sp_executesql @SQL FETCH NEXT FROM check_cursor INTO @ColumnName END CLOSE check_cursor DEALLOCATE check_cursor

(完)

相關