萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 表結構變更實戰:增刪改列、重命名、存在性判斷與字段描述視圖

DDL 基礎操作

-- 新增列
ALTER TABLE DocDsp ADD DspCode CHAR(200);

-- 刪除列
ALTER TABLE Table_Name DROP COLUMN Column_Name;

-- 修改數據類型
ALTER TABLE Table_Name ALTER COLUMN Column_Name New_Data_Type;

-- 重命名對象(表/列/索引/類型)
EXEC SP_RENAME 'OldName', 'NewName', 'OBJECT|COLUMN|INDEX|TYPE';

智能添加主鍵列(判斷是否已有 PK)

IF EXISTS (SELECT 1 FROM SYS.OBJECTS 
           WHERE PARENT_OBJECT_ID = OBJECT_ID('TB') AND TYPE = 'PK')
BEGIN
    PRINT '表已有主鍵,僅作普通列添加';
    ALTER TABLE TB ADD ColName INT DEFAULT 0;
END
ELSE
BEGIN
    PRINT '表無主鍵,添加主鍵列';
    ALTER TABLE TB ADD ColName INT PRIMARY KEY DEFAULT 0;
END

判斷表/列是否存在

-- 判斷列存在
IF EXISTS (SELECT 1 FROM SYS.COLUMNS 
           WHERE OBJECT_ID = OBJECT_ID('Table1') AND NAME = 'Name')
BEGIN
    SELECT * FROM People;  -- 業務邏輯
END

-- 判斷用戶表存在並刪除(正式表)
IF EXISTS (SELECT 1 FROM SYS.OBJECTS 
           WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[TableName]') AND TYPE = 'U')
    DROP TABLE [DBO].[TableName];

-- 判斷臨時表存在(需去 tempdb)
IF OBJECT_ID('TEMPDB..##Temp') IS NOT NULL
    DROP TABLE ##Temp;

字段描述統一檢視(生產環境推薦)

CREATE VIEW V_Field_Desc AS
SELECT
    O.NAME AS TableName,
    C.NAME AS ColumnName,
    T.NAME AS DataType,
    C.MAX_LENGTH,
    C.PRECISION,
    C.SCALE,
    C.IS_NULLABLE,
    EP.VALUE AS Description
FROM SYS.COLUMNS C
JOIN SYS.OBJECTS O ON O.OBJECT_ID = C.OBJECT_ID
JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID
LEFT JOIN SYS.EXTENDED_PROPERTIES EP
    ON EP.MAJOR_ID = C.OBJECT_ID
   AND EP.MINOR_ID = C.COLUMN_ID
   AND EP.NAME = 'MS_Description'
WHERE O.TYPE = 'U';

使用:SELECT * FROM V_Field_Desc WHERE TableName = 'YourTable';

copyright © 萬盛學電腦網 all rights reserved