SQL Server 查询列是否存在 - zLulus/My_Note GitHub Wiki

以SQL Server为例，查询列是否存在，如果不存在，则新增列

IF NOT EXISTS (
SELECT
    *
FROM
    syscolumns
WHERE
    id = object_id('[dbo].[Table_Name]')
    AND  name = 'Column_Name')
ALTER TABLE [dbo].[Table_Name]
ADD Column_Name VARCHAR(50)
GO

也可以根据名称和数据类型查询，借此修改数据类型

IF EXISTS(
        SELECT 1
        FROM sys.[tables] AS T
                    INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
                    INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
            AND AC.[user_type_id] = TY.[user_type_id]
        WHERE T.[name] = 'tableName'
            AND AC.[name] = 'columnName'
            AND TY.[name] = 'int')
    BEGIN
        ALTER TABLE dbo.tableName
            ALTER COLUMN columnName varchar(100)
    END

AND TY.[name] = 'int'可以根据具体类型调整

也可以根据名称和数据类型、长度查询，修改数据长度
这里将一个原长度为50的列长度改为100

IF EXISTS(
    SELECT 1
    FROM sys.[tables] AS T
                INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
                INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
        AND AC.[user_type_id] = TY.[user_type_id]
    WHERE T.[name] = 'tableName'
        AND AC.[name] = 'columnName'
        AND TY.[name] = 'varchar'
        AND AC.[max_length] = 50)
BEGIN
    ALTER TABLE dbo.tableName
        ALTER COLUMN columnName varchar(100)
END