-- 新增列
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';
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';