[研究]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
|
(完)
相關
